?
Solved

After running repairs, database remains in SUSPECT mode

Posted on 2009-07-14
2
Medium Priority
?
1,669 Views
Last Modified: 2012-05-07
Hi guys!  I maxed the points on this one because I think (I hope I'm wrong) that this is a hard one.
Last Thursday someone decided to shut the SQL server down dirty because it was taking too long to reboot.  As a result the most important database went into suspect mode. Initially, I was not worried because of the backups. To my chagrin, I found out from the backup admin that the backups were failing for weeks and he was working on it!  I'm passed the pissed off point and now I need help from people smarter than myself.  After researching the erros I was pleased to find out that there were no torn pages.  Error 3414 Serverity 21 State 1, Error 3313 Severity 21 State 2, Error 824 Severity 24 State 2.  This is what I have done thus far:
Set the db in emergency mode with this query

use master
alter database sx set emergency
go
use sx

Then I checked for errors with this command

use sx
dbcc checkdb

There were over 500 allocation errors and over 7400 consistency errors so I fixed the erros and allowed for data loss with this command

alter database sx set single_user with rollback immediate
go
dbcc checkdb ('sx', repair_allow_data_loss)
go
alter database sx set multi_user
go
alter database sx set online

After that completed, the database was back online and seemingly happy.  So I followed the Wolfman's advice and ran maintenance plan job to rebuild the indexes. It ran for 34 minutes than stopped without finishing.  The history stated that it was in progress but it was not working so I executed this to rebuild the indexes:

USE [sx]
GO
ALTER INDEX [alp_order] ON [dbo].[alp] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [alp_pri] ON [dbo].[alp] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [PK_categories] ON [dbo].[categories] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [UQ_catname] ON [dbo].[categories] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [PK_cds] ON [dbo].[cds] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [SK_cds_description] ON [dbo].[cds] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [UK_cds_hash] ON [dbo].[cds] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [PK_devices] ON [dbo].[devices] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [PK_devparams] ON [dbo].[devparams] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO
ALTER INDEX [PK_file2cat] ON [dbo].[file2cat] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
GO
USE [sx]
GO

It finished in about 5 seconds sucessfully.  I opened the logs to see if it really completed and the logs said that the db was back in suspect mode!  I refreshed the db and it was marked suspect again.  So I put it in emergency mode, and ran a DBCC  CHECKDB.  That came back with zero errors.  The database was out of Suspect mode but it was marked Emergency, so I ran this T-SQL:

alter database sx set online

After running for 20 hours without finishing, I stopped it and ran this command:

alter database sx set single_user with rollback immediate
go
dbcc checkdb
go
alter database sx set multi_user
go
alter database sx set online
After running for 35 minutes it back with these errors:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'sx'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594053132288 in database 15. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
Msg 926, Level 14, State 1, Line 1
Database 'sx' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Failed to restart the current database. The current database is switched to master.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3414, Level 21, State 2, Line 1
An error occurred during recovery, preventing the database 'sx' (database ID 15) 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.

What can I do?  Thanks in advance!




0
Comment
Question by:hdk001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Assisted Solution

by:60MXG
60MXG earned 600 total points
ID: 24851088
CHECKDB found 0 allocation errors and 0 consistency errors in database 'sx'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 608, Level 16, State 1, Line 1
No catalog entry found for partition ID 72057594053132288 in database 15. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
Msg 926, Level 14, State 1, Line 1
Database 'sx' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Failed to restart the current database. The current database is switched to master.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3414, Level 21, State 2, Line 1
An error occurred during recovery, preventing the database 'sx' (database ID 15) 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.

Check 4 of these Errors indicated by the Server.  Check one of them at a time.
0
 

Accepted Solution

by:
hdk001 earned 0 total points
ID: 24861920
I used a backup to restore the database.  Now I am having a different problem.  The database has been "restoring" for almost 16 hours and I think something is wrong.  Any help will be appreciated.  
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've always wanted to allow a user to have a printer no matter where they login. The steps below will show you how to achieve just that. In this Article I'll show how to deploy printers automatically with group policy and then using security fil…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question