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;