Check DB Integrity Maintenance Plan Suddenly not working

  • It is weird...Check DB Integrity Task suddenly started failing on one of my servers after one month good run; however, I could run the T-SQL scripts generated from the Task without any problem.

    Any thoughts?:ermm:

    ====Info====

    Select @@version

    gives my environment and build number

    Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    After runing the maintenance plan, it gave me the following Error Message:

    Sql Severity0

    Sql Message ID0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted0

    Message

    Executed as user: sa. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:49:13 PM Progress: 2009-11-13 12:49:14.14 Source: {C16890E0-0B46-45CC-8A7A-924DDC79CA46} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:49:13 PM Finished: 12:49:14 PM Elapsed: 1.141 seconds. The package execution failed. The step failed.

  • View the history for the maintenance plan instead of the SQL Agent job. You will find additional information that might indicate exactly what is causing the failure.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Have you checked SQL Server Error Log? what does it say there?


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Jeff/Bru,

    Thanks for your reply.

    1. Didn't see any suspicious errors in SQL Server log or SQL Agent Error Log.

    2. Did check the maintenance plan history...it says something like " ALTER failed for the Server 'myserver'". Please see the attached screenshot. Not sure why there is ALTER in the integrity check task.

  • What is the version of SQL Server? I recall that there were some issues with SP1 and Integrity checks. Not sure if that applies here.

    Is SQL Server running under a local account - or domain account? If using a domain account, does that account have all of the required permissions on the file system to create the snapshot for the integrity check?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hmm Seems to be strange, have not got an idea what does it alter?

    Any way have you tried to rerun that job?


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Never mind about the version - I reviewed the original post and see that you are running SP3. Verify the account running both SQL Server and the Agent and make sure they have the right permissions.

    SQL Server 2005 creates several groups on the server and grants permissions to those groups. Make sure your service account(s) are in those groups as needed - and then verify that the service account has appropriate access to the file system.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Bru Medishetty (11/13/2009)


    Hmm Seems to be strange, have not got an idea what does it alter?

    Any way have you tried to rerun that job?

    I think the alter is part of the process to create a database snapshot that is used for the integrity check. Not sure though...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • This is really interesting, guys. I feel i have to report back.

    I reran the job just now with my trace turned on(hoping to catch the ALTER) , and it was successful. Yes, it did go through and gave no error! To make sure it really works, I tried three times to check all DBs' integrity and all were successful--And I didn't change a thing! (except set the Memory Min=Max, which I don't know how it could remotely relate to the cure though)

    When I reran the job yesterday and the day before yesterday... it just failed.

    Don't want to reason with it..:unsure:

Viewing 9 posts - 1 through 8 (of 8 total)

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