Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » Data Corruption (SS2K8 / SS2K8 R2) » MS-SQL Integrity job failing again and again
Add to briefcase
13 posts, Page 1 of 2
12»»

MS-SQL Integrity job failing again and again Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sql-lover
Posted Tuesday, September 04, 2012 7:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 230, Visits: 733
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
opc.three
Posted Tuesday, September 04, 2012 9:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,722, Visits: 11,765
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1353968
Robert Davis
Posted Thursday, September 06, 2012 12:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 19, 2013 8:09 AM
Points: 1,558, Visits: 1,396
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
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #1355059
sql-lover
Posted Thursday, September 06, 2012 6:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 230, Visits: 733
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
opc.three
Posted Thursday, September 06, 2012 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,722, Visits: 11,765
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1355257
Robert Davis
Posted Thursday, September 06, 2012 9:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 19, 2013 8:09 AM
Points: 1,558, Visits: 1,396
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
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #1355411
prettsons
Posted Sunday, September 09, 2012 11:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 99, Visits: 1,035
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
Robert Davis
Posted Monday, September 10, 2012 8:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 19, 2013 8:09 AM
Points: 1,558, Visits: 1,396
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
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #1356769
prettsons
Posted Monday, September 10, 2012 9:00 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 99, Visits: 1,035
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
sql-lover
Posted Tuesday, September 11, 2012 7:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 230, Visits: 733
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
13 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.