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:
select name,snapshot_isolation_state,
snapshot_isolation_state_desc
from
sys.databases
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.
Comments