Friday, September 21, 2012

SQL Server Upgrade Gotcha: Read-only Databases

Let me start off by saying that this is not Microsoft's fault. You really can't expect any different behavior in this scenario. The story goes a little something like this:

We had a couple datamarts that held historical information that could not be recovered from the source so we had them marked "read-only" to prevent them from getting mucked up. These datamarts were copies of another datamart that we do update on a regular basis so they have really long names to explain why they're hanging around in a read-only state.

Well, after the SQL Server 2012 upgrade I didn't notice that the databases got stuck in the "recovery" state. The indicator that SQL Server Management Studio puts at the end of the database name was hidden because the database name was so long. Since no one actually uses these datamarts, no one noticed for a long time. That is, until I needed the disk space for something else. You can't detach a database in a recovery state you know.

The cause for this problem is really simple and should be expected. When a SQL Server instance is upgraded all the databases get upgraded as well when the SQL Server service starts up for the first time after the upgrade. However, if your database is marked "read-only" SQL Server promises not to make any changes to it. So, you end up with a database that SQL Server doesn't know what to do with.

The solution is quite simple. Just make the database writeable. As soon as you do that, SQL Server restarts the database and performs the upgrade. This should only take a couple of seconds. As soon as it's done you can set it back to "read-only" again. You probably smart enough to figure out how to do this on your own but a script follows. It's the least I can do if you read this far for the solution.

ALTER DATABASE [MyDB] SET READ_WRITE
GO
ALTER DATABASE [MyDB] SET READ_ONLY
GO


Yes, it's that simple. Just change the database name to match your database, of course.

Seen anything related to SQL Server upgrades that you want to share? Post a comment or link to a blog post that tells your story!

No comments:

Post a Comment