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 1, 2011 8:04 PM


SSC-Dedicated

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

Group: Administrators
Last Login: Yesterday @ 12:29 PM
Points: 32,232, Visits: 16,593
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 1, 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, June 13, 2014 3:03 PM
Points: 594, Visits: 655
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 1, 2011 10:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:58 PM
Points: 2,298, Visits: 3,839
I remember reading about this in an article.

M&M
Post #1118504
Posted Thursday, June 2, 2011 12:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 22, 2015 2:13 AM
Points: 378, Visits: 121
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 2, 2011 2:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 20, 2015 9:14 AM
Points: 305, Visits: 240
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 2, 2011 5:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, June 20, 2015 8:57 AM
Points: 43, Visits: 506
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 2, 2011 9:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 10:38 AM
Points: 1,639, Visits: 2,011
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 2, 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 @ 6:29 AM
Points: 42,458, Visits: 39,235
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, 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 2, 2011 9:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 1, 2015 10:38 AM
Points: 1,639, Visits: 2,011
Interesting. What does the ghost cleanup process do then?
Post #1118824
Posted Thursday, June 2, 2011 9:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 18,903, Visits: 17,185
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1118825
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse