Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MS-SQL Integrity job failing again and again


MS-SQL Integrity job failing again and again

Author
Message
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
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?
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8261 Visits: 14368
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
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
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 ...
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8261 Visits: 14368
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
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
prettsons
prettsons
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 1381
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 :-)
Robert Davis
Robert Davis
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
prettsons
prettsons
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 1381
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 :-)
sql-lover
sql-lover
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1930
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search