May 22, 2014 at 12:53 pm
We're running a piece of code to get spids of processes with locks on a table named "arss", then proceeding to kill them one at a time. As far as I know, this code used to work fine until SQL 2012. The error only comes up when the code finds a spid, but when it finds one, it throws out this:
Arithmetic overflow error converting expression to data type int. [SQLSTATE 22003] (Error 8115)
By running this in a query window, I can see that it has an issue with the bolded code. Anyone know why? And maybe suggest a better way than MIN of grabbing one spid when there may be more than one?
DECLARE @spid int
SELECT
@spid = MIN(l.request_session_id)
FROM
sys.dm_tran_locks l
INNER JOIN sys.dm_tran_active_transactions t ON l.request_owner_id=t.transaction_id
WHERE
l.request_mode like 'Sch%'
AND OBJECT_NAME(l.resource_associated_entity_id)='arss'
-- AND t.transaction_begin_time < DATEADD(MINUTE, -5, GETDATE())
WHILE @spid IS NOT NULL
BEGIN
BEGIN TRY
EXECUTE ('KILL ' + @spid)
END TRY
BEGIN CATCH
PRINT 'Could not kill Session ID ' + CONVERT(nvarchar, @spid)
END CATCH
SELECT
@spid = MIN(l.request_session_id)
FROM
sys.dm_tran_locks l
INNER JOIN sys.dm_tran_active_transactions t ON l.request_owner_id=t.transaction_id
WHERE
l.request_mode like 'Sch%'
AND OBJECT_NAME(l.resource_associated_entity_id)='arss'
AND t.transaction_begin_time < DATEADD(MINUTE, -5, GETDATE())
END
More info: We're running a SSIS job overnight to drop table "arss" and it seems to hang when there are locks. If there is a better way of doing this, we'd love to know.
Pretty green and "accidental" DBA here, so any help is most appreciated!
Jason
May 22, 2014 at 1:03 pm
Looks to me like the issue is with the Kill statement
EXECUTE ('KILL ' + @spid)
you need to cast the spid to varchar because 'Kill ' cannot be converted to INT.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 22, 2014 at 1:06 pm
The problem is that OBJECT_NAME expects an int value and l.resource_associated_entity_id is a bigint.
Maybe changing the last condition would solve the proble, but I'm not sure if it will give the result you're looking for.
AND l.resource_associated_entity_id= OBJECT_ID('arss')
May 22, 2014 at 1:26 pm
I suggest you change from the + (addition) concatenation to the CONCAT function, it should implicitly cast the input to the appropriate character type if it does what says on the tin.
😎
May 22, 2014 at 1:31 pm
It's very possible that there is an issue within the WHILE loop, but I got the error even while running just the top part of the script.
The datatype on the resource_associated_entity_id may be something to investigate. Like I say, I'm really green, so lots of Googling. 🙂
May 22, 2014 at 1:31 pm
Luis Cazares (5/22/2014)
The problem is that OBJECT_NAME expects an int value and l.resource_associated_entity_id is a bigint.Maybe changing the last condition would solve the proble, but I'm not sure if it will give the result you're looking for.
AND l.resource_associated_entity_id= OBJECT_ID('arss')
Nice catch
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 22, 2014 at 1:39 pm
jmoorhead14 (5/22/2014)
DECLARE @spid int
SELECT
@spid = MIN(l.request_session_id)
FROM
sys.dm_tran_locks l
INNER JOIN sys.dm_tran_active_transactions t ON l.request_owner_id=t.transaction_id
WHERE
l.request_mode like 'Sch%'
AND OBJECT_NAME(l.resource_associated_entity_id)='arss'
-- AND t.transaction_begin_time < DATEADD(MINUTE, -5, GETDATE())
...
Try this change:
...
WHERE
l.request_mode like 'Sch%'
AND l.resource_associated_entity_id = OBJECT_ID('arss')
-- AND t.transaction_begin_time < DATEADD(MINUTE, -5, GETDATE())
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 22, 2014 at 1:53 pm
Thanks for all the quick replies! We've edited the code and will hopefully get better results. You guys are pretty good to catch that needle in a haystack. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply