Suspect Database - Need Help!!

Question

Suspect Database - Need Help!!

Keith Ward

Valued Member

Points: 65
More actions
May 10, 2005 at 3:19 pm

#88395

HELP!! We have a Windows 2000 w/SP4 and MSSQL 2000 w/SP3a. Developer called me today and said he has a server that has a suspect database. Claims he was executing some create table scripts and the server seemed like it was non-responsive. He said he rebooted and when the server came back online his database was suspect. The following errors are present in the log:
Error while undoing logged operation in database 'dbname'. Error at log record ID (26990:18320:431)..
Error: 3314, Severity: 21, State: 3
An error occurred while processing the log for database 'dbname'..
Error: 9004, Severity: 23, State: 2
Supposedly they had tried to detach and retach the database with no luck. Wish I would have got called before that happened. At this point I copied the original log file to separate directory and then deleted the file and tried a single_file_db attach with the same errors.
Of course the big kicker, nobody bothered to create any type of backups. Got to love coming to a new shop where you are the only DBA. Any help would be greatly appreciated.
-Keith

Viewing 13 posts - 1 through 12 (of 12 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hello,

When you say No backups did you mean No SQL Backups or No Windows Backups or both? If the server had Windows backups and SQL Server was stopped during one of Windows backups you may have database files somewhere on the tapes.

Regards,Yelena Varsha

Keith Ward Valued Member Points: 65 More actions · Answer 2

We may have a windows backup...Still waiting to hear back on that. But definitely no database backups.

BigCityCheese Old Hand Points: 393 More actions · Answer 3

Hello,

Have you tried executing DBCC to check for consistency errors ?

Something like this :

In SQL Query Analyzer:

a) place database in single-user mode

exec sp_dboption dbname,'single user',true

b) repair database

DBCC CHECKDB ('dbname',REPAIR_REBUILD)

c) if the results indicate that there is still a consistency error

DBCC CHECKDB ('dbname',REPAIR_ALLOW_DATA_LOSS)

d) remove single-user mode

exec sp_dboption dbname,'single user',false

After clearing error make sure to backup DB (with verify check) in question.

Hope this helps

Keith Ward Valued Member Points: 65 More actions · Answer 4

When a database is in suspect mode you can't run DBCC commands against it. However I have come up with a solution to get the database online and the log file rebuilt. I am currently checking the consistency and I will post my findings back to this thread should everything work.

Thanks for your input.

-Keith

Bikeman Ten Centuries Points: 1207 More actions · Answer 5

Can't let developers run create table in production, much less REBOOT THE SERVER! I would be in there with a big whip and a loudspeaker.

Bikeman Ten Centuries Points: 1207 More actions · Answer 6

Can't let developers run create table in production, much less REBOOT THE SERVER! I would be in there with a big whip and a loudspeaker.

Keith Ward Valued Member Points: 65 More actions · Answer 7

Couldn't agree more!! Just wish I had the political clout to raise some hell and get more policies established. If I was unable to recover the database it would have been a lot easier to have the political push to get something done.

Bikeman Ten Centuries Points: 1207 More actions · Answer 8

Can't let developers run create table in production, much less REBOOT THE SERVER! I would be in there with a big whip and a loudspeaker.

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 9

Your database is victim of TORN_PAGE_DETECTION.

You should restore from backup but as you do not have a backup try using

sp_resetstatus database_name. Remember to reboot the server before trying to access the database.

Do let us know if this help you.

Kindest Regards,

Amit Lohia

Parag Sohoni-154912 SSC Veteran Points: 246 More actions · Answer 10

here is what you can use... replace replace the appropriate values

Microsoft® SQL Server™ 2000 returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:

Execute sp_resetstatus.
Use ALTER DATABASE to add a data file or log file to the database.
Stop and restart SQL Server.
With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.
Free disk space and rerun recovery.

sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.

Caution Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.

Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:

USE masterGOsp_configure 'allow updates', 1GORECONFIGURE WITH OVERRIDEGO

After the procedure is created, immediately disable updates to the system tables:

sp_configure 'allow updates', 0GORECONFIGURE WITH OVERRIDEGO

sp_resetstatus

Luis Mendes SSC-Addicted Points: 402 More actions · Answer 11

That developer stuff is unreal ... You should know better! That's why I like to do all on my own - never had a db problem nor a DBA to set it up for me!! If you are unhappy with your developers try changing - if you cannot - maybe they'll change you!! Eheh ...

Unreal man

nevil SSC Rookie Points: 41 More actions · Answer 12

visist SQLTeam.com and find How to attach DB with corrupt.ldf

fellow there method

thank

Nevil

Sri lanka