Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

DB corrupted with event 17052 and Error: 823, Severity: 24, State: 2 Expand / Collapse
Author
Message
Posted Monday, September 12, 2011 3:21 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 791, Visits: 1,420
I was approached today for a crashed SQL 2000 SP2 server with a single 800G database on it. Log is 20G. The database crashed due to the reason in the subject.

I successfully recovered another backup from the same server, unfortunately it was long long time ago and size is only 3G, but structure is the same.

I wonder how do I recover the rest of the data? Where should I start?

I checked sysdatabases, I don't see the damaged database, not even in suspect mode.

Any clue is greatly appreciated.
Post #1173804
Posted Monday, September 12, 2011 3:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
Run this and LET IT FINISH

DBCC CHECKDB(DbName) WITH NO_INFOMSGS, ALL_ERRORMSGS


Did you try to restore that backup on a test server to see if it works and what data it contains?


Any way you can take a backup of the current <corrupt> db?


Any reason (after last time), that you couldn't setup backups on all servers and that you didn't cehck if the backups were usable (we need to know of your current limits)?
Post #1173806
Posted Monday, September 12, 2011 3:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
Did someone detach or drop the DB? Only reasons it wouldn't be in sysdatabases.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1173809
Posted Monday, September 12, 2011 3:43 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 791, Visits: 1,420
Ninja's_RGR'us (9/12/2011)
Run this and LET IT FINISH

DBCC CHECKDB(DbName) WITH NO_INFOMSGS, ALL_ERRORMSGS


Did you try to restore that backup on a test server to see if it works and what data it contains?


Any way you can take a backup of the current <corrupt> db?


Any reason (after last time), that you couldn't setup backups on all servers and that you didn't cehck if the backups were usable (we need to know of your current limits)?


the backup is really not a backup, i believe it was created several years ago and when the owner found it's growing too fast then he decided to create another data file and start growing there and reaches 800g now and crashed.

that "backup" is working fine now.

i spoke to the guy who is responsible for it, he said due to the db size too big, no backup have been made in the past but he is considering a bk solution but unfortunately it crashed b4 it is implemented.

due to the fact that moving the damaged db files to another server is not applicable now, i will have to do everything on site.

pls consider a relatively safer suggestion.

thanks lots.
Post #1173816
Posted Monday, September 12, 2011 3:44 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 791, Visits: 1,420
GilaMonster (9/12/2011)
Did someone detach or drop the DB? Only reasons it wouldn't be in sysdatabases.


i don't know, the guy did tell me he saw it was marked as suspect
Post #1173817
Posted Monday, September 12, 2011 3:48 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 791, Visits: 1,420
Ninja's_RGR'us (9/12/2011)
Run this and LET IT FINISH

DBCC CHECKDB(DbName) WITH NO_INFOMSGS, ALL_ERRORMSGS



this is not gonna work becoz the db is not even in sysdatabases

here is the error:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'SELS'. No entry found with that name. Make sure that the name is entered correctly.
Post #1173819
Posted Monday, September 12, 2011 4:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
halifaxdal (9/12/2011)
Ninja's_RGR'us (9/12/2011)
Run this and LET IT FINISH

DBCC CHECKDB(DbName) WITH NO_INFOMSGS, ALL_ERRORMSGS



this is not gonna work becoz the db is not even in sysdatabases

here is the error:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'SELS'. No entry found with that name. Make sure that the name is entered correctly.


I got 1 idea left but it's not going to be pretty. I'm hoping Gail has a better option cause mine's going to require a heck of a lot of free space and quite certainly losing data and you won't be able to know what you lost.


What's your SLA, RTO & RPO on this DB? And when were you called in?
Post #1173826
Posted Monday, September 12, 2011 4:42 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
halifaxdal (9/12/2011)
GilaMonster (9/12/2011)
Did someone detach or drop the DB? Only reasons it wouldn't be in sysdatabases.


i don't know, the guy did tell me he saw it was marked as suspect


A suspect database still appears in sysdatabases. If it's not in sysdatabases it's been detached or dropped and there's absolutely nothing that can be done unless you can find the files for that database.

If the DB has been dropped, it's gone.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1173830
Posted Monday, September 12, 2011 5:38 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 791, Visits: 1,420
The guy said he saw the DB in suspect but now it's not even in sysdatabases; whether he dropped it or not, I have no idea. I tried to attach it, I got error 5159: Operating system error 2 (The system cannot find the file specified.) on device 'D:\SQL\SELS_Log.LDF' during ForwardLogBlockAHeadAsync. Could not open new database 'SELS'. CREATE DATABASE is aborted.

Maybe I can rename the mdf/ldf, create the DB, then replace the empty mdf/ldf with the crashed one?
Post #1173844
Posted Monday, September 12, 2011 6:31 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 791, Visits: 1,420
I renamed the original two damaged mdf/ldf, I created the db with the same name so now I have 2 files that make the db up and running.

I then stopped the db, deleted the two empty files, renamed the damaged files back to the original names.

I tried to bring the db online, failed.

I checked the log file, please see the attachment.



  Post Attachments 
errorlog201109122030.txt (12 views, 76.92 KB)
Post #1173854
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse