October 16, 2012 at 6:55 am
Not sure if applying SP4 is going to do any good. I don't have any answers, however, try running these and see if anything shows up.
DBCC CheckCatalog
go
DBCC CHECKALLOC
go
October 16, 2012 at 7:21 am
Thank you Markus, I'll try that. I'm also going to try deleting the the plan step and recreate it. I'll report back with the results.
October 16, 2012 at 7:24 am
One thing to watch for with Maintenance Plans is when you have a particular plan being utilized across many databases and you add or drop one of those databases, the plan stops working.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
October 16, 2012 at 7:25 am
Scott D. Jacobson (10/16/2012)
Thank you Markus, I'll try that. I'm also going to try deleting the the plan step and recreate it. I'll report back with the results.
You know.. .the more I think about it in SQL2005 and later those commands I posted for you to run are within the DBCC CHECKDB utility... they are not in the SQL2000... so.. I don't think that is going to be of any value to you.
You might have to open a ticket with Microsoft SQL support on that.
October 16, 2012 at 7:41 am
Markus (10/16/2012)
Scott D. Jacobson (10/16/2012)
Thank you Markus, I'll try that. I'm also going to try deleting the the plan step and recreate it. I'll report back with the results.You know.. .the more I think about it in SQL2005 and later those commands I posted for you to run are within the DBCC CHECKDB utility... they are not in the SQL2000... so.. I don't think that is going to be of any value to you.
You might have to open a ticket with Microsoft SQL support on that.
Command came back without any errors anyway. What's weird is DBCC CHECKDB works just fine when run in Mgmt. Studio. It has succeeded on some days since the 9th (when it started failing) but there doesn't seem to be any rhyme or reason why it succeeds or fails. Nothing else is happening on the database at the same time and we've spaced all the maintenance tasks apart so they don't trip over one another.
I just recreated the plan step so lets see what happens with that. Sorry for posting early but it takes about 10 minutes to run this each time, even when it fails. I'll be back soon with more news.
October 16, 2012 at 8:03 am
Just recreated the step and confirmed it works by running the SQL Agent job. A couple of things to note:
1) This plan is only being executed on a single database. There are only 8 user DBs on the server and not all of them have maintenance plans. This particular plan is only executing against 1 user database.
2) I recreated the step by deleting the original step and adding a new one. Instead of using the built in "Check Database Integrity" plan task, I created an Execute SQL task and out the following in it: DBCC CheckDB ('MyDatabase') WITH No_INFOMSGS, ALL_ERRORMSGS. Obviously, replace MyDatabase with your actual database name. Assigned this task the same schedule as the one I deleted.
3) Ran the SQL Agent job. Execution was successful and the duration was about the same as before when the task was succeeding.
I'm pretty stumped on this one but since I found a workaround I'm not that concerned. Don't think it's worth calling MS on it either since the solution works. If anyone has anything else to add I'm all ears.
October 16, 2012 at 10:40 am
I have had some odd things happen related to the creation and execution of maintenance plans. In general, I try to avoid creating and using them unless they perform file system maintenance operations like deleting old database backup files.
My suggestion is that in the future, you use T-SQL to script the maintenance you want to have happen and paste it into a SQL Agent job. Obvious choices for this would be:
1. DBCC CHECKDB
2. Database backups
3. Index maintenance operations
4. Updating of index statistics
I think you'll find that you have fewer mysterious problems.
October 16, 2012 at 9:34 pm
If you are running on SQL Server 2005 please refer to http://support.microsoft.com/kb/949199
If not, this sometimes happens when more than one disk intensive maintenance tasks are running at the same time. Please review the jobs that are running when the errors arise and make sure that dbcc jobs do not coincide with something like a reindexing task.
October 17, 2012 at 7:04 am
Thank you Siva. I had seen that KB before I posted but didn't think it applied for 2 reasons. One, I do not have Snapshot Isolation enabled on this DB. Secondly, the hot fix was supposed to be included with SP2. Since I'm on SP3 I didn't think it applied here.
I think Lee hit the nail on the head. It's better to use TSQL to do the maintenance than using the built-in SSIS-based maintenance tasks.
For anyone who may stumble across this later, try recreating the problem step first. If that works, great! If not, try recreating it using TSQL instead, mine has been working consistently since I converted it to plain TSQL.
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply