Post reply

Ghost Records

  • June 2, 2011 at 12:29 pm

    #1333294

    Very interesting question, thanks also for the link.

  • June 2, 2011 at 7:18 pm

    #1333498

    I don't like this shiny new terminology introduced for SQL 2008.

    Paul R's blog entry looks wrong. It says "The ghost cleanup task can't physically delete the ghost records until after the delete transaction commits" which clearly suggests that a record can be a "ghost" before the transaction deleting it is commited. That's strange! I might imagine it as a reord marked for deletion by a comited transaction but not yet cleaned out, but...

    Anyway, a "ghost record" or a "phantom record" was traditionally one that was created (or modified) in an as yet uncommited transaction. That use of "ghost record" dates back at least to the mid 70s. Why give it a new meaning something over thirty years on?

    Tom

  • June 3, 2011 at 12:28 am

    #1333519

    Thanks for the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 3, 2011 at 3:17 am

    #1333554

    Tom.Thomson (6/2/2011)

    I don't like this shiny new terminology introduced for SQL 2008.

    It's not new in 2008. SQL 2000 had ghost records, my guess SQL 7 too since it was 6.5-7 where the storage engine was substantially rewritten, and then 2000-2005

    Paul R's blog entry looks wrong. It says "The ghost cleanup task can't physically delete the ghost records until after the delete transaction commits" which clearly suggests that a record can be a "ghost" before the transaction deleting it is commited. That's strange! I might imagine it as a reord marked for deletion by a comited transaction but not yet cleaned out, but...

    Paul's correct (and really, when it comes to storage engine that should be a given, since he owned the code and wrote some of the code). The record is considered ghost after the delete statement runs until the ghost cleanup runs. The only influence with transactions is that the ghost cleanup can't run until the transaction commits

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 3, 2011 at 5:04 am

    #1333585

    GilaMonster (6/3/2011)

    Tom.Thomson (6/2/2011)

    I don't like this shiny new terminology introduced for SQL 2008.

    It's not new in 2008. SQL 2000 had ghost records, my guess SQL 7 too since it was 6.5-7 where the storage engine was substantially rewritten, and then 2000-2005

    Yes, you are right, they were called that in SQL Serer 2000. I guess I never noticed (or maybe forgot, but that's not likely).

    Paul R's blog entry looks wrong. It says "The ghost cleanup task can't physically delete the ghost records until after the delete transaction commits" which clearly suggests that a record can be a "ghost" before the transaction deleting it is commited. That's strange! I might imagine it as a reord marked for deletion by a comited transaction but not yet cleaned out, but...

    Paul's correct (and really, when it comes to storage engine that should be a given, since he owned the code and wrote some of the code). The record is considered ghost after the delete statement runs until the ghost cleanup runs. The only influence with transactions is that the ghost cleanup can't run until the transaction commits

    I didn't mean Wrong in the sense that the system didn't do what he said, but wrong in the sense that "ghost record" had a long established different meaning. But now I see it had been used with Paul's meaning in the SQL Server world for quite a long time, so I was wrong to call that use wrong.

    Tom

  • November 2, 2011 at 11:19 am

    #1402298

    Does a full backup include ghost records? When I restore to another location are they still there?

  • November 2, 2011 at 11:23 am

    #1402302

    rbalt (11/2/2011)

    Does a full backup include ghost records? When I restore to another location are they still there?

    If there are ghost records on the pages at time of backup, yes and yes. Backup just copies data to backup. It doesn't crack the pages, it doesn't do any modifications to them

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 2, 2011 at 4:09 pm

    #1402457

    GilaMonster (11/2/2011)

    rbalt (11/2/2011)

    Does a full backup include ghost records? When I restore to another location are they still there?

    If there are ghost records on the pages at time of backup, yes and yes. Backup just copies data to backup. It doesn't crack the pages, it doesn't do any modifications to them

    If you restore with recovery, which rolls everything commited forwards and rolls everything uncommitted backwards, will the answer change to yes and maybe (they will still be there if the background host cleanup task hasn't yet got round to them, they won't be there if it has), or is the async cleanup sufficiently slow that they will always be yes and yes for a noticeable amount of time after the resore is completed? (I know that it used to be "yes and yes for a very long time", but that was a bug.)

    Tom

  • March 9, 2012 at 1:23 pm

    #1457374

    Great question on ghost records that are not as commonly mentioned about.

    Thank you.

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply