question

Mark avatar image
Mark asked

Ghosts in the Machine

Today's SSC QOTD (SQL Server Central Question of the Day) was about Ghost records. A deleted record is not necessarily deleted (reminding me of FoxPro by the way). So, how does the SQL engine mark records for deletion?
delete
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
It isn't any Black Magic... the Row Header on the data page has a bit to indicate that the row is **marked** for deletion. A background process cleans up these deleted rows as resources permit. If a rollback happens the row isn't "restored", the bit is just flipped again and - voila - the row is undeleted .
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image Mark commented ·
That's right, it's only magic when you don't understand it. Thank's for the explanation Blackhawk.
2 Likes 2 ·
Oleg avatar image
Oleg answered
Looks like you are venturing into Paul Randal territory. He has a couple of very good posts: - [Fragmentation (part 1): What are records?][1] where all of them are explained, not just the ghost records - [Inside the Storage Engine: Ghost cleanup in depth][2], this one is really good. <\!-- **Begin Edit** What do you know, I just went through the posts to today's question on SSC site and it looks like next Tuesday Gail Shaw is going to blog on the subject of how to see deleted data after the transaction to delete it has been committed and the ghost cleanup process has already removed the slots info from the array afterwards. I am sure this is going to be a fascinating read, and I will definitely add it to the collection of life (or at least a job) saving posts. **End Edit** --> [1]: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/23/644607.aspx [2]: http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image Mark commented ·
Thanks Oleg. Yes, I'll be looking forward to Gail's post too, that out to be a good read.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.