October 15, 2012 at 8:49 am
Hello all,
Received a call this morning that the database maintenance plan on a server is failing on it's Check Database Integrity task with the above error. From the Maintenance Plan history, it appears this began happening on 10/9/2012 and has been happening consistently with every run of DBCC CHECKDB via the plan. So far, the customer can't recall changing anything around that time.
I have suggested updating to SP4 (currently on SP3, no CUs installed). I am also currently running DBCC CheckDB ('MyDatabase') WITH No_INFOMSGS, ALL_ERRORMSGS. When that completes I'll post the results here.
Just wondering if anyone had any further suggestions of what to try or what might be wrong? I did find some other posts on the topic but none related to this particular set of circumstances. They were on older service packs or the error was produced during some other operation. Any help would be greatly appreciated.
Here is the full error message from the Maintenance Plan history:
Executing the query "DBCC CHECKDB WITH NO_INFOMSGS" failed with the following error: "Unable to deallocate a kept page.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
EDIT: Just as I finished typing that up DBCC CheckDB ('MyDatabase') WITH No_INFOMSGS, ALL_ERRORMSGS finished. The only output in the messages tab was: Command(s) completed successfully.
Any thoughts on this besides installing Service Pack 4?
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply