DB IN SUSSPECT STATUS

Question

Post reply

DB IN SUSSPECT STATUS

liorvikel

SSChasing Mays

Points: 651
More actions
July 31, 2016 at 10:44 pm

#312146

hey
plz help
i didnt have backup at all .
Error: 9003, Severity: 20, State: 1.
2016-07-31 14:10:28.68 spid14s The log scan number (814596:6991:1) passed to log scan in database 'Harmony' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
2016-07-31 14:10:28.68 spid14s Error: 3414, Severity: 21, State: 1.
2016-07-31 14:10:28.68 spid14s An error occurred during recovery, preventing the database 'Harmony' (database ID 5) 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.
2016-07-31 14:10:28.68 spid14s CHECKDB for database 'Harmony' finished without errors on 2015-11-30 10:02:30.837 (local time). This is an informational message only; no user action is required.
2016-07-31 14:10:28.80 spid13s The Service Broker protocol transport is disabled or not configured.
2016-07-31 14:10:28.81 spid13s The Database Mirroring protocol transport is disabled or not configured.
August 16, 2016 at 10:36 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Start by running DBCC CheckDB ('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS and post the full and unedited output.

At this point DO NOT detach the database!

😎

liorvikel SSChasing Mays Points: 651 More actions · Answer 2

now its online..

i do this procedure:

http://www.sql-server-performance.com/2015/recovery-sql-server-suspect-mode/2/

but how can o check the last writing on this db?

to see if the data consistent

thanks

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

liorvikel (7/31/2016)
now its online..
i do this procedure:
http://www.sql-server-performance.com/2015/recovery-sql-server-suspect-mode/2/
but how can o check the last writing on this db?
to see if the data consistent
thanks

Ooops, so you ran DBCC CheckDB ('DatabaseName', REPAIR_ALLOW_DATA_LOSS) and got it online again:pinch:. The REPAIR_ALLOW_DATA_LOSS should NOT be used unless all other options are exhausted!

😎

Run DBCC CheckDB ('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS and post the full and unedited output.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Quick question, did you take any kind of a backup before you started the repair exercise?

😎

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 5

You can also take the help of these links

http://www.sqlrecoverysoftware.net/blog/sql-error-9003.html

http://sqltechtips.blogspot.com/2015/12/fix-error-9003-model-database.html

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Eirikur Eiriksson (7/31/2016)
Quick question, did you take any kind of a backup before you started the repair exercise?
😎

In this case no data would have been lost, the repair rebuilt the log file. There may have been a loss of transactional consistency, eg part-done transactions committed or committed ones only partially reflected in the DB.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

johnwalker10 (7/31/2016)
You can also take the help of these links
http://www.sqlrecoverysoftware.net/blog/sql-error-9003.html
http://sqltechtips.blogspot.com/2015/12/fix-error-9003-model-database.html

Both of those are horrible advice. (Emergency mode repair listed as the first option. Reinstall SQL?!?!)

This may sound blunt, but please avoid giving advice on corruption situations unless you know what the advice will do to a DB. It's very easy to quote a blog that looks legit, but when the advice is followed results in massive data loss.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 8

GilaMonster (8/1/2016)
Eirikur Eiriksson (7/31/2016)
Quick question, did you take any kind of a backup before you started the repair exercise?
😎
In this case no data would have been lost, the repair rebuilt the log file. There may have been a loss of transactional consistency, eg part-done transactions committed or committed ones only partially reflected in the DB.

My thought here was that anyone who approaches a DB corruption with a "poke a needle into the clockwork" methods should at least have a fallback to a previous state, no telling of which methods may be applied and what kind of damages done.

😎