Eine unheimliche Begegnung der dritten Art hatten Kollegen als sie versuchten eine Datenbank vom SQL-Server zu trennen. Es ging nicht, weil die Datenbank noch in Verwendung sei. Als sie nachschauten, stellten sie fest, dass ein SQL-Server-Task die Datenbank verwendete, der "Ghost-Cleanup Task".

Die besten Informationen fand ich dazu in meinem Lieblingsblog "SQL Server Storage Engine" von Paul Randal:

Ghost records

  • These are records that have been logically deleted but not physically deleted from the leaf level of an index.
  • The reasons for this are complicated, but basically having ghost records simplfies key-range locking and transaction rollback.
  • The record is marked with a bit that indicates it's a ghost record and cannot be physically deleted until the transaction that caused it to be ghosted commits. Once this is done, it is deleted by an asynchronous background proces (called the ghost-cleanup task) or it is converted back to a real record by an insert of a record with the exact same set of keys.
  • Ghost records will be mentioned later in the series when I discuss page compaction.

Abhilfe brachte die Datenbank zunächst in den Single-User-Mode zu schalten und dann zu trennen.