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.


2
0
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

Description

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.

SELECT
	db.[name]
	, l.resource_type
	, COALESCE(
		OBJECT_NAME(l.resource_associated_entity_id), 
		OBJECT_NAME(qt.[objectid])
	) 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 Microsoft 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,

-Haitao