Thursday, 31 March 2011

Ghost cleanup in sql server

Ghost Cleanup is the removal of pages that have been marked for deletion.  When a record is deleted, it is marked with a bit, and is no longer "available".  In other words, it becomes a ghost record.  The Ghost Cleanup process comes along, and physically removes the records, so the space can be reused.  If you want to stop Ghost clean up follow the below process

Trace flag 661: Disable the ghost record removal process
Trace flag 661 disables the ghost record removal process. A ghost record is the result of a delete operation. When you delete a record, the deleted record is kept as a ghost record. Later, the deleted record is purged by the ghost record removal process. When you disable this process, the deleted record is not purged. Therefore, the space that the deleted record consumes is not freed. This behavior affects space consumption and the performance of scan operations.

When you turn on trace flag 661 at startup or in a user session, trace flag 661 always applies across the server and has global scope. If you turn off this trace flag, the ghost record removal process works correctly.

No comments:

Post a Comment