Winner Announcement!

The third winner of this month is Annapu Reddy Gayathri! I've seen a nice increase of participants so far. I have one more month of Pluralsight to give away then it's on to the book! Dec 30th I will be mailing a book to one lucky winner. Something that just came to my mind... Mailing the book will be for continental US only. I don't want to stop anyone out of country from joining.... If you win and you're from another country I will instead send you a code for 25$ to amazon to purchase what ever you wish.

Popular DBCC Commands!

These are the DBCC commands I catch myself using the most. I'm including the reasons I use them. Do you have any suggestions to throw in here?

DBCC CheckDB - I mainly use it to check for any consistency issues. I prefer to have this running daily, or when  I do my weekly restores to test my backups, I run DBCC CHECKDB on that database. It can let you know of problems that you can fix prior to losing data.

DBCC INPUTBUFFER - Basic use is to show what the last command sent from a specific spid did.Good for tracking down what a blocking query is doing.

DBCC OPENTRAN - This shows what the oldest active transaction is. You can use this to see if you have active transactions or even to see what may be holding replication up between servers.

DBCC FREEPROCCACHE - This removes your plan cache. It's not something I'd advise unless you're isolating a specific issue, but it's good to test query times when you don't want the whole thing reliant on ram. Best on a test box though.

DBCC FREESESSIONCACHE - This flushes the distributed query connection cache.This is another one that I'd advise against using unless you're isolating a specific issue. I use this for testing queries as well.

DBCC FREESYSTEMCACHE - This removes all unused cache entries. SQL does this automatically, but this is the manual version. I like to use this when I'm testing queries. It's nice to at least think I have a clean slate.

DBCC SHRINKFILE - This allows you to shrink database files one by one to either specified sizes or to as much as it will allow. Now now, let me defend myself first. Shrinks are not all bad. They have a good use occasionally! I wouldn't use them daily... but if you're moving a lot of data around and building out partitions, you have a use to shrink the old files. Maybe you ran out of disk space by accident and your logs are huge since the database in full recovery didn't have a place to backup? These are good uses.

DBCC SHRINKDATABASE - This shrinks a database as a whole. Again like with shrink file... there are uses. If you're moving the database to a small test box, you may need the space before you can fit that restore there.

DBCC UPDATEUSAGE - This helps report accurate row counts. My understanding is that this was most useful coming from 2000 to any higher version. It still catches some that aren't listed else where.