SQL Server – sys.dm_exec_query_plan error when database is offline [ Msg 942 // cannot be opened because it is offline ]

Background

One runs some query and they end up being cached.

In a little while later, take the Database offline.

Later when one runs diagnostic queries, it is possible that errors might pop up

 

Query


	print '@@version ' + @@version
	
	;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	, ctePlan
	(
		[plan_handle]
	)
	as
	(
		select 
				[plan_handle]
					= cp.plan_handle
					  
		from   sys.dm_exec_cached_plans cp

		CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

	)
	select count(*)
	from   ctePlan

 

Error



@@version Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Msg 942, Level 14, State 4, Line 3
Database 'ReportServer' cannot be opened because it is offline.

 

Problem Identification

The problem is not with sys.dm_exec_cached_plans, but sys.dm_exec_query_plan.

It appears that a database has to be online to get its query plan.

 

Reproduce

To reproduce with Microsoft’s tools:

  1. Ensure that Reporting Services is installed and configured
  2. Using the services applet, stop the Reporting Services service
  3. Take the ReportServer and ReportServerTenpdb databases offline
  4. Issue the aforementioned query

Connect Items

  1. Join sys.dm_exec_cached_plans & sys.dm_exec_query_plan :- Error Message “cannot be opened because it is offline”
    ID :- 3115135
    Date :- 12/7/2016 12:28 PM
    Type :- Bug
    Status :- Active
    Link
  2. MS Connect Broken – Unable to upload files
    ID :- 3115137
    Date :- 12/7/2016 12:51 PM
    Type :- Bug
    Status :- Active
    Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s