It’s amazing what I didn’t know about dbcc !!

As part of a series of processes I manipulate ( cleanse )  a copy of production for use in a test/support environment, one of things which I do is switch to simple recovery and shrink the t-log. This process is automated overnight and is intended to present a restored environment the following morning. Anyway as part of the process I use dbcc updateusage to ensure that the t-log gets fully shrunk after putting the database to simple recovery. For reasons I’m not going to list I want the ldf file to be as small as possible.

To make sure the shrink works I use  dbcc updateusage(0) with count_rows;   if you’ve ever had to deal with a log that refuses to shrink or buffers which refuse to clear you’ll understand.

What I wasn’t aware of was that although dbcc updateusage will load some data into buffer cache the with count_rows option will attempt to load the entire dataset into cache. This had the effect of essentially clearing the buffer cache for my production database .. not good.

How did I track what was happening? Well I repeated the process on a DR server monitoring the buffer cache as I went, the sql for this is below; If your database is bigger than your memory this isn’t something you want to do most likely, very interesting but I guess unless you’re actually looking for this you won’t know. BOL has no mention that I can see of the downside of using this command – fortunately  my database is small, had it been a couple of terabytes the imapct might have been very severe

select db_name(database_id) as db_name, 
count(page_id)as number_pages,count(page_id)/128 as mb
from sys.dm_os_buffer_descriptors 
where database_id !=32767 
group by database_id 
order by database_id;
Published Thursday, September 23, 2010 10:29 PM by GrumpyOldDBA


No Comments