Nice to know! DB2 behaves quite differently, because it just re-uses the page after records have been marked as deleted. It doesn't need an extra background process (ghost exorciser) to make a physical delete.

As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

cfradenburg (6/2/2011)As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.

cfradenburg (6/2/2011)As to why something goes through and cleans up the ghost records instead of just allowing the space get reused, that way the data doesn't remain on disk so it doesn't accidentally read by something. Or purposefully read by someone for that matter.

Ghost cleanup doesn't overwrite the ghosted rows. A direct read of the data file can see deleted data well after the ghosted rows are removed.

Sounds like a fun experiment...

Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNR