NOTE: The below was a very particular scenario, the best way to recover a SUSPECT Database is to restore from a BACKUP.
Scenario
After a network issue with the SAN one of our databases went to suspect mode. Checking the logs both Windows App Logs and SQL Server Error Logs we got this:
LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.
The log for database 'X' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
During undoing of a logged operation in database 'X', an error occurred at log record ID (326627:109731:1). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
First we verified the Log Drive was present and the Database Log file was there (it was) so this was good.
We try to bring the database online since the log file was there and there was no need to rebuild it.
ALTER DATABASE [x] SET ONLINE
But we hit this error.
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database 'TQ_ThreatLinQ'. Try again later.
Verified there were no process running against this database but just in case I try to put it in RESTRICTED_MODE
ALTER DATABASE [x] SET RESTRICTED_USER NO_WAIT
Same error, the DB STARTUP background process was blocking any attempt against this database, however it was not doing a thing.
Since this Database was part of a dedicated cluster we fail over the DB Instance and that fix the issue.
Recovery went successfully this time for the database.
Recovery is complete. This is an informational message only. No user action is required.
