Stored Procedure will not populate table
-
I was looking for some expert help! I have a store procedure that was passed on to me that runs in a loop and populates a table with lockiing information. It hasn't yet populated it with data, even though locking events have occured.
The job is setup to run when sqlagent starts with the following command:
WHILE 1=1
BEGIN
EXEC SP_BLOCK @BATCH='Y'
WAITFOR DELAY '00:00:10'
END
Here is the stored procedure that executes:
CREATE procedure SP_BLOCK @BATCH CHAR(1) = NULL AS-- ***********************************************************************-- This stored procedure is provided AS IS with no warranties and confers no rights.-- ***********************************************************************CREATE TABLE #DBCC(PARENTOBJECTNVARCHAR(128),OBJECTNVARCHAR(128),FIELDNVARCHAR(128),VALUENVARCHAR(128))DECLARE @BLOCKED TABLE(BLOCKER_SPIDSMALLINT,BLOCKER_CONTEXTVARCHAR(128),BLOCKER_STATUSVARCHAR(18),BLOCKED_SPIDSMALLINT,BLOCKED_CONTEXTVARCHAR(128),WAITTIMEINT,LOCK_MODEVARCHAR(7),LOCK_TYPECHAR(3),DBIDSMALLINT,LOCK_RESOURCEVARCHAR(30),BLOCKER_SQLTEXT,BLOCKED_SQLTEXT)DECLARE @BLOCKED2 TABLE(BLOCKER_SPIDSMALLINT,BLOCKER_CONTEXTVARCHAR(128),BLOCKER_STATUSVARCHAR(18),BLOCKED_SPIDSMALLINT,BLOCKED_CONTEXTVARCHAR(128),WAITTIMEINT,LOCK_MODEVARCHAR(7),LOCK_TYPECHAR(3),DBNAMECHAR(8),TABLENAMECHAR(18),INDEXIDINT,BLOCKER_SQLTEXT,BLOCKED_SQLTEXT)SET NOCOUNT ONDECLARE@BLOCKER_SPIDSMALLINT,@BLOCKER_CONTEXTVARCHAR(128),@BLOCKER_STATUSVARCHAR(18),@BLOCKED_SPIDSMALLINT,@BLOCKED_CONTEXTVARCHAR(128),@WAITTIMEINT,@LOCK_MODEVARCHAR(7),@LOCK_TYPECHAR(3),@DBIDSMALLINT,@OBJECTIDINT,@INDEXIDINT,@LOCK_RESOURCEVARCHAR(30),@BLOCKER_HANDLEBINARY(20),@BLOCKER_SQLVARCHAR(8000),@BLOCKED_HANDLEBINARY(20),@BLOCKED_SQLVARCHAR(8000),@CMD VARCHAR(1000),@DELIMITER1 TINYINT,@DELIMITER2 TINYINT,@DELIMITER3 TINYINT,@FILEIDVARCHAR(10),@PAGEIDVARCHAR(10)-----------------------------------------------------------------------------------------Populate temporary table #BLOCKED from sysindexes for blocked and blocking processes---------------------------------------------------------------------------------------DECLARE PROCESSES CURSOR FORSELECTBLOCKER.spid,-- BLOCKER_SPIDRTRIM(convert(varchar(128),BLOCKER.context_info)),-- BLOCKER_CONTEXTCASE BLOCKER.blocked-- BLOCKER_STATUSWHEN 0 THEN 'Lead Blocker'ELSE 'In Blocking Chain'END,BLOCKED.spid,-- BLOCKED_SPIDRTRIM(convert(varchar(128),BLOCKED.context_info)),-- BLOCKER_CONTEXTBLOCKED.waittime, -- BLOCKED_WAITTIMECASECONVERT(TINYINT, BLOCKED.waittype)-- LOCK_MODEWHEN 1THEN 'SCH-ST'WHEN 2THEN 'SCH-MOD'WHEN 3THEN 'S'WHEN 4THEN 'U'WHEN 5THEN 'X'WHEN 6THEN 'IS'WHEN 7THEN 'IU'WHEN 8THEN 'IX'WHEN 9THEN 'SIU'WHEN 10THEN 'SIX'WHEN 11THEN 'UIX'WHEN 12THEN 'BU'WHEN 13 THEN 'RangeS-S' WHEN 14 THEN 'RangeS-U' WHEN 15 THEN 'RangeIn-Null' WHEN 16 THEN 'RangeIn-S' WHEN 17 THEN 'RangeIn-U' WHEN 18 THEN 'RangeIn-X' WHEN 19 THEN 'RangeX-S' WHEN 20 THEN 'RangeX-U' WHEN 21 THEN 'RangeX-X'ELSE 'UNKNOWN'END,SUBSTRING(BLOCKED.waitresource,1,3),-- LOCK_RESOURCE_TYPEBLOCKED.dbid,-- DBIDSUBSTRING(BLOCKED.waitresource,6,30),-- LOCK_RESOURCEBLOCKER.sql_handle,BLOCKER.cmd,BLOCKED.sql_handle,BLOCKED.cmdFROM master..sysprocesses BLOCKERJOIN master..sysprocesses BLOCKED ON BLOCKER.spid = BLOCKED.blockedWHERE BLOCKED.blocked <> 0AND BLOCKER.dbid = db_id()OPEN PROCESSESFETCH PROCESSESINTO@BLOCKER_SPID,@BLOCKER_CONTEXT,@BLOCKER_STATUS,@BLOCKED_SPID,@BLOCKED_CONTEXT,@WAITTIME,@LOCK_MODE,@LOCK_TYPE,@DBID,@LOCK_RESOURCE,@BLOCKER_HANDLE ,@BLOCKER_SQL,@BLOCKED_HANDLE ,@BLOCKED_SQLWHILE@@FETCH_STATUS = 0BEGINIF @BLOCKED_HANDLE <> 0x0 ANDIS_SRVROLEMEMBER ('sysadmin') = 1SELECT @BLOCKED_SQL = text from ::fn_get_sql(@BLOCKED_HANDLE)IF @BLOCKER_HANDLE <> 0x0 ANDIS_SRVROLEMEMBER ('sysadmin') = 1SELECT @BLOCKER_SQL = text from ::fn_get_sql(@BLOCKER_HANDLE)INSERT INTO @BLOCKED VALUES( @BLOCKER_SPID,@BLOCKER_CONTEXT,@BLOCKER_STATUS,@BLOCKED_SPID,@BLOCKED_CONTEXT,@WAITTIME,@LOCK_MODE,@LOCK_TYPE,@DBID,@LOCK_RESOURCE,@BLOCKER_SQL,@BLOCKED_SQL)FETCH PROCESSES INTO@BLOCKER_SPID,@BLOCKER_CONTEXT,@BLOCKER_STATUS,@BLOCKED_SPID,@BLOCKED_CONTEXT,@WAITTIME,@LOCK_MODE,@LOCK_TYPE,@DBID,@LOCK_RESOURCE,@BLOCKER_HANDLE ,@BLOCKER_SQL,@BLOCKED_HANDLE ,@BLOCKED_SQLEND-- @@FETCH_STATUS = 0DEALLOCATE PROCESSESDECLARE BLOCKED CURSOR FOR SELECTBLOCKER_SPID,BLOCKER_CONTEXT,BLOCKER_STATUS,BLOCKED_SPID,BLOCKED_CONTEXT,WAITTIME,LOCK_MODE,LOCK_TYPE,DBID,LOCK_RESOURCE,BLOCKER_SQL,BLOCKED_SQLFROM@BLOCKEDOPEN BLOCKEDFETCH BLOCKEDINTO @BLOCKER_SPID,@BLOCKER_CONTEXT,@BLOCKER_STATUS,@BLOCKED_SPID,@BLOCKED_CONTEXT,@WAITTIME,@LOCK_MODE,@LOCK_TYPE,@DBID,@LOCK_RESOURCE,@BLOCKER_SQL,@BLOCKED_SQLWHILE @@FETCH_STATUS = 0 BEGIN-----------------------------------------------------------------------------------------Decode the waitresource column from sysprocesses.--The 1st 5 bytes have already been trimmed off and stored in LOCK_TYPE-----------------------------------------------------------------------------------------Establish position of the delimiters between the fields of the lock--resource. In order to establish a uniform delimiter to look for, replace ':' with ' '---------------------------------------------------------------------------------------SET @LOCK_RESOURCE = REPLACE(@LOCK_RESOURCE,':',' ')SET @DELIMITER1 = CHARINDEX(' ',@LOCK_RESOURCE)SET @DELIMITER2 = CHARINDEX(' ', @LOCK_RESOURCE, (@DELIMITER1+1))SET @DELIMITER3 = CHARINDEX(' ', @LOCK_RESOURCE, (@DELIMITER2+1))-----------------------------------------------------------------------------------------Delimiter positions are then used to substring fields from @LOCK_RESOURCE---------------------------------------------------------------------------------------IF @LOCK_TYPE IN ('RID','PAG')BEGIN-----------------------------------------------------------------------------------------Extract objectid and indexid from file/page for resources of RID or PAG.--LOCK_RESOURCE_TYPE 'RID':dbid:fileid:pageid:row#--Example:7:3:47912:10--LOCK_RESOURCE_TYPE 'PAG':dbid:fileid:pageid--Example:7:3:47912---------------------------------------------------------------------------------------SET @FILEID = SUBSTRING(@LOCK_RESOURCE,@DELIMITER1+1,(@DELIMITER2-@DELIMITER1)-1)SET @PAGEID = SUBSTRING(@LOCK_RESOURCE,@DELIMITER2+1,(@DELIMITER3-@DELIMITER2)-1)-----------------------------------------------------------------------------------------Execute DBCC PAGE to determine the object owner of the page. We use the rowset--version of DBCC (WITH TABLERESULTS) to make it easy to retrieve objext id and index id.---------------------------------------------------------------------------------------SET @CMD = 'DBCC PAGE ('+CONVERT(VARCHAR(3),@DBID)+','+@FILEID+','+@PAGEID+') WITH TABLERESULTS, no_infomsgs'INSERT INTO #DBCC EXEC(@CMD)SELECT @OBJECTID = CONVERT(INT,SUBSTRING(VALUE,1,30)) FROM #DBCC WHERE FIELD = 'm_objId' OPTION(KEEP PLAN)SELECT @INDEXID = CONVERT(INT,SUBSTRING(VALUE,1,30)) FROM #DBCC WHERE FIELD = 'm_indexId' OPTION(KEEP PLAN)TRUNCATE TABLE #DBCCENDIF @LOCK_TYPE = 'TAB'BEGIN-----------------------------------------------------------------------------------------LOCK_RESOURCE_TYPE 'TAB':dbid:objectid--Example:7:1993058136---------------------------------------------------------------------------------------SET @OBJECTID = SUBSTRING(@LOCK_RESOURCE,@DELIMITER1+1,(@DELIMITER2-@DELIMITER1)-1)-----------------------------------------------------------------------------------------For table locks, set indexid to '0'---------------------------------------------------------------------------------------SET@INDEXID = 0-----------------------------------------------------------------------------------------If the waitresource contains the keyword COMPILE, then the object name is actually a--stored procedure.--Example:6:834102 [[COMPILE]]---------------------------------------------------------------------------------------IF @LOCK_RESOURCE LIKE '%COMPILE%'SET @LOCK_TYPE = 'PRC'ENDIF @LOCK_TYPE = 'KEY'BEGIN-----------------------------------------------------------------------------------------LOCK_RESOURCE_TYPE 'KEY':dbid:objectid:indexid (hash of key value)--Example:7:1993058136:4 (0a0087c006b1)---------------------------------------------------------------------------------------SET @OBJECTID = SUBSTRING(@LOCK_RESOURCE,@DELIMITER1+1,(@DELIMITER2-@DELIMITER1)-1)SET @INDEXID = SUBSTRING(@LOCK_RESOURCE,@DELIMITER2+1,(@DELIMITER3-@DELIMITER2)-1)END-----------------------------------------------------------------------------------------If executing in batch, write directly to SQLPERF..BLOCKED_PROCESSES---------------------------------------------------------------------------------------IF @BATCH IS NOT NULLINSERT INTO SQLPERF..BLOCKED_PROCESSES WITH (TABLOCKX) VALUES (GETDATE(),@BLOCKED_SPID,@BLOCKED_CONTEXT,@BLOCKER_SPID,@BLOCKER_CONTEXT,@BLOCKER_STATUS,@WAITTIME,LEFT(DB_NAME(@DBID),8),LEFT(OBJECT_NAME(@OBJECTID),18),@INDEXID,@LOCK_TYPE,@LOCK_MODE,@BLOCKER_SQL,@BLOCKED_SQL)ELSE-----------------------------------------------------------------------------------------Otherwise, insert into temp table for subsequent OUTPUT---------------------------------------------------------------------------------------INSERT INTO @BLOCKED2 VALUES (@BLOCKER_SPID,@BLOCKER_CONTEXT,@BLOCKER_STATUS,@BLOCKED_SPID,@BLOCKED_CONTEXT,@WAITTIME,@LOCK_MODE,@LOCK_TYPE,LEFT(DB_NAME(@DBID),8),LEFT(OBJECT_NAME(@OBJECTID),18),@INDEXID,@BLOCKER_SQL,@BLOCKED_SQL)FETCH BLOCKEDINTO @BLOCKER_SPID,@BLOCKER_CONTEXT,@BLOCKER_STATUS,@BLOCKED_SPID,@BLOCKED_CONTEXT,@WAITTIME,@LOCK_MODE,@LOCK_TYPE,@DBID,@LOCK_RESOURCE,@BLOCKER_SQL,@BLOCKED_SQL END-- WHILE @@FETCH_STATUS = 0 DEALLOCATE BLOCKED-----------------------------------------------------------------------------------------Format output.---------------------------------------------------------------------------------------IF @BATCH IS NULLSELECT BLOCKED_DTTM = GETDATE(),BLOCKED_SPID,BLOCKED_CONTEXT, BLOCKER_SPID,BLOCKER_CONTEXT,BLOCKER_STATUS,WAITTIME AS[WAITING(ms)],DBNAME,TABLENAME,INDEXID, LOCK_TYPE ,LOCK_MODE ,BLOCKER_SQL,BLOCKED_SQLFROM @BLOCKED2 ORDER BY BLOCKER_STATUS desc, [WAITING(ms)] DESCRETURNGOAppreciate replies.
-
Can you actually read your post?
_____________
Code for TallyGenerator -
Peter
Can you edit your original post to remove the formatting. Just highlight the line and select "Normal" from the style drop-down.
--------------------
Colt 45 - the original point and click interface
-
I've seen this happen, I tried to do something similar once before.
There's nothing wrong with your SP. Try running this in Query Analyzer and see what happens:
WHILE 1=1
BEGIN
WAITFOR DELAY '00:00:05'
SELECT getdate()
END
Comment out the WAITFOR command and it runs fine.
Something about the WAITFOR DELAY command inside a WHILE loop does NOT work.
If someone can explain it, I'd love to know how to get around it.
Temporary solution:
Change your SQLAgent job to just do
EXEC SP_BLOCK @BATCH='Y'
and schedule it to run every minute.
-
I googled on WAITFOR and found some code example here:
Quick example -
DECLARE @DELAYPERIOD NCHAR(12)
SET @DELAYPERIOD = '00:00:01'
DECLARE @Retries INT
SET @Retries = 0
DECLARE @MAXTRIES INT
SET @MAXTRIES = 5 -- Maximum number of tries before timing out
DECLARE @Modified DATETIME
WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
SET @Modified = GetDate() -- Set Modification Date
PRINT @Modified
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
This works, but you only see the output at THE END OF THE LOOP.
I think that is the key to the issue - the WHILE 1=1 loop never ends.
That's my current guess anyway
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply