MS-SQL Integrity job failing again and again

  • 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?

  • 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

  • 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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • 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 ...

  • 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

  • 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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • This was removed by the editor as SPAM

  • 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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • This was removed by the editor as SPAM

  • 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.

  • Well,

    I restored the database on a different server and same MS-SOL flavor (or almost) but it's the best I can do, I have no more available servers and space for that.

    It took a while, but it's there and NOT in a suspect state; database is open and available. That's good!

    Now I am running (on that standby server) a DBCC CHECKDB and waiting for results. Crossing fingers! :unsure:

    I still believe is not data corruption, but if DBCC command via console gave errors (hopefully it will not) here's what I am planning to do;

    Run this

    DBCC TRACEON (3604, -1)

    GO

    DBCC PAGE('MyDatabase', 1, 12345, 3)

    GO

    Where 12345, would be the corrupted data page? Hopefully it won't be a Clustered Index. If it's a non clustered Index, drop it and recreate it. Otherwise, go back a few weeks and check backups.

    I do not know if I should re-run the DBCC command via Maintenance Plan if the DBCC command via console gives errors. That may be the issue, and not real data corruption.

  • It took 4hrs to complete ... but no errors ...

    CHECKDB found 0 allocation errors and 0 consistency errors in database xxxxxx

    I guess that next (and last step) is creating a SQL job and invoke DBCC from there?

  • I had a similar problem. The only way, to stop it, was: doing a data migration to a database created directly on the instance with the compatibility level according to the sql server version (because I recovered from MSSQLS 2005 to MSSQLS 2008 R2 and appeared me "suspect" 3 times in one week!!! :crying:), luckily we did not have data loss 😀

    Hope it help you.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply