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


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



	print '@@version ' + @@version
	;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	, ctePlan
					= 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



@@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.



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
  2. MS Connect Broken – Unable to upload files
    ID :- 3115137
    Date :- 12/7/2016 12:51 PM
    Type :- Bug
    Status :- Active

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s