Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MS-SQL Integrity job failing again and again Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 7:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 384, Visits: 1,262
I keep receiving his error every Monday, after my Sunday's Integrity job schedule

"DBCC CHECKDB WITH NO_INFOMSGS  " failed with the following error: "Database 'xxxxxxxxxxx' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.  A severe error occurred on the current command.  The results, if any, should be discarded.". Possible failure reasons: Problems w...  The package execution fa...  The step failed.


1st time the job ran, it went ok. After that, it started failing.

The database is not in suspect state. It is up and running.I actually ran a DBCC myself, not via job, and gave no errors; but that does not mean it is not corrupted, still it can be.

This is the only database on same server and drive that it is failing the Integrity job. And the Server Admin states there is no server or disk problem.

Here's the SQL version:
Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

Yeah, I know, old stuff, but no plans to upgrade that to Denali or SQL2008 soon... :-( ... it is what it is ...

Any ideas?
Post #1353888
Posted Tuesday, September 4, 2012 9:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:36 PM
Points: 7,097, Visits: 12,600
Did you look in the SQL Server error log as the error message suggested? Were there any entries of interest around the time the scheduled integrity check ran?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1353968
Posted Thursday, September 6, 2012 12:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
That would seem to be a failure creating the database snapshot that DBCC uses. You need to investigate why the database snapshot is failing.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1355059
Posted Thursday, September 6, 2012 6:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 384, Visits: 1,262
Robert Davis (9/6/2012)
That would seem to be a failure creating the database snapshot that DBCC uses. You need to investigate why the database snapshot is failing.


Hi Robert,

You mean, the DBCC command or T-SQL inside job itself? ... I should say, this is a medium size database, not small ...
Post #1355253
Posted Thursday, September 6, 2012 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:36 PM
Points: 7,097, Visits: 12,600
sql-lover (9/6/2012)
Robert Davis (9/6/2012)
That would seem to be a failure creating the database snapshot that DBCC uses. You need to investigate why the database snapshot is failing.


Hi Robert,

You mean, the DBCC command or T-SQL inside job itself? ... I should say, this is a medium size database, not small ...

Internally CHECKDB creates a snapshot and that is what the integrity checks are run against.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1355257
Posted Thursday, September 6, 2012 9:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
The error message indicates that. As opc.three said, CheckDB creates a database snapshot internally. When it creates a snapshot, it has to run crash recovery on the snapshot to ensure it is consistent. This why you get the message about "recovery" marking the database as suspect. It's actually the snapshot that is being marked as suspect.

Make sure that when you test DBCC manually that you are testing it with the same account as lack of permissions could cause the snapshot to fail.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1355411
Posted Sunday, September 9, 2012 11:34 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 10:51 PM
Points: 112, Visits: 1,209
First check SQL Server error log ?

The error means: You have at least one corrupt page in the database that's being hit during recovery. This means you can't access the database unless you put it in emergency mode (using ALTER DATABASE PIS SET EMERGENCY). You can then poke about an extract data BUT it will be transactionally inconsistent as recovery has not been run. If you don't have any valid backups, or they're too old, the only way to fixup the database is to use emergency mode repair. See Paul blog post at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx



SQL Database Recovery Expert
Post #1356560
Posted Monday, September 10, 2012 8:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
prettsons (9/9/2012)
First check SQL Server error log ?

The error means: You have at least one corrupt page in the database that's being hit during recovery. This means you can't access the database unless you put it in emergency mode (using ALTER DATABASE PIS SET EMERGENCY). You can then poke about an extract data BUT it will be transactionally inconsistent as recovery has not been run. If you don't have any valid backups, or they're too old, the only way to fixup the database is to use emergency mode repair. See Paul blog post at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx



No, it doesn't mean this and telling him to put eh database in emergency mode when it's not suspect is just reckless. Please stop referring to yourself as a recovery expert as you clearly are not.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1356769
Posted Monday, September 10, 2012 9:00 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 10:51 PM
Points: 112, Visits: 1,209
Robert Davis (9/10/2012)
prettsons (9/9/2012)
First check SQL Server error log ?

The error means: You have at least one corrupt page in the database that's being hit during recovery. This means you can't access the database unless you put it in emergency mode (using ALTER DATABASE PIS SET EMERGENCY). You can then poke about an extract data BUT it will be transactionally inconsistent as recovery has not been run. If you don't have any valid backups, or they're too old, the only way to fixup the database is to use emergency mode repair. See Paul blog post at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx



No, it doesn't mean this and telling him to put eh database in emergency mode when it's not suspect is just reckless. Please stop referring to yourself as a recovery expert as you clearly are not.


Dear Robert,

Actually I checked a paul post where the same error mention: ""Database 'xxxxxxxxxxx' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information."

But did not check that "sql-lover" mentioned "The database is not in suspect state". I missed this line. sorry for that. Next time I will carefully suggest any answer!!!


SQL Database Recovery Expert
Post #1357116
Posted Tuesday, September 11, 2012 7:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 384, Visits: 1,262
Well,

I am not planning (and certainly, will not) to change a database that is not in suspect state and fully operational to "emergency mode" just to check.

What I will do(only because it is up and running and does not look is generating logical problems with the query) is restore a fresh backup on a different server and run DBCC command again; I'll do that manually and via Maintenance Plan or job.

I got the feeling is a permissions or job problem and not a logical or physical error on the database. Hopefully, that will be the case.
Post #1357431
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse