September 2, 2011 at 5:31 am
In SQL 2005 we have some maintenance plans that were built with the Maintenance Plan GUI. We have 2 on one server that have the first block bing "Check Database Integrity Task". Both of these plans have run flawlessly for months and months. Out of nowhere two of these plans started failing on 2 nights ago. There error message was as usual (at least for me) - cryptic and unhelpful and didn't indicate that there were integrity issues.
I scripted the task out and ran the script in the query window for both jobs and no problems whatsoever. I deleted the "Check Database Integrity Task" for both and added a "Execute T-SQL Statement Task" and pasted the scripted DBCC commands into them. Now both jobs run fine. I can only assume that the Maintenance Plan Task "Check Database Integrity Task" is doing something more than the DBCC commands and needs something that for some reseason it no longer has.
I've restarted SQL Server and the Agent. Haven't re-booted the computer. Does anybody have any ideas why this might happen?
Thanks for any info
September 2, 2011 at 5:43 am
September 2, 2011 at 7:20 am
yes, I also experienced something like that...
Btw...whats's ur version of sql server
Generally, if i have some simple t sql statements, i would prefer to run them through sql agent jobs in a t sql script rather than maintenance plans as they act different at times...
I heard they are sometimes faulty too.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
September 2, 2011 at 7:23 am
pls post the error.
was it related to ansi settings by any chance?
---------------------------------------------------------------------
September 2, 2011 at 12:49 pm
Sorry - I forgot to post the message. I sanatized the message replacing the DB name with MyDatabase and the Domain as MyDomain and the server as MyServer.
The database is SQL 2005 Version 9.00.4035.00 and is hosted on a SQL cluster.
--------------------------------------------------------------------------
Thanks everybody. I appreciate the help
--------------------------------------------------------------------------
Date 8/31/2011 10:00:00 PM
Log Job History (MyDatabase- DB Maintenance.Full Backup)
Step ID 1
Server MyServer
Job Name MyDatabase- DB Maintenance.Full Backup
Step Name Full Backup
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: MyDomain.COM\sqlclaSQLServerAgent. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:00:00 PM Progress: 2011-08-31 22:00:01.18 Source: {E034007A-D586-460E-9AF0-D6A69D8EBF54} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:00:00 PM Finished: 10:00:01 PM Elapsed: 0.89 seconds. The package execution failed. The step failed.
September 2, 2011 at 12:53 pm
what does your log files say located on server:-
default location:-
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
September 2, 2011 at 1:11 pm
The tip for Ola Hallengren's site was worth the posting itself. That's going to be some interestign reading. Thank you very much!!
August 8, 2013 at 2:13 am
I found today this post , and it works for me, thank you !! 😛
August 8, 2013 at 12:23 pm
DoubleEx (9/2/2011)
Sorry - I forgot to post the message. I sanatized the message replacing the DB name with MyDatabase and the Domain as MyDomain and the server as MyServer.The database is SQL 2005 Version 9.00.4035.00 and is hosted on a SQL cluster.
--------------------------------------------------------------------------
Thanks everybody. I appreciate the help
--------------------------------------------------------------------------
Date 8/31/2011 10:00:00 PM
Log Job History (MyDatabase- DB Maintenance.Full Backup)
Step ID 1
Server MyServer
Job Name MyDatabase- DB Maintenance.Full Backup
Step Name Full Backup
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: MyDomain.COM\sqlclaSQLServerAgent. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:00:00 PM Progress: 2011-08-31 22:00:01.18 Source: {E034007A-D586-460E-9AF0-D6A69D8EBF54} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:00:00 PM Finished: 10:00:01 PM Elapsed: 0.89 seconds. The package execution failed. The step failed.
This is the job history from the agent - not the maintenance plan history. To get the maintenance plan history you need to right click on the maintenance plan in Object Explorer and select View History.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply