Database ‘xxx’ cannot be opened because it is offline

Got an error the other day which seemed pretty straight forward at first.

Msg 942, Level 14, State 4, Procedure test, Line 4
Database 'ghost' cannot be opened because it is offline.

I will provide some context to the cause of the error message before I go on with the story. A database was being migrated from one server to the other. As part of the migration, a DBA has implemented a strategy using a bridging database so that the database would appear available on both servers.

image

It was all nice and slick during the transition phase of the migration project. Then on the day of the cut-over, the DBA migrated the database following these steps:

1. Rename the database on Server B. For example, sp_renamedb ‘active’, ‘ghost’

2. Migrate the database from Server A to Server B.

3. Setting the renamed database ‘ghost’ to offline so no one can access it anymore.

In hindsight, the ‘ghost’ database should’ve been removed instead of the rename and offline approach.

After the database was migrated, users started to get error messages like the one shown at the start of the blog post. My initial assumption was that somewhere someone was still referencing the bridging database.

The next natural thing to do was to search in all the code and see if anyone anywhere was still referencing the database ‘ghost’. You probably already guessed, the search revealed nothing at all. Since I wasn’t the person working on the migration project, I wasn’t sure if I understood everything the DBA had done. Whilst discussing the error with the DBA and getting him to confirm that all the migration steps had been done, another senior DBA listening on the side interrupted with a comment that was like a lightning bolt from the clear sky “It would be the execution plans.

Because the offline ‘ghost’ database was not removed from the server, the database id was still cached in the execution plans.

image

The solution was then easy once the root cause was determined. The execution plan just needed to be refreshed by detaching the database.