Seeing a database in any state other than “online” makes me a little nervous. For one thing, it’s one of those things I don’t see often, so I have to rub a few brain cells together to remember what I did the last time. For another, there’s no guarantee that you’ll be able to get the database back online. That’s scary.
But one of our clients had three SQL Server databases in “recovery pending” state this week. Trying to bring the databases online produces these errors:
First:
"Error: 5173, Severity: 16, State: 1. One or more files do not match the
primary file of the database. If you are attempting to attach a database,
retry the operation with the correct files. If this is an existing database,
the file may be corrupted and should be restored from backup."Second:
"Error: 928, Severity: 20, State: 1: During upgrade, database raised exception
945, severity 14, state 2, address (). Use the exception number to determine
the cause."The 928 error was a typical Microsoft red herring having to do with database compatibility levels and permissions. However, trying to bring the database online added a little meat to the 5173 error’s bones:
"Database [dbname] cannot be opened due to inaccessible files or insufficient
memory or disk space. See the SQL Server errorlog for details."
And also:
"Log file [log name] does not match the primary file. It may be from
a different database or the log may have been rebuilt previously."
I checked file permissions, memory and disk space. Everthing was kosher there. I knew that would be the case because life just isn’t that easy.
Which left the log files. These databases (along with a dozen others in the instance) had been involved in a SAN crash. They were migrated to a new disk, but performance was awful — data warehouse processes were taking 10 times longer to run than they had previously. So the client built a new drive for the logs, migrated the logs, changed the drive letter, and altered the file paths stored in the master database. Plenty of opportunities there to corrupt the log.
I had no backups to work with, so I needed to recover the databases. I put one of the db’s in emergency mode to try to run CHECKDB, but CHECKDB couldn’t access the files either.
So, on to the last resort (I’ll spare you the trial and error and get down to the solution):
1. Set the database status to emergency:
ALTER DATABASE [dbname] SET EMERGENCY
2. Put the database in multi-user mode:
ALTER DATABASE [dbname] SET MULTI_USER
3. Detach the database:
EXEC sp_detach_db '[dbname]'
4. Reattach the data file only:
EXEC sp_attach_single_file_db @dbname = '[dbname]',
@physname = N'[mdf path]'
The point here is to get rid of the corrupt log and let SQL Server build a new one. Three words to the wise:
First, according to Microsoft, you should use sp_attach_single_file_db ONLY on data files detached using sp_detach_db. So don’t use the GUI — use sp_detach_db.
Second, put the database in MULTI_USER mode before detaching. SQL Server can’t build a new log if the data file is read-only. If you detach in SINGLE_USER mode, Plan B is a real pain.
Third, don’t make your DBA do this. Back up your databases.