Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to deallocate kept page - DBCC CHECKDB


Unable to deallocate kept page - DBCC CHECKDB

Author
Message
Scott D. Jacobson
Scott D. Jacobson
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1000
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?
Markus
Markus
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: 1587 Visits: 3680
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



Scott D. Jacobson
Scott D. Jacobson
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1000
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.
Meet George Jetson
Meet George Jetson
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2638 Visits: 1393
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.
Markus
Markus
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: 1587 Visits: 3680
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.



Scott D. Jacobson
Scott D. Jacobson
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1000
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.
Scott D. Jacobson
Scott D. Jacobson
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1000
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.
Gail Wanabee
Gail Wanabee
Old Hand
Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)Old Hand (330 reputation)

Group: General Forum Members
Points: 330 Visits: 1310
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.
Siva Ram
Siva Ram
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
Scott D. Jacobson
Scott D. Jacobson
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1000
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.
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