Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Ghost Records Expand / Collapse
Author
Message
Posted Wednesday, June 01, 2011 8:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:29 PM
Points: 32,834, Visits: 14,975
Comments posted to this topic are about the item Ghost Records






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1118482
Posted Wednesday, June 01, 2011 8:05 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 15, 2013 10:35 AM
Points: 594, Visits: 654
First to answer and got it right! Never heard of it but the right answer was perfectly logical.

Great question!


Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Post #1118483
Posted Wednesday, June 01, 2011 10:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:49 PM
Points: 2,263, Visits: 3,758
I remember reading about this in an article.

Mohammed Moinudheen
Post #1118504
Posted Thursday, June 02, 2011 12:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:51 PM
Points: 371, Visits: 111
When rows are marked as deleted after a DML operation and the transaction is committed, the rows become Ghost records.

I got it right :)
Post #1118534
Posted Thursday, June 02, 2011 2:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 1:55 AM
Points: 298, Visits: 236
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.

Not sure why SQL Server adopted this approach

Kwex.
Post #1118560
Posted Thursday, June 02, 2011 5:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 8:01 AM
Points: 42, Visits: 411
I believe it is done for performance reasons. Deleting data can occur faster and rollbacks would be faster. Then a process can later update clear up the ghost records.
Post #1118659
Posted Thursday, June 02, 2011 9:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:13 AM
Points: 1,634, Visits: 1,964
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.
Post #1118808
Posted Thursday, June 02, 2011 9:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1118819
Posted Thursday, June 02, 2011 9:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:13 AM
Points: 1,634, Visits: 1,964
Interesting. What does the ghost cleanup process do then?
Post #1118824
Posted Thursday, June 02, 2011 9:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:05 PM
Points: 20,485, Visits: 14,144
GilaMonster (6/2/2011)
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 CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1118825
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse