We had someone rebuild the drives that SQL database and log
files live on. No big deal, but SQL was
not stopped cleanly before performing this action. Now it is time to call on
the DBA to fix the issue.
First problem I ran into was that SQL would not start. A quick review of the event log showed:
CREATE FILE encountered operating system error 3(The
system cannot find the path specified.) while attempting to open or create the
physical file 'T:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\Data\tempdb.mdf'.
Sure enough the T drive was one that was rebuilt.
The fix for this error is to create the path that SQL is
looking for for tempdb. I added all the
folders for T:\Program
Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ and SQL is
off and running.
But my user databses are in Recovery Pending now.
Start with:
alter database DB_UTILS set online with rollback immediate;
Folders don’t exist on the rebuilt drive again. Add all the folders where the ldf files lived
and databases are running.
But wait, not all databases are coming online. Looks like a few that did not shut down
cleanly:
File activation failure. The physical file name
"F:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\Data\ReportServer_log.ldf" may be
incorrect.
The log cannot be rebuilt because there were open
transactions/users when the database was shutdown, no checkpoint occurred to
the database, or the database was read-only. This error could occur if the
transaction log file was manually deleted or lost due to a hardware or
environment failure.
Msg 5181, Level 16, State 5, Line 1
Could not restart database
"ReportServer". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Let’s set our database into Emergency mode and lock everyone
else out:
ALTER DATABASE ReportServer SET EMERGENCY
GO
ALTER DATABASE ReportServer SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Now we can rebuild the
log file:
DBCC CHECKDB('ReportServer',REPAIR_ALLOW_DATA_LOSS)
And let the users back
in:
ALTER DATABASE ReportServer SET MULTI_USER
You do need to resize
the log file and take a new full backup because the LSN chain has been broken.
