Certain DMVs causing arithmetic overflow during "deadlock" - by Brandon Tucker

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 276695 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 5/10/2007 6:43:46 PM
Access Restriction Public


During a deadlock-ish server state, a query being used to examine the problem threw an 8115/arithmetic overflow error.  Three views being INNER JOINed in the query were sys.dm_tran_locks, sys.sysprocesses, and sys.databases.  A CROSS APPLY with sys.dm_exec_sql_text was then being used.  The query executed as expected before the deadlock then threw the error during it.  sys.dm_tran_locks and sys.dm_exec_sql_text seemed to work correctly when being run on their own.

	, l.resource_type
	) AS [object_name]
	, l.request_mode
	, l.request_type
	, l.request_status
	, sp.blocked
	, sp.lastwaittype
	, sp.status
	, sp.hostname
	, sp.program_name
	, sp.cmd
	, sp.loginame
	, qt.[text]
FROM sys.dm_tran_locks l
	JOIN sys.databases db
		ON l.resource_database_id = db.database_id
	JOIN sys.sysprocesses sp
		ON l.request_session_id = sp.spid
	CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS qt
Sign in to post a comment.
Posted by Haitao [MSFT] on 8/9/2007 at 11:00 AM
Hi Sleip,
Thanks for using our product and reporting the problem. We've looked at the scenario and I think the problem is in the query, specifically OBJECT_NAME(l.resource_associated_entity_id) .

sys.dm_tran_locks.resource_associated_entity_id is bigint where OBJECT_NAME takes an int. Where l.resource_type is not OBJECT, l.resource_associated_entity_id can be greater than INT_MAX and cause overflow. You can revise the queries like Razvan sugguested in the workaround (https://connect.microsoft.com/SQLServer/feedback/Workaround.aspx?FeedbackID=276695), or adding a filter such as (WHERE l.resource_type = 'OBJECT') to avoid the overflow.

Hope this helps,