One of my clients contacted me when there was business down situation. They had one of their production databases in suspect state and unable to drop it also. In this blog, we would learn about how to drop a suspect database.
When they contacted me, I started GoToMeeting and got connected in a few minutes. I asked them about the history but there was not much aware of it. This was one of the very critical databases for their business. They have regular backups taken and they wanted to restore the backup if things are not getting better in the next few minutes.
I tried to restart the SQL Service. the database shows “In-recovery” mode. When I checked the SQL ERRORLOG, it showed that the database started the recovery process. It went to recovery up-to 10% and then failed with a dump. Here is my earlier blog about various causes of dumps.
- SQL SERVER – Too Many SQLDump Files Consuming a Lot of Disk Space. What Should You Do?
- What Part of the SQLDump Suggest Database Corruption? – Interview Question of the Week #188
Here was the message, for any operation, we tried in the database.
Msg 922, Level 14, State 1, Line 1
Database ‘Database_Name’ is being recovered. Waiting until recovery is finished.
When I checked ERRORLOG, I found below:
<DateTime> spid23s Error: 824, Severity: 24, State: 2.
<DateTime> spid23s SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:67; actual 0:3342337). It occurred during a read of page (1:66) in database ID 15 at offset 0x00000000088000 in file’F:\SQLMDF\PROD_DB_Data.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
<DateTime> spid23s Error: 3414, Severity: 21, State: 1.
<DateTime> spid23s An error occurred during recovery, preventing the database ‘PROD_DB’ (database ID 15) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
SOLUTION/WORKAROUND
As we can see above, one we start SQL Service, the database goes through recovery and its failing there. Due to this, we were not able to drop the database. I have not seen this earlier as I was always able to drop such databases for restore. Here are the steps we did to drop the database and perform the restore:
- Stop the SQL Server service.
- Take a safe copy of existing MDF/NDF and LDF files.
- Rename the file (MDF or LDF or both) related to this database.
- Start the SQL Server service.
Since the files are not available the database startup would fail, and the database would go to “Recovery Pending” state. After this, we were able to drop the database. As I mentioned they were ready to restore it from the backup and it worked well.
Have you ever come across such a situation? What did you do to fix it?
Reference: Pinal Dave (https://blog.SQLAuthority.com)
5 Comments. Leave new
Hi Pinal,
Thanks for new idea.
I was working with AlwaysOn databases where primary server database was in recovery state instead of synchronized state. More importantly it was production database.
After finding causeS , I came to know that system disk was full and same databases LDF file was increased at 400+ GBs.
I shrank database using DBCC SHRINKFILE (1, TRUNCATEONLY); and space was released.
Vaibhav Shukla
Thanks for sharing your situation and solution Vaibhav.
Yesterday I got a call from other project. They have stucked in similar situation.
We had a similar situation in the past where recovery is always struck at a percentage and it does not proceed further even after hours. It blocks every single command we tried to run on the database to bring it back online. Solution we followed was more or less in similar lines like you explained, however we created empty files with same names when the sql service is put down. This made us to bring the database into emergency mode after the service was restarted. Later on we have removed it from AG and re-added it back.
I have this situation once, but it was moving the database files. I took the db offline and copy the mdf and ldf to another disk, then i changed the filename property and when i bring it online it came Suspect mode. After some minutes i realize that i didn t let the file to complete the copy. I took the db offline again, copy he file and came online good.
PD: Sorry for my english