DBCC INDEXDEFRAG and DBREINDEX

DBCC commands are some of the most misunderstood, yet essential to anyone maintaining a MSSQL database.

I work with MSSQL databases daily and found http://blogs.msdn.com/b/teamcenteronsql/ to be indispensable when trying to determine to use DBCC INDEXDEFRAG or DBCC DBREINDEX.

There are some main points to this MSDN blog...

DBREINDEX works to resolve "extent" or physical fragmentation
DBREINDEX uses shared locking, so only SELECT statements can be used
INDEXDEFRAG works to resolve logical fragmentation, but does nothing for physical or "extents"
Physical disk, i.e. the hard drive, defragmentation can cause "extent" fragmentation in the database, so if we are to scan a disk for fragments and decide to defragment that disk, be sure to run DBCC SHOWCONTIG afterwards to see how the "extent" fragmentation holds up and if you will then need to run DBREINDEX to resolve the issues cause by defragmenting the hard disk.

This information helped me and I hope someone else finds it equally useful.

Rebuilding a MSSQL 2005+ Master Database

When you find yourself unable to log into your Microsoft SQL database - first check that the service is running. If the service is not running, then you might have a corrupt MASTER system database. So then you ask... How do I fix that?

In MSSQL 2000, the task is a bit different because there is a utility in the binn folder that is not present in MSSQL 2005 or greater. To fix/rebuild the master system database, one has to go through the setup process instead.

I've explained how to do this with a command-line command at my www.enkognedo.com site. Just visit there and you will find what I had to do, why most of the on-line resources miss the target a bit, and a resource link to Microsoft that gives more detail.

Here is the link: http://bit.ly/diWPv5