Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DB in 'Suspect' status

Author  Topic 

kowani1
Starting Member

42 Posts

Posted - 2006-12-17 : 19:02:12
Hi All,

My SQL Server 2000 had to be rebooted this morning and I founf out that one of my bigger DBs(50G dd file) came up with a 'suspect' status.

Is there a way I can continue to use the existing data file without dropping the DB, then creating new datafiles and recovering from recent backups, coz the current datafile may be corrupted.

thanks.
jungle dba

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-12-17 : 19:15:28
any errors in your error log? what about your system or application event logs?


-ec
Go to Top of Page

kowani1
Starting Member

42 Posts

Posted - 2006-12-17 : 19:38:28
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 12/18/2006
Time: 7:17:56 AM
User: CORP\admin_iw
Computer: BSSWH01
Description:
Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset 0x0000064aa4a000 in file 'D:\sqldata\Repository\Repository_Data.MDF'.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-17 : 22:06:54
see
http://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.html

It shows how to access a suspect database towards the end.
Your torn page usually means you will beed to restore from backup but by folowing the above you should be able to access most of the data - but not update it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

kowani1
Starting Member

42 Posts

Posted - 2006-12-17 : 22:36:14
thanks for the URL.. It does relate well to my current situation with the 'torn pages' error on my current production MDF.

I'm certainly gonna have a icy cold beer if this works.


Jungle DBA.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2006-12-17 : 22:40:01
You won't be able to access it via enterprise manager but you can via query analyser.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-18 : 04:24:13
If you have a FULL backup from before the problem, and ALL Transaction Backups SINCE there is a good chance you can restore those an get a clean backup.

Steps would be:

1) Set database to SINGLE USER (i.e. chuck everyone else out)
2) Make a FINAL TLog backups
3) Restore FULL backup to a NEW TEMPORARY database (preferably on different server) in STANDBY
4a) Perform DBCC CHECKDB
4b) If errors go back to an earlier FULL backup
5) Restore all TLog backups in order
6) Perform another DBCC CHECKDB to check the database health

If that's all OK you should be able to Backup the "temporary" database and restore it to the correct location (make another DBCC CHECKDB after that)

TORN PAGE is normally caused by hardware failure - check your Event Log.

You may want to have a read through the Data Corruption Issues forum for similar scenarios, and actions that were recommended / tried

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=22

Kristen
Go to Top of Page
   

- Advertisement -