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?
more ▼

asked Jun 02, 2011 at 11:02 AM in Default

Mark gravatar image

2.6k 23 25 27

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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 .
more ▼

answered Jun 02, 2011 at 01:28 PM

Blackhawk-17 gravatar image

11.9k 28 31 37

That's right, it's only magic when you don't understand it. Thank's for the explanation Blackhawk.
Jun 02, 2011 at 01:35 PM Mark
(comments are locked)
10|1200 characters needed characters left

Looks like you are venturing into Paul Randal territory. He has a couple of very good posts:

<!-- 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 -->

[2]: http://www.sqlskills.com/blogs/paul/post/inside-the-storage-engine-ghost-cleanup-in-depth.aspx
more ▼

answered Jun 02, 2011 at 11:37 AM

Oleg gravatar image

15.9k 2 4 24

Thanks Oleg. Yes, I'll be looking forward to Gail's post too, that out to be a good read.
Jun 02, 2011 at 01:03 PM Mark
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 02, 2011 at 11:02 AM

Seen: 995 times

Last Updated: Jun 02, 2011 at 11:02 AM