FIX: When you shrink a database that has the SNAPSHOT transaction isolation level enabled in SQL Server 2005, the shrink operation is not completed successfully

View products that this article applies to.

SYMPTOMS

In Microsoft SQL Server 2005, you enable the SNAPSHOT transaction isolation level or the read committed SNAPSHOT transaction isolation level for a database. When you execute DML statements against a table that does not have a clustered index, the database continues growing. The database becomes very large. Also, when you shrink the database, the shrink operation does not complete successfully. If you run the following statements, the values of the ghost_record_count column and of the forwarded_record_count column are very large:

SELECT 
DB_NAME (database_id) AS database_name,
OBJECT_NAME (object_id) AS object_name,
index_type_desc AS index_type,
alloc_unit_type_desc AS alloc_unit_type,
page_count,
record_count,
ghost_record_count,
version_ghost_record_count,
forwarded_record_count
FROM sys.dm_db_index_physical_stats (
DB_ID ('<DatabaseName>'), 
OBJECT_ID ('<TableName>'), 
NULL, 
NULL, 
'DETAILED')

The large values indicate that the Ghost Cleanup process does not work.

Back to the top

CAUSE

When you shrink the database or the Ghost Cleanup process runs, forwarded versioned records in a heap are not handled correctly. Therefore, the Ghost Cleanup process skips these records. Therefore, you cannot shrink the database. Because the Ghost Cleanup process does not work, the database grows continuously. Therefore, you have less disk space.

Back to the top

RESOLUTION

Service pack information

To resolve this problem, obtain the latest service pack for SQL Server 2005. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

913089 (http://support.microsoft.com/kb/913089/ ) How to obtain the latest service pack for SQL Server 2005

Back to the top