SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Maintenance Plan - Check Database Integrity Tool vs. Simple T-SQL Script


Maintenance Plan - Check Database Integrity Tool vs. Simple T-SQL Script

Author
Message
DoubleEx
DoubleEx
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 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
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9910 Visits: 13350
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
SKYBVI
SKYBVI
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1569 Visits: 3239
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
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10392 Visits: 13687
pls post the error.

was it related to ansi settings by any chance?

---------------------------------------------------------------------
DoubleEx
DoubleEx
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 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.
SKYBVI
SKYBVI
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1569 Visits: 3239
what does your log files say located on server:-

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

Regards,
Sushant
DoubleEx
DoubleEx
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 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!!
elvismeggi
elvismeggi
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 62
I found today this post , and it works for me, thank you !! :-P
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7769 Visits: 9971
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 opportunities brilliantly disguised as insurmountable obstacles.

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search