DBCC SHRINKDATABASE & Unable to deallocate Kept Page.

Question

DBCC SHRINKDATABASE & Unable to deallocate Kept Page.

rajdba

Ten Centuries

Points: 1362
More actions
December 20, 2009 at 6:10 am

#156518

Hi,
In one of my PROD Box I am getting the below error.
Error: 831, Severity: 20, State: 1.
Unable to deallocate a kept page.
At the same time my DBCC SHRINKDATABASE job was running and there was no error raised for SHRINKING job and it was completed successfully.
There is no other error logged during this time.
My BOX is SQL Server 2005 Ent SP2.
As to my intial serach I found, this error can be raised if SNAPSHOT ISOLATION is enabled in the database. http://support.microsoft.com/kb/949199
Which is not true in my BOX.
select name,snapshot_isolation_state,snapshot_isolation_state_desc
from sys.databases
I think one of the KB Article 969142 been recalled by Microsoft which relates it to DBCC CHECKDB
FIX: Error message when you run the DBCC CHECKDB statement on a database in SQL Server 2005: "Unable to deallocate a kept page"
http://support.microsoft.com/kb/969142/
I am not able to find the relation between the Deallocation of Kept Page and DBCC SHRINKDATABASE.
Please advice.
Thanks in advance.
Raj

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Firstly why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

As for the error, please run this and post any results.

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

To add to what Gail has asked, I also ask why you want to shrink your database. If this is because of some unusual activity, don't use SHRINKDATABASE - use SHRINKFILE as a one time operation.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

rajdba Ten Centuries Points: 1362 More actions · Answer 3

Gail,

Thanks for the reply, As the Year-End procedure before making a database READONLY we Shrink it.

There is no error in DBCC CHECKDB output, the first thing I ran after seeing the error "Unable Deallocate a Kept Page"

My Question what we mean by Kept Page, and how DBCC SHRINKDATABASE works in relation with Deallocation of Pages.

Thanks again.

Raj

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

Which build of 2005 are you on?

Have you used any of the following recently in the database: online index operations, MARS, DML triggers? These all use versioning under the covers (the system that underpins snapshot isolation).

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

rajdba Ten Centuries Points: 1362 More actions · Answer 5

Cant Believe my Eyes .. 🙂 Thanks a ton for the Reply / Help.

My Build is 9.00.3186.00.

We were not using online index operations, MARS, DML triggers at the same time DBCC SHRINKDATABASE was running.

Sir, Can you please tell me what is a Kept Page or from where I can get some more info on this.

Thanks in Advance.

Regards,

Raj

Paul Randal One Orange Chip Points: 29544 More actions · Answer 6

Well, you've hit a bug somewhere in the server - very hard to tell where though without having a debugger attached to it.

A 'kept' page is one that has a KP latch on it to stop it being removed from the buffer pool, or deallocated from an object/index. Latches are lightweight locks internal to SQL Server. You can't control them in any way. Shrink is trying to move the contents of a page to a lower page ID and then deallocate the higher page ID - it can't as the page has a KP latch even though shrink can X lock it.

Hmm - are you doing any ALTER INDEX ... REORGANIZE operations at the same time as the shrink, or running CHECKDB at the same time?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

free_mascot One Orange Chip Points: 27169 More actions · Answer 7

Can you check if UPDATE STATISTICS help?

Just guessing...

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

rajdba Ten Centuries Points: 1362 More actions · Answer 8

Thanks a Ton for your extended help!!!

I don’t know how much I understood but I am assuming that

DBCC SHRINKDATABSE, will try to move all the pages from the Higher Page ID to the Lower Page ID, to achieve it will first put a KP Latch (Keep) and after that when it wants to deallocate the page it requires a DT (Destroy) but its not able to and there I got this error. “Unable Deallocate a Kept Page”

But anyway the KP Latch and the DT Latch are not compatible; don’t know how much I got it.

We are not doing any DBCC CHECKDB at that time, and this error doesn’t get repeated for DBCC SHRINKDATABASE every time it run, so we are not able to replicate the same in our test boxes.

Sir, want to know more about DBCC SHRINKDATABSE.

Regards,

Raj

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Look on Paul's blog (link in his sig above). There's some stuff there on shrinkdatabase.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 10

I beg your pardon. It's not UPDATE STATISTICS it's DBCC UPDATEUSAGE. Run UPDATEUSAGE before SHRINKDATABASE OR SHIRNKFILE.

"DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables."

Hence, could be possible to eliminate the errors during SHRINKDATABASE OR SHIRNKFILE.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."