Undocumented Miscellaneous SQL Server 2014 DBCC Commands
In this article, you can find the description of some useful undocumented miscellaneous DBCC
commands, and find out how you can use these commands in SQL Server 2014.
1. DBCC COLLECTSTATS
This command can be used to turn on/off cache statistics.
Syntax:
DBCC COLLECTSTATS (‘on’ | ‘off’)
This example turns on cache statistics:
DBCC COLLECTSTATS (‘on’)
This example turns off cache statistics:
DBCC COLLECTSTATS (‘off’)
2. DBCC CURSORSTATS
This DBCC command returns an aggregate collection of statistics for cursor usage.
Syntax:
DBCC CURSORSTATS ([spid [,’clear’]])
where
spid – is a process ID, which can be returned by the sp_who system stored procedure.
clear – used to clear the cursor statistics.
This example returns an aggregate collection of statistics for cursor used in the current
user process:
DBCC CURSORSTATS (@@SPID)
3. DBCC DETACHDB
This command is used to detach SQL Server 2014 database. You can use the DBCC DETACHDB command
to detach SQL Server 2014 database, but the more correct way is using the sp_detach_db system
stored procedure as a documented way to accomplish the same task.
Syntax:
DBCC DETACHDB ( ‘dbname’ [, fKeep_Fulltext_Index_File (0 | 1)] )
This example detaches the TestDb database:
DBCC DETACHDB (‘TestDb’)
4. DBCC FILEHEADER
This command returns logical file name, file size, growth increment and so on.
Syntax:
DBCC FILEHEADER ({‘dbname’ | dbid} [, fileid])
where
dbname | dbid – is a database name or database ID
fileid – is a file identificator
This example shows the file header for the TestDb database:
DBCC FILEHEADER (‘TestDb’)
5. DBCC INVALIDATE_TEXTPTR_OBJID
This DBCC command can be used to invalidate in-row text pointers for table.
Syntax:
DBCC INVALIDATE_TEXTPTR_OBJID (objid)
where
objid is integer (the object identificator to invalidate the in-row text pointers)
Example:
DECLARE @objid INT
SET @objid = OBjECT_ID(‘tb1’)
DBCC INVALIDATE_TEXTPTR_OBJID (@objid)
6. DBCC SQLMGRSTATS
This command returns three values that shows how caching is being performed on ad-hoc and prepared
Transact-SQL statements. There are:
Memory Used (8k Pages) – cache size for the ad-hoc and prepared Transact-SQL statements.
Number CSql Objects – is the total number of cached Transact-SQL statements.
Number False Hits – wrong attempts to get prepared Transact-SQL statements from the cache.
Syntax:
DBCC SQLMGRSTATS
This example returns the status of cached Transact-SQL statements:
DBCC SQLMGRSTATS