Solved

sql database suspect

Posted on 2013-01-21
27
460 Views
Last Modified: 2013-01-27
We have a sql database that is showing as suspect. How can we recover?
0
Comment
Question by:tntdatacentre
  • 5
  • 5
  • 5
  • +5
27 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38802348
Did you try restarting the sql server service ?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 38802588
Restart the SQL server. You can do it from MS by right clicking on the server name and then Stop follow by Start. I would prefer that to a Restart.
0
 

Author Comment

by:tntdatacentre
ID: 38803140
This was attempted with no success
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38803302
You don't need, or want, to restart SQL Server in that situation.  That's a very bad idea that could cause lost data or corrupt the db beyond repair.

You need to check the error logs to get more details on what specifically happened to corrupt the db.  Hopefully you haven't restarted SQL so many times that the original log is no longer available.

Can't give you any more specific direction until we know WHY the db is corrupted, because different types of corruption have different fixes (or can't be fixed, depending on the problem).
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 200 total points
ID: 38803305
Try this:

According to BOL , a database in SUSPECT state is : “At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem”
 There are numerous reasons for a SQL Server database to be marked SUSPECT.  A  suitable and tightly monitored database backup strategy is critical.
Some common causes
1. Missing device when SQL Server starts
2. Denial of access to a database resource by the operating system
3. A drive where log files reside goes offline.
4. Anti-virus or 3rd party software blocking access
5.  Killing a DBCC comand : Failure of rollback of big job and renaming db,  e.g  a developer  is performing cleanup of a  database realised that it brings your server on its knees, the developer stops a job which starts rollback and it takes forever
6 . A  hardware/ network issue. Needs investigation and  can be difficult to find.
7. Corruption of database file(s)
 
Actions to take if a database in SUSPECT status:
1)                  Don’t reset status immediately, start a restore or any other change that is difficult to reverse.
2)                  Check Logs for relevant messages. Quite often they are unavailable, so consider some remedial action
3)                  Contact DBA and propose a) a server restart b) change to emergency mode where copies can be taken
4)                  If a suitable BACKUP is available enact a RESTORE procedure.
5)                  Test RESTORE procedure regularly to ensure integrity


You can also try this:

http://www.mytechmantra.com/LearnSQLServer/Repair_Suspect_Database_P1.html
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 200 total points
ID: 38803339
what is your sql server version\edition?

for start: check if all your drives are up;
check event viewer related errors
check sql server errors
make sure you have good DB backup

--
after you post your sql version - you will be provided with option that may help you

or for sql 2005+
try
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

--

sql2000

Suspect SQL Server 2000 Database
http://www.mssqltips.com/sqlservertip/1204/suspect-sql-server-2000-database/

USE Master
GO

-- Determine the original database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases 
GO

-- Enable system changes
sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO

-- Update the database status
UPDATE master.dbo.sysdatabases 
SET Status = 24 
WHERE [Name] = 'YourDatabaseName'
GO

-- Disable system changes
sp_configure 'allow updates',0
GO
RECONFIGURE WITH OVERRIDE
GO

-- Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases 
GO 

Open in new window

0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38803363
check if you mdf ldf corrupted DB files are in the locations where they were allocated
in some cases Suspected when ldf is missed \corrupted  (bad drive; transaction interrupted by unexpected reboot, etc)
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38804080
You should follow the advice of ScottPletcher, check the error logs to find out why SQL Server marked the database suspect.

If you post the parts of the log that show this, we can help you figure it out. If you restart SQL, SQL Server will just find the same thing wrong and mark it suspect, but may do more damage than good.

Always find out "why" SQL marked it suspect and go from there, you are much safer that way.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38806243
you may try this statement:
RESTORE DATABASE yourdatabase WITH RECOVERY; 

Open in new window

if that does not help, only a full restore will help
you may want to show the error(s) you get in the error log about this db
0
 

Author Comment

by:tntdatacentre
ID: 38806616
Hi Everyone,

Thank you for all the submissions.

I tried a number of he suggestions was unable to get any information regarding the cause or to resolve the 'suspect' flag.

Hence, we have resorted to restoring the database using BrightStor ARCServe 11.5 but we got this error message after a 2 hour period:
Failed to end restore. (DBNAME=restorebgdbms10 , EC ILLUMINA-AooD6F:[SQL Server] szSqlState = S1000 ,fNativeError = 823, errMSG = [Microsoft][ODBC SQL Servr Driver][SQL Server] I/O error (bad page ID) detected during read at offset 0x00000000012000 in file 'D:\SQL\bgdbms_20130122\\PrimaryGroup_01.mdf.')

Can anyone assist?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38806727
This would mean that you have corruption in your backup. You can use this option to help you get it restored but you will need to fix your database afterwards.
http://msdn.microsoft.com/en-us/library/ms190952(v=SQL.105).aspx
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 200 total points
ID: 38806761
I think that is rather an error in the target database as it points to the mdf file. Try to restore that backup on a different database or server see if it works.

It is possible that the suspect mode was caused exactly by bad sectors on that hard drive. One idea would be to isolate them before you do the restore.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38806796
I was going off the phrase "bad page id". If there is corruption in the database and it is backed up, that corruption can exhibit itself just like this. But I suppose it could be a disk problem too.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 42

Expert Comment

by:EugeneZ
ID: 38807813
start looking for a good backup file..  and you did not post your sql server version,...
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38807829
I would still try the CONTINUE_AFTER_ERROR on the restore to see if you can determine if the bad page id is in an index that can be dropped and recreated.  This would prevent you from some data loss.  It could be that there is corruption and it may be table based, but it is still worth a shot.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38807851
:) it can be old sql server  version
0
 

Author Comment

by:tntdatacentre
ID: 38807861
sql version 2003

Please let me know how i can set the restore option continue_after_error? Reminder that i'm using arcserve 11.5
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38807883
If you can do a SELECT @@version and post that, it will help us more.

As far as the CONTINUE_AFTER_ERROR, I am not sure how you would do that in Arcserve.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38808252
sql version 2003
You may want to double check that.  It is either 2000, 2005, 2008, 2008-R2 or 2012.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 38809766
Have you tried to restore the backup on different server?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38811001
Sql Server 2003 may refer to the SQL server coming with SBS server 2003
0
 

Author Comment

by:tntdatacentre
ID: 38811114
correction after double check - sql version 2000

The server with the current sql database is installed on a drive with compression turned on. If i uncheck compression for that drive what are the implications for the existing database? Will it affect it? Do i need to have sql services turned off while the decompression is in progress?
0
 

Author Comment

by:tntdatacentre
ID: 38811137
As a general question can i have live transactions occuring on the sl database during a decompression?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38811139
Ah, good point.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 38811156
>> The server with the current sql database is installed on a drive with compression turned on. <<

Nope, very bad idea; not sure it's supported, but even if it is, it's an awful idea.

SQL databases should never be on compressed drives.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38811169
That is correct.  SQL Server does not support compressed folders.  Let me know if you need documentation to back that up.
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 200 total points
ID: 38812257
for sql 2000 - you have no other solution but get good backup: CONTINUE_AFTER_ERROR is not sql server 2000 option

also you can try  to open MS support case: they may try to help you with you backup file that you used in this case -
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now