SQL Server Tacklebox- P29: This book, as with almost all books, started out as an idea. I wanted to accumulate together those scripts and tools that I have built over the years so that DBAs could sort through them and perhaps adapt them for their own circumstances. | 5 DBA as detective . Executing. SQLQuery . SQLQuery . .ster sa 56 -. Executing BEGIN PRINT DBCC Results for SPID Cast @tSPID as varchar 5 4- rtrimiSblkst 1 1 PRINT 1-----------------------------------------1 PRINT 1 1 DBCC INPUTBUFFER BtSPID SELECT TOP 1 Qt-qPTTl hqPTTi T mi Results DBCC execution completed. If DBCC printed error messages contact your system administrator. DBCC Results for SPID 53 BLOCKED EventType Parameters EventInfo Language Event 0 Script for SelectTopNRows coramand from SSMS SELECT TOP 1000 T_ID T_Desc T_Back T_Square FROM DBA_Rep . dbo . Important_Data DBCC execution completed. If DBCC printed error messages contact your system administrator. DBCC Results for SPID 55 BLOCKED EventType Parameters EventInfo Language Event 0 Script for SelectTopNRows command from SSMS SELECT TOP 1000 T_ID T_Desc T_Back T_Square FROM DBA_Rep . dbo . Inportant_Data DBCC execution completed. If DBCC printed error messages contact your system administrator. Figure Blocked SPIDs found using Automated Discovery query. You might decide that you would like to take this query and make it into a stored procedure. You can then load it into a maintenance database on each server so that you have it always available. It also means that you can parameterize it to control its behavior. For example you may decide that you do not want to execute the portion of the query that counts locks which on a very busy system could take quite a bit of time. Listing shows the code to create this stored procedure named usp_Find_Problems with a flag to execute the lock count portion based on need. USE DBA_Rep GO Object StoredProcedure dbo . usp_Find_Problems Script Date 06 22 2009 22 41 37 140 5 DBA as detective SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE dbo . usp_Find_Problems @count_locks BIT 1 AS SET NOCOUNT ON -- Count the locks IF @count_locks 0 GOTO Get_Blocks ELSE IF @count_locks 1 BEGIN CREATE TABLE .
