603
Chapter 22: Maintaining the Database
22
Name : user_transaction
LSN : (42:228:1)
Start time : May 30 2012 7:31:53:077PM
SID :
0x010500000000000515000000f619041ca1c7523210b246a2e8030000
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.Based on this information, the errant transaction can be tracked down, and the SPID (user
connection) can be killed. SQL Server Management Studio’s Processes node in Activity
Monitor can provide more information about the SPID’s activity. A more drastic option is to
stop and restart the server and then shrink the database.The recovery model and transaction log backups both affect how the transaction log grows and
automatically truncates. For more information on these critical issues, refer to Chapter 21, “Backup
and Recovery Planning.”Now that you’ve seen an open transaction, close it. The following code rolls back the trans-
action and verifi es that there aren’t any other open transactions for the database:ROLLBACK TRAN;
DROP TABLE Test;DBCC OPENTRAN;Result:No active open transactions.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.Miscellaneous DBCC Commands
The remaining DBCC commands are used in troubleshooting during testing of stored proce-
dures and triggers. The fi rst fi ve DBCC commands must be used with caution and should not
be run against a production SQL Server instance because it may result in a sudden decrease
in query performance.■ DBCC DROPCLEANBUFFERS: Cleans the memory of any buffered data so that it
doesn’t affect query performance during testing. This command is useful for test-
ing queries without recycling SQL Server service.■ (^) DBCC FREEPROCCACHE: This command can clear an entire plan cache, remove
specifi c plans from the plan cache (by specifying a plan or SQL handle), or you can
clear all cache entries associated with a specifi ed resource pool.
■ DBCC FLUSHPROCINDB(DBID): Clears out the stored procedure cache for a specifi c
SQL Server database and not the entire SQL Server.
■ DBCC FREESESSIONCACHE: Clears the cache used by the distributed queries.
c22.indd 603c22.indd 603 7/31/2012 9:24:32 AM7/31/2012 9:24:32 AM
http://www.it-ebooks.info