Useful Undocumented Maintenance SQL Server 2005 DBCC Commands
DBCC is an abbreviation for Database Console Command. DBCC commands are generally used to check
the physical and logical consistency of a database, although they are also used for a variety of
miscellaneous tasks.
In this article, I want to tell you about some useful undocumented maintenance DBCC commands,
and how you can use these commands in SQL Server 2005 for administering and maintenance.
1. DBCC addextendedproc
This undocumented DBCC command is used in the sp_addextendedproc system stored procedure to
register the name of a new extended stored procedure to SQL Server 2005. You can use the
DBCC addextendedproc command to add a new extended stored procedure, but the more correct way
is using the sp_addextendedproc system stored procedure as a documented way to accomplish the
same task.
Syntax:
DBCC addextendedproc (function_name, dll_name)
where
functname nvarchar(517) is the name of function to call.
dllname varchar(255) is the name of DLL containing function.
The following example adds the xp_test extended stored procedure:
USE master
DBCC addextendedproc (xp_test, 'c:\xp_test.dll')
2. 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')
3. 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:\SQL2005\Data\Test.MDF', 1)
4. 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')
5. 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')
6. 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')
7. DBCC dropextendedproc
This undocumented DBCC command is used in the sp_dropextendedproc system stored procedure to drop
an extended stored procedure. You can use the DBCC dropextendedproc command to drop an extended
stored procedure, but the more correct way is using the sp_dropextendedproc procedure as a
documented way to accomplish the same task.
Syntax:
DBCC dropextendedproc (functname)
where
functname nvarchar(517) is the name of the extended stored procedure to delete.
The following example drops the xp_test extended stored procedure:
USE master
DBCC dropextendedproc ('xp_test')
8. 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 MSSQLServer 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
9. DBCC flushprocindb
This DBCC command can be used to clear out the stored procedure cache for a specific database on
a SQL Server 2005.
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)
10. DBCC renamecolumn
This DBCC command can be used to rename a column in a table. You can use the DBCC renamecolumn
command to rename a column, but the more correct way is using the sp_rename system stored
procedure or ALTER TABLE statement as a documented way to accomplish the same task.
Syntax:
DBCC renamecolumn (object_name, old_name, new_name)
where
object_name is the table name.
old_name is the old column name.
new_name is the new column name.
The following example renames the CName column to the CountryName column in the Countries table:
DBCC renamecolumn (Countries, CName, CountryName)
