I'm trying to retrieve cached execution plans for a specific database object, but this fails because of cached plans which belong to a mirrored database in MIRROR state.

The below query, without any additional WHERE clauses is failing with the following SQL error:

Msg 954, Level 14, State 1, Line 1 The database "DatabaseName" cannot be opened. It is acting as a mirror database.

FROM    sys.dm_exec_query_stats AS deqs 
CROSS APPLY     sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

I suspect what happens is the sys.dm_exec_query_plan function is first trying to parse all the plan handles from the cache, but fails on cached objects for a mirrored database.

Does anyone know if there are any ways to get around this, T-SQL wise?

Off course I could execute DBCC FREEPROCCACHE to clear the cache, however I'm hoping for other solutions. I'm a bit amazed this function is not discarding any objects from mirrored databases while trying to parse the plans.

  • Does it fail if you only run SELECT * FROM sys.dm_exec_query_stats AS deqs ?
    – Hannah Vernon
    Mar 21, 2013 at 14:36

1 Answer 1


The sys.dm_exec_query_plan has a column returned for dbid, you could just filter on that for the database that is being mirrored.

Or, you could get fancy and nest a sub-query that filters for any dbid that is in a mirrored state by going out to the sys.database_mirroring DMV.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.