This example creates a task that executes the stored procedure check_for_blocks every ten seconds. Typical execution times are from 0 to 30ms if no block is found.

SQL Server's Books on Line or T-SQL help documents have complete info on the parms of the sp_addtask stored procedure. The sp_addtask stored procedure can be found in the msdb database.

Replace the <userName> with that actual user name you want to monitor for a blocked state. It's only used in a description field in the msdb..systasks table here, so it will work as is, but puts a bogus description in msdb..systasks.


msdb..sp_addtask 'Block Monitor', 
      'TSQL', NULL, 'sa', 'admin', 1,
      4, 1, 2, 10, 0, 0, 19980101, 19991231, 
      060000, 220000, 000000, 080000, 0, 
      'Bill Wunder', 0, 0, 
      'check_for_blocks', 2, 2,
      'Check for Blocked <userName>'