Undocumented Maintenance SQL Server 2014 DBCC Commands
In this article, you can find the description of some useful undocumented maintenance DBCC
commands, and find out how you can use these commands in SQL Server 2014 for administering
and maintenance tasks.
1. DBCC addinstance
This DBCC command can be used to add an object instance to track in Performance Monitor.
Syntax:
DBCC addinstance (objectname, instancename)
where
objectname is the name of the object that contains the instance.
instancename is the name of the instance to add.
The following example add the ‘instance_name’ instance for the ‘object_name’ object to track by
Performance Monitor:
DBCC addinstance (‘object_name’, ‘instance_name’)
2. DBCC checkprimaryfile
This DBCC command checks the allocation and structural integrity of the primary database file.
Syntax:
DBCC checkprimaryfile ({‘FileName’} [,opt={0|1|2|3}])
where
FileName is the primary database file to check.
opt=0 – check is the file a primary database file.
opt=1 – return name, size, maxsize, status and path of all files associated with the database.
opt=2 – return the database name, version and collation.
opt=3 – return name, status and path of all files associated with the database.
The following example checks the Test.MDF file and return name, size, maxsize, status and path
of all files associated with the database:
DBCC checkprimaryfile (‘C:SQL2014DataTest.MDF’, 1)
3. DBCC dbrecover
This DBCC command recovers a suspect database.
Syntax:
DBCC dbrecover (‘dbname’)
where
dbname is the database name to recover.
The following example recovers the Sales database:
DBCC dbrecover (‘Sales’)
4. DBCC dbreindexall
This DBCC command rebuilds all indexes in the database.
Syntax:
DBCC dbreindexall (‘dbname’)
where
dbname is the database name to rebuild all indexes.
The following example rebuilds all indexes in the Sales database:
DBCC dbreindexall (‘Sales’)
5. DBCC deleteinstance
This DBCC command can be used to delete a Performance Monitor object instance that was set up
with DBCC addinstance.
Syntax:
DBCC deleteinstance (objectname, instancename)
where
objectname is the name of the Performance Monitor object.
instancename is the name of the instance to delete.
The following example deletes the ‘instance_name’ instance for the ‘object_name’ object:
DBCC deleteinstance (‘object_name’, ‘instance_name’)
6. DBCC errorlog
This DBCC command can be used to truncate the current SQL Server log. You can use this command
when you rarely restart the Microsoft SQL Server service and you find out that your server log
gets very large and takes a long time to load and view. You can use the DBCC errorlog command
to truncate the current SQL Server log, but the more correct way is using the sp_cycle_errorlog
system stored procedure as a documented way to accomplish the same task.
Syntax:
DBCC errorlog
The following example truncates the current SQL Server log:
DBCC errorlog
7. DBCC flushprocindb
This DBCC command can be used to clear out the stored procedure cache for a specific database on
a SQL Server 2014.
Syntax:
DBCC flushprocindb (dbid)
where
dbid is the database ID number to clear the stored procedure cache.
The following example clears the stored procedure cache in the Sales database:
DECLARE @dbid INT
SELECT @dbid = dbid FROM master.dbo.sysdatabases WHERE name = ‘Sales’
DBCC flushprocindb (@dbid)