sql database suspect

We have a sql database that is showing as suspect. How can we recover?
tntdatacentreAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Did you try restarting the sql server service ?
0
 
ZberteocCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tntdatacentreAuthor Commented:
This was attempted with no success
0
 
Scott PletcherSenior DBACommented:
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
 
ZberteocConnect With a Mentor Commented:
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
 
Eugene ZConnect With a Mentor Commented:
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
 
Eugene ZCommented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
tntdatacentreAuthor Commented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
Eugene ZCommented:
start looking for a good backup file..  and you did not post your sql server version,...
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
Eugene ZCommented:
:) it can be old sql server  version
0
 
tntdatacentreAuthor Commented:
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
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
Anthony PerkinsCommented:
sql version 2003
You may want to double check that.  It is either 2000, 2005, 2008, 2008-R2 or 2012.
0
 
ZberteocCommented:
Have you tried to restore the backup on different server?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Sql Server 2003 may refer to the SQL server coming with SBS server 2003
0
 
tntdatacentreAuthor Commented:
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
 
tntdatacentreAuthor Commented:
As a general question can i have live transactions occuring on the sl database during a decompression?
0
 
Anthony PerkinsCommented:
Ah, good point.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
>> 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
 
Anthony PerkinsCommented:
That is correct.  SQL Server does not support compressed folders.  Let me know if you need documentation to back that up.
0
 
Eugene ZConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.