Querying DM_Exec_Query_Plan and an offline database causes an error. - by John.Morehouse

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<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 900570 Comments
Status Resolved Workarounds
Type Bug Repros 7
Opened 6/19/2014 9:03:33 AM
Access Restriction Public


When you attempt to interrogate the DMV sys.dm_exec_query_plan and there is an offline database, you potentially could run into an error stating that "Database '[DatabaseNameGoesHere]' cannot be opened because it is offline"
Sign in to post a comment.
Posted by Jason Brimhall (MCM) on 9/25/2017 at 1:08 PM
I have been able to reproduce this issue with SQL Server 2012, 2014, and 2017. If there is any database that has plans in cache and then is set offline, those plans remain in cache and prevent this function (sys.dm_exec_query_plan) from returning any results.
Posted by wilso010 on 6/19/2014 at 12:22 PM
I was able to reproduce this issue. When all databases where online there were 143 rows returned with no error and 30 of those rows were from database id 5. After setting database id 5 offline only 68 rows were returned with the error message "Database '[DatabaseNameGoesHere]' cannot be opened because it is offline".