Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Maintenance Plan - Check Database Integrity Tool vs. Simple T-SQL Script Expand / Collapse
Author
Message
Posted Friday, September 2, 2011 5:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 16, 2014 4:23 AM
Points: 53, Visits: 237
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
Post #1169153
Posted Friday, September 2, 2011 5:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:28 PM
Points: 4,421, Visits: 10,739
Can you post the error message? Maybe it's not so cryptic after all.
You can always use an integrity check routine such as the one on Ola Hallengren's site. It's a good one and lots of people use it.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1169157
Posted Friday, September 2, 2011 7:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 6:14 AM
Points: 1,200, Visits: 3,237
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
Post #1169205
Posted Friday, September 2, 2011 7:23 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:38 AM
Points: 5,879, Visits: 13,010
pls post the error.

was it related to ansi settings by any chance?


---------------------------------------------------------------------

Post #1169211
Posted Friday, September 2, 2011 12:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 16, 2014 4:23 AM
Points: 53, Visits: 237
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.

Post #1169461
Posted Friday, September 2, 2011 12:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 6:14 AM
Points: 1,200, Visits: 3,237
what does your log files say located on server:-

default location:-
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

Regards,
Sushant
Post #1169466
Posted Friday, September 2, 2011 1:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 16, 2014 4:23 AM
Points: 53, Visits: 237
The tip for Ola Hallengren's site was worth the posting itself. That's going to be some interestign reading. Thank you very much!!
Post #1169476
Posted Thursday, August 8, 2013 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 4:51 AM
Points: 4, Visits: 59
I found today this post , and it works for me, thank you !!
Post #1482179
Posted Thursday, August 8, 2013 12:23 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1482513
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse