Loss of Files Does Not a Suspect Database Make

I saw a post recently by someone asking how to get a database to be suspect. They said they had added a data file and then remove it, but the database wasn’t suspect. This was true in the past (v6.x), but it seems that as the product has advanced, there are more database states, and things like a missing file do not force a suspect state.

Instead the database on 2008 goes into an Offline status. Gail Shaw, who pointed this out to me in a blog, actually wrote her own blog on this topic, so I won’t duplicate the code.

I stopped my local SQL Server, then renamed one of the db files as shown:

suspect_a

In essence, this file is “lost” to SQL Server, just like it had been deleted, moved, etc. When I restart SQL Server, I see this:

suspect_b

No way to expand out db2 here. If I look at properties, I see it in “Shutdown” status

suspect_c

Strangely, if I right click, I have the “Take offline” available, not not “Take Online”. If I click that, I get the database offline, but I cannot then bring it back online. It gives me an error, as it should since the files are missing. The error log shows the issues with

suspect_d

I can rename the file without taking SQL Server down, and then bring the db online. It’s the same as recovering this disk drive or fixing the file somehow.

I’m glad this changed, since we should have more details about what happened, and a “suspect” database should be reserved for more serious issues. I think having this “shutdown” or “offline” is a better status.

The databases status values are here: http://msdn.microsoft.com/en-us/library/ms190442.aspx

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.