Resolving Error: 831, Severity: 20, State: 1 with message “Unable to deallocate a kept page” in SQL 2005 Error log
This is a known error when you run queries on a database that has the SNAPSHOT isolation level enabled in SQL Server 2005.
Error: 831, Severity: 20, State: 1 + SQL Server
Unable to deallocate a kept page.
If some TSQL/Batch/SP experience it, it can throw and error and fail. So follow below steps to resolve it:
1. Find out if snapshot isolation level is enabled on the database from where this error is thrown. Use below query to find if snapshot isolation level is enabled on the database:
where name = <DB NAME>
2. If Snapshot isolation level is enabled and this is a repetitive in nature, use the below link to download and apply the hotfix to fix this bug.
If this is not in repetitive in nature, then it is best not to apply it as per Microsoft recommendation.
Sometime you may find this error even if the Snapshot isolation level is not enabled. This can happen because of some internal components of SQL Server is using Snapshot isolation level. In that case also, the hotfix available at http://support.microsoft.com/kb/949199/en-us needs to be applied if this symptom becomes repetitive.
Please note that typically this hotfix installation does not need a reboot.