Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Small Collection of DBCC Commands

There are a number of pretty useful DBCC Commands that have nothing to do with checking the consistency of a database (and thus were probably not written by Paul Randal).  I have put together a few of them in the script below.  The first command is the only one that I consider to be somewhat “dangerous”, in that it will completely flush the contents of your “clean’ buffer cache, which will cause a lot of stress on your I/O subsystem as the buffer cache is refilled from disk. Depending on your workload, this might take a few minutes to happen, during which time you would see a performance impact.

-- A Small Collection of Useful DBCC Commands
-- Glenn Berry 
-- August 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry


-- Clears out contents of buffer cache
-- Use caution before doing this on a production system!
DBCC DROPCLEANBUFFERS;

-- Clears procedure cache on entire SQL instance
DBCC FREEPROCCACHE;

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

-- Clear ad-hoc SQL plans for entire SQL instance
DBCC FREESYSTEMCACHE('SQL Plans'); 

-- Clears TokenAndPermUserStore cache on entire SQL instance
DBCC FREESYSTEMCACHE ('TokenAndPermUserStore');

-- Releases all unused cache entries from all caches. ALL specifies all supported caches
-- Asynchronously frees currently used entries from their respective caches after they become unused
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;


-- Determine the id of the current database
-- and flush the procedure cache for only that database
DECLARE @intDBID AS INT = (SELECT DB_ID());
DBCC FLUSHPROCINDB (@intDBID);

-- Clear Wait Stats for entire instance
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Get VLF count for transaction log for the current database,
-- number of rows equals VLF count. Lower is better!
DBCC LOGINFO;

-- Returns lots of useful information about memory usage
DBCC MEMORYSTATUS;

-- Find oldest open transaction
DBCC OPENTRAN;

-- Get input buffer for a SPID
DBCC INPUTBUFFER(21);

-- Check trace status for instance
DBCC TRACESTATUS(-1)

Comments

Posted by Robert L Davis on 2 August 2010

Interesting list. A list I would like to see is a comprehensive list of all of the undocumented DBCC commands.

Posted by Dukagjin Maloku on 3 August 2010

Thanks Glenn, +1 vote for Robert L Davis suggestion!

Posted by basuadapa2000 on 20 March 2012

good

Leave a Comment

Please register or log in to leave a comment.