604
Part V: Enterprise Data Management
■ (^) DBCC FREESYSTEMCACHE: Clears the unused cache entries from all caches. It also
clears the plan cache. It also causes an unload of all AppDomains for SQLCLR and
frees the CLR memory allocations in the Virtual Address Space/Memory To Leave
areas. Additionally, with this command, you’re able to manually remove unused
entries from all caches or from a specifi ed Resource Governor pool cache.
■ DBCC INPUTBUFFER (SPID): Returns the last command executed by a client,
as identifi ed by the client’s SPID. This command can be executed only by mem-
bers of the sysadmin server group or those who have explicit VIEW SERVER STATE
permission.
■ DBCC OUTPUTBUFFER (SPID): Returns the results of the last command executed
by a client in hexadecimal and ASCII format. Like the DBCC INPUTBUFFER com-
mand, this command can be executed only by members of the sysadmin group.
■ (^) DBCC PROCCACHE: Reports some basic statistics about the procedure cache as que-
ries and procedures are compiled and stored in memory.
■ (^) DBCC PINTABLE: Has been deprecated and has no effect on the server.
■ DBCC UNPINTABLE: Has been deprecated and has no effect on the server.
■ (^) DBCC dllname (FREE): Unloads the specifi ed extended stored procedure dll from
memory.
■ (^) DBCC MEMORYSTATUS: Provides a snapshot of the current memory status of SQL
Server. This command is useful in troubleshooting issues that relate to SQL Server
memory consumption.
■ DBCC USEROPTIONS: Returns the active SET options for the current connections.
You can use the following three DBCC commands with trace fl ags. Use trace fl ags to tempo-
rarily set certain specifi c SQL Server characteristics or turn off some behavior. For example,
use trace fl ag 1204 to enable deadlock reporting to SQL Server Errorlog.
■ DBCC TRACEON: Turns on the specifi ed trace fl ags
■ (^) DBCC TRACEOFF: Turns off the specifi ed trace fl ags
■ DBCC TRACESTATUS: Displays the active trace fl ags
Managing Database Maintenance
SQL Server provides a host of database maintenance commands. Fortunately, it also pro-
vides the DBA with ways to schedule maintenance tasks.c22.indd 604c22.indd 604 7/31/2012 9:24:32 AM7/31/2012 9:24:32 AM
http://www.it-ebooks.info