SYS.DM_EXEC_REQUESTS Changes Between Versions Of SQL Server

I was recently doing some work with a backup verification routine that is meant to run on various versions of SQL Server and came across some variations in the way the RESTORE VERIFYONLY command is reported by sys.dm_exec_requests between SQL Server 2005, 2008 R2, and 2012. I haven’t seen this mentioned anywhere, so I thought I’d document it here. This is useful information to have if you are doing any type of programming based on the output of sys.dm_exec_requests while using the RESTORE VERIFYONLY command.

The table below notes the differences. Note the change of field length in SQL 2008 / 2008 R2, which results in what appears to be the truncation of the command text. I have not run this test on SQL Server 2014.

The command run was:

RESTORE VERIFYONLY
FROM DISK = N'<backup path and file here>'

And the code to view sys.dm_exec_requests while the above is running is:

SELECT	session_id,
		status,
		percent_complete,
		command,
		wait_type,
		wait_time,
		CAST(estimated_completion_time AS DECIMAL(20,3))/1000.0/60.0 AS EstimatedCompletionTimeInMinutes,
		last_wait_type
FROM	sys.dm_exec_requests
WHERE session_id >50
order by percent_complete desc

SQL Server Version

Sys.dm_exec_requests.Command data type

Command text reported

2005

Nvarchar(32)

RESTORE VERIFYON

2008 / 2008 R2

Nvarchar(16)

RESTORE HEADERON

2012

Nvarchar(32)

RESTORE HEADERONLY

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.