{"id":1054,"date":"2011-06-07T16:30:23","date_gmt":"2011-06-07T14:30:23","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1054"},"modified":"2011-06-06T16:38:01","modified_gmt":"2011-06-06T14:38:01","slug":"on-the-exorcism-of-ghost-records","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2011\/06\/07\/on-the-exorcism-of-ghost-records\/","title":{"rendered":"On the exorcism of Ghost Records"},"content":{"rendered":"

There was a question earlier on one of the SQL forums as to whether or not Ghost Cleanup overwrote the deleted rows when it ran so as to ensure no one could read that data again.<\/p>\n

Now I could just reference Paul Randal<\/a>‘s blog post where he described Ghost Cleanup in depth<\/a> and leave it there, but where would the fun be in that? \"Smile\"<\/p>\n

So, now that everyone’s read Paul’s blog post, time for some fun with undocumented commands to prove (again) what we already know (and what Paul’s already shown).<\/p>\n

Setup code:<\/p>\n

CREATE TABLE TestingCleanup (\r\nID INT IDENTITY PRIMARY KEY,\r\nDescription VARCHAR(20),\r\nFiller CHAR(50) DEFAULT ''\r\n);\r\nGO\r\n\r\nINSERT INTO TestingCleanup (Description)\r\nVALUES ('One'), ('Two'), ('Three'), ('Four'), ('Five'), ('Six'), ('Seven'), ('Eight'), ('Nine'), ('Ten')\r\n\r\n-- Find the page number that the table is on\r\n\r\nSELECT OBJECT_ID('TestingCleanup') -- 1399012065\r\nDBCC IND(11,1399012065,1)\r\n-- The IAM is 1:309 and the data page is 1:308<\/pre>\n
Now I’m going to delete half the table and then go and look at the page. The ghost cleanup can’t run until the transaction commits, so running this within a transaction gives a chance to go and look at how things work.<\/p>\n
BEGIN TRANSACTION\r\nDELETE FROM TestingCleanup WHERE ID%2 = 0 -- delete the even rows\r\nSELECT ID, Description FROM TestingCleanup -- 5 rows\r\nDBCC TRACEON (3604)\r\nDBCC PAGE(11,1,310,1)\r\nDBCC TRACEOFF (3604)\r\nCOMMIT TRANSACTION<\/pre>\n
The delete removes all the rows with even identity values and the select returns only 5 rows, as expected<\/p>\n
Dump type 1 for DBCC Page gives the header and then each row separately in binary. I’m editing out uninteresting parts of the output to keep things manageable.<\/p>\n
<\/p>\n
Page header:\r\n\r\nm_pageId = (1:308)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_headerVersion = 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_type = 1\r\nMetadata: ObjectId = 1399012065\u00a0\u00a0\u00a0\u00a0\u00a0 m_prevPage = (0:0)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 m_nextPage = (0:0)\r\nm_ghostRecCnt = 5\r\nm_tornBits = 0<\/pre>\n
Five ghosted records (m_ghostRecCnt = 5), corresponding to the 5 deleted rows. Now for the rows (I’m only going to show the first two)<\/p>\n
\r\nSlot 0, Offset 0x60, Length 68, DumpStyle BYTE\r\n\r\nRecord Type = PRIMARY_RECORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0 NULL_BITMAP VARIABLE_COLUMNS\r\nRecord Size = 68\r\nMemory Dump @0x000000000FD4A060\r\n\r\n0000000000000000:\u00a0\u00a0 30003a00 01000000 20202020 20202020 \u20200.:.....\r\n0000000000000010:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n0000000000000020:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n0000000000000030:\u00a0\u00a0 20202020 20202020 20200300 00010044 \u2020\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .....D\r\n0000000000000040:\u00a0\u00a0 004f6e65 \u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.One\r\n\r\nSlot 1, Offset 0xa4, Length 68, DumpStyle BYTE\r\n\r\nRecord Type = GHOST_DATA_RECORD\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0 NULL_BITMAP VARIABLE_COLUMNS\r\nRecord Size = 68\r\nMemory Dump @0x000000000FD4A0A4\r\n\r\n0000000000000000:\u00a0\u00a0 3c003a00 02000000 20202020 20202020 \u2020<.:.....\r\n0000000000000010:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n0000000000000020:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n0000000000000030:\u00a0\u00a0 20202020 20202020 20200300 00010044 \u2020\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .....D\r\n0000000000000040:\u00a0\u00a0 0054776f \u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.Two\r\n<\/pre>\n
The first row, with an ID of 1 and Description of ‘One’, is a Primary Record. The second row, with an ID of 2 and Description of ‘Two’ is a Ghost Data Record. Deleted but not removed from the rows on the page. The same goes for the rest of the rows, the ones that were deleted (even values) are Ghost Data Records, the other are Primary Records. (aside, that begs the question, if there are Primary Data Records, are there Secondary or Tertiary? If so, where?)<\/p>\n
Then if we look at the bottom of the DBCC Page output there’s the slot  array (the offset of the rows on the page) there’s only 5 entries in  there, despite there still being 10 displayed in the record section of DBCC Page. That’s because 5 of the aren’t real rows any longer.<\/p>\n
Now let’s exorcise those ghosts.<\/p>\n
DBCC ForceGhostCleanup -- Undocumented. Do not use in production\r\n\r\nDBCC TRACEON (3604)\r\nDBCC PAGE(11,1,310,1)\r\nDBCC TRACEOFF (3604)<\/pre>\n
Now when we look at the header, the ghostRecCnt is 0, the ghosts are gone and DBCC Page only returns the 5 remaining rows thusly<\/p>\n
<\/code>Slot 0, Offset 0x60, Length 68, DumpStyle BYTE\r\n\r\nRecord Type = PRIMARY_RECORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0 NULL_BITMAP VARIABLE_COLUMNS\r\nRecord Size = 68\r\nMemory Dump @0x000000000B8DA060\r\n\r\n0000000000000000:\u00a0\u00a0 30003a00 01000000 20202020 20202020 \u20200.:.....\r\n0000000000000010:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n0000000000000020:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n0000000000000030:\u00a0\u00a0 20202020 20202020 20200300 00010044 \u2020\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .....D\r\n0000000000000040:\u00a0\u00a0 004f6e65 \u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.One\r\n\r\nSlot 1, Offset 0xe8, Length 70, DumpStyle BYTE\r\n\r\nRecord Type = PRIMARY_RECORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Record Attributes =\u00a0 NULL_BITMAP VARIABLE_COLUMNS\r\nRecord Size = 70\r\nMemory Dump @0x000000000B8DA0E8\r\n\r\n0000000000000000:\u00a0\u00a0 30003a00 03000000 20202020 20202020 \u20200.:.....\r\n0000000000000010:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n0000000000000020:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n0000000000000030:\u00a0\u00a0 20202020 20202020 20200300 00010046 \u2020\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .....F\r\n0000000000000040:\u00a0\u00a0 00546872 6565\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020.Three\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/pre>\n
So Two’s gone. Or has is?<\/p>\n
DBCC Page with dump type 2 dumps the entire page out without any interpretation of rows (the header is still interpreted, but there’s nothing new there hence I’m omitting it). So\u2026<\/p>\n
DBCC TRACEON (3604)\r\nDBCC PAGE(11,1,310,2)\r\nDBCC TRACEOFF (3604)<\/pre>\n
<\/code>DATA:\r\nMemory Dump @0x000000000D6EA000\r\n\r\n<snip>\r\n000000000D6EA070:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n000000000D6EA080:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n000000000D6EA090:\u00a0\u00a0 20202020 20202020 20200300 00010044 \u2020\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 .....D\r\n000000000D6EA0A0:\u00a0\u00a0 004f6e65 3c003a00 02000000 20202020 \u2020.One<.:.....\r\n000000000D6EA0B0:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n000000000D6EA0C0:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n000000000D6EA0D0:\u00a0\u00a0 20202020 20202020 20202020 20200300 \u2020\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ..\r\n000000000D6EA0E0:\u00a0\u00a0 00010044 0054776f 30003a00 03000000 \u2020...D.Two0.:.....\r\n000000000D6EA0F0:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n000000000D6EA100:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n000000000D6EA110:\u00a0\u00a0 20202020 20202020 20202020 20202020 \u2020\r\n000000000D6EA120:\u00a0\u00a0 20200300 00010046 00546872 65653c00 \u2020\u00a0 .....F.Three<.\r\n<snip><\/pre>\n
The even-numbered data is still there. The rows are deleted, the page has only 5 rows on it and it requires either a raw binary dump of the page or some work with a hex editor to see them, but the data that was stored in the now deleted rows is still there and will be there until its overwritten by new rows from somewhere.<\/p>\n
Does Ghost Cleanup overwrite old data to prevent people reading it later? No, most certainly not.\u00a0 Now it’s not something any user can do. Reading the data file requires  permissions to either stop SQL, detach the DB or take the DB offline (or  a hex editor that ignores file locks) and, unless a hex editor is  standard on servers, permission to install software (or a hex editor  that doesn’t require installation) and DBCC Page requires sysadmin rights. It’s still something to bear in mind if you’re ever working with confidential or  classified data that has to be irretrievable after being deleted. It’s  far from trivial to ensure that in SQL, especially if working with  variable-length columns (where an update might not overwrite the old  data if a page split\/forwarded record results).<\/p>\n","protected":false},"excerpt":{"rendered":"
There was a question earlier on one of the SQL forums as to whether or not Ghost Cleanup overwrote the deleted rows when it ran so as to ensure no one could read that data again. Now I could just… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[29,15,16],"tags":[],"class_list":["post-1054","post","type-post","status-publish","format-standard","hentry","category-internals","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-h0","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1054","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=1054"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1054\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1054"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1054"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1054"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}