February 6, 2010 at 10:31 pm
Comments posted to this topic are about the item A guide to recover a database out from Suspect mode
February 7, 2010 at 11:54 pm
Caution! Below steps will cause you to loose data...
It's "lose data."
Nice article, thanks. I'll remember [font="Courier New"]RESTORE DATABASE WITH RECOVERY[/font]
February 8, 2010 at 12:38 am
very helpful article. thx
February 8, 2010 at 1:56 am
Good article - To the Point.
I had a problem of SUSPECTED Database few years back (SQL2000). I updated the status in sysdatabases, and the problem never reoccured.
Thanks
B.K.Prasad
February 8, 2010 at 2:06 am
Well thats a really helping things to bring the Database Online. But before going to perform the thrid step I would like to go for DBCC DBRECOVER(DBNAME) command. Also after setting the Database in emergency do not forget to execute the DBCC Checkdb command, this will give what are the objects or transcations having the problem. Then in the end go for DBCC CHECKDB with allowloss option.:-)
February 8, 2010 at 2:23 am
Very helpful arcticle..thanks a lot. Wish I should not come across this situation. 🙂
February 8, 2010 at 5:11 am
Paul S. Randal is the definitive source on Database Corruption and recovering corrupt databases. Check him out at http://www.sqlskills.com/BLOGS/PAUL/category/CHECKDB-From-Every-Angle.aspx He wrote DBCC CHECKDB for Mircosoft. A lot of things in this article and a whole lot more is in his site....
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
February 8, 2010 at 6:47 am
Another possible issue/solution, probably under possibility 1:
I get a lot of SAN detatch errors from a certain iSCSI SAN. The SAN Drives will drop, and reattach. Fortunately, the Windows OS sees the drives as being back but once SQL Server looses the connection, there is no reconnect. These will show up as suspect databases (multiple). Solution so far (and I have been lucky) is simply restart the SQL Service. Usually shows up as a 9002 error in the error log.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
February 8, 2010 at 7:08 am
Possibility 1: Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file. The solution is to place the missing data/log file in proper location. The SQL Server Error Log error message will give you the exact name and path of the missing file. Once you place the file execute below command to bring your database online with no data loss.
RESTORE DATABASE WITH RECOVERY
Possibility 2: SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool (like Antivirus), which puts an exclusive lock on the data/log file. To resolve it, use process explorer and kill the file handler which placed lock on the file. You may want to involve your System Admins to get this step executed. Then execute below command and you will have your database online with no data loss:
RESTORE DATABASE WITH RECOVERY
Sorry, but I am going to completely, 100% disagree with you.
RESTORE DATABASE <DB Name> WITH RECOVERY is what you run if your database is in a RESTORING state because an earlier backup was restored with NORECOVERY. It is not used to bring a database out of the suspect state.
I did a quick test. Created a DB, shut SQL down, renamed the data file and restarted SQL. The DB came up in the RECOVERY PENDING state (not suspect). I then followed your instructions, renamed the file correctly and ran the RESTORE DATBASE statement.
RESTORE DATABASE SuspectDB WITH RECOVERY
Msg 3148, Level 16, State 3, Line 1
This RESTORE statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The correct solution, when a file has been moved or was locked by some other app when SQL started, is to rectify the root cause first (put the file back, remove whatever was locking it), then take the DB offline and bring it back online (or restart the SQL Service). That will allow SQL to open the DB again, this time find all the files and run the restart_recovery again this successfully.
This will work if the DB state is RECOVERY PENDING (meaning that restart-recovery has not started due to missing or inaccessible files). If the DB state is truly suspect, it means there's a problem in the data file or log file, the restart recovery started (so all files are accessible) but could not complete. That's a lot more severe than just a missing file and usually indicates corruption of the data/log that was encountered during the restart-recovery.
One additional note on Emergency mode repair. It's a last resort. If it fails, there's nothing else that can be done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2010 at 7:22 am
I'm a little surprised that you're not recommending running CHECKDB on the database in question. Yes, it's marked as suspect, and yes, you might find a cause in the error log, but then again, you might not. Running a database consistency check will frequently identify the root cause. It might be something recoverable through other means than running repair with data loss, which is a very dangerous thing to do to a production system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2010 at 7:32 am
A suspect DB has to be set to Emergency mode before CheckDB can be run. There's a brief (one sentence) mention of running CheckDB in Scenario 3, right before the checkDB with repair. It should have been a lot clearer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2010 at 7:42 am
GilaMonster (2/8/2010)
A suspect DB has to be set to Emergency mode before CheckDB can be run. There's a brief (one sentence) mention of running CheckDB in Scenario 3, right before the checkDB with repair. It should have been a lot clearer.
Ah, I did miss that part, but scenario 3 is not the only place I'd run it, depending on the situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 8, 2010 at 7:45 am
It is worth mentioning that after a repair with data loss, not only is the corrupted record lost, but the whole data page. The good news is that with a good backup strategy, the lost page can be restored from backups.
Considering that the transaction is written to the transaction logs before committed to the database, you may get lucky and have no data loss after restoring the page.
February 8, 2010 at 8:13 am
@robert Not true - the action CHECKDB takes depends on the corruption - there are some cases where I delete just the corrupt record rather than the whole page.
IMHO this article should not have been published in it's current form. It does not explain all the ramifications of going straight to emergency mode repair (running REPAIR_ALLOW_DATA_LOSS in emergency mode), doesn't explain the SUSPECT and RECOVERY_PENDING statuses correctly, and also doesn't advocate attempting an export/import into a new database rather than running repair.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 8, 2010 at 9:21 am
This article would be fine except it doesn't cover the ground sufficiently and you could end up restoring a database over new data when there was nothing wrong in the first place.
Before restoring a database that is in suspect mode, check your error log. You may find that a drive has gone offline. You may be able to bring that drive back online with the data file in tact. I have done this with SAN connections that are failing.
It pays to be very thorough before jumping to the conclusion that you need to restore a production database over new data.
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply