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

Unable to deallocate kept page - DBCC CHECKDB Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 8:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 356, Visits: 925
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?
Post #1372765
Posted Tuesday, October 16, 2012 6:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 1,324, Visits: 2,941
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



Post #1373213
Posted Tuesday, October 16, 2012 7:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 356, Visits: 925
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.
Post #1373236
Posted Tuesday, October 16, 2012 7:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 7:00 AM
Points: 1,868, Visits: 1,339
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.
Post #1373241
Posted Tuesday, October 16, 2012 7:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 1,324, Visits: 2,941
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.



Post #1373245
Posted Tuesday, October 16, 2012 7:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 356, Visits: 925
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.
Post #1373261
Posted Tuesday, October 16, 2012 8:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 356, Visits: 925
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.
Post #1373284
Posted Tuesday, October 16, 2012 10:40 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:15 PM
Points: 258, Visits: 1,103
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.
Post #1373397
Posted Tuesday, October 16, 2012 9:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 4, 2014 6:25 PM
Points: 9, Visits: 38
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.

Post #1373611
Posted Wednesday, October 17, 2012 7:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 356, Visits: 925
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.
Post #1373772
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse