sys.dm_tran_locks or How to avoid a TRUNCATE TABLE to be blocked

• Tore Bostrup-382308

  SSC-Addicted

  Points: 448

  More actions

  October 20, 2016 at 4:28 pm

  #331486

  I have a couple of processes that will (re-)populate a table nightly - a simple TRUNCATE TABLE and INSERT INTO / SELECT FROM. There should not be any application activity at this time, but experience shows that occasionally, someone will leave something up that holds a lock. I'd like to "surgically" identify and kill any SPIDs that would cause the TRUNCATE to be blocked.

  I don't want to affect anything that won't stop the TRUNCATE (and subsequent INSERT INTO) operations, but I do want to make sure the operations succeed. It seems like overkill to kill all SPIDs and set the database in single user mode for this.

  It seems a very simple approach (not tested at this point) would be to simply look for SPIDs with entries in sys.dm_tran_locks, limiting it to the database (resource_database_id) and table (resource_associated_entity_id) in question, and eliminating the SPID of the process running the KILL.

  The table(s) in question will have a Primary Key (non-clustered) and a clustered, non-unique index.

  Do I need to look anywhere else? Do I need to also consider the possibility that an allocation unit, etc. with a resource_associated_entity_id different from the table I'm concerned with? Do I need to restrict to certain types of locks? Other concerns?

  What happens if the KILL SPID triggers a ROLLBACK? How do I monitor the rollback completion? If the transaction will remain in sys.dm_tran_locks until rollback is completed, is there a value that indicates it so I can implement waits?

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KillSPIDsLockingTable]') AND type in (N'P', N'PC'))

  DROP PROCEDURE [dbo].[KillSPIDsLockingTable]

  GO

  CREATE PROCEDURE KillSPIDsLockingTable (

  @TableName sysname,

  @DatabaseName sysname = DB_NAME

  )

  AS

  BEGIN

  DECLARE @killspid INT

  SELECT TOP 1 @killspid = tl.request_session_id

  FROM sys.dm_tran_locks tl

  WHERE resource_database_id = DB_ID(@DatabaseName)

  AND resource_associated_entity_id = OBJECT_ID(@TableName)

  -- How about Allocation Units, HOBTs, items with other names?

  AND tl.request_session_id <> @@SPID

  WHILE @@ROWCOUNT = 1

  BEGIN

  --KILL @killspid

  PRINT @killspid-- Just testing ATM

  SELECT TOP 1 @killspid = tl.request_session_id

  FROM sys.dm_tran_locks tl

  WHERE resource_database_id = DB_ID(@DatabaseName)

  AND resource_associated_entity_id = OBJECT_ID(@TableName)

  -- How about Allocation Units, HOBTs, items with other names?

  AND tl.request_session_id <> @@SPID

  -- Exclude processes that are rolling back, possibly from an issued KILL?

  END

  -- Wait for ROLLBACKS to complete after spids are killed...?

  END

  GO

  --SELECT * FROM dbo.TestData AS td

  EXEC KillSPIDsLockingTable @TableName = 'dbo.TestData', @DatabaseName = 'TBTest'

  ---

  Regards,

  Tore Bostrup