November 13, 2009 at 10:58 am
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.
November 13, 2009 at 1:04 pm
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
November 13, 2009 at 1:26 pm
Have you checked SQL Server Error Log? what does it say there?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 13, 2009 at 1:36 pm
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.
November 13, 2009 at 1:41 pm
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
November 13, 2009 at 1:42 pm
Hmm Seems to be strange, have not got an idea what does it alter?
Any way have you tried to rerun that job?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 13, 2009 at 1:44 pm
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
November 13, 2009 at 1:45 pm
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
November 13, 2009 at 2:53 pm
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