Ghost Records, Card Recon and PCI Compliance

As part of a PCI Compliance audit, I recently ran a scan on a database using software call Card Recon.

A little odd thing occurred.

At a point in the past, a row of data in a column, which had been dropped from the database schema, contained a single test credit card number.

However, the Card Recon software showed that the data was still there in the database file (this was a SQL Database) in the form of a SQL Ghost Record. A Ghost Record can appear when running a delete or insert command and when running delete and insert in different queries but related by the same indexed data, you can read all about it over at Ghost "Rows" Buster in  SQL Server on Technet.

It's basically a record somewhere in the database file, but not directly in a database table and is living in a bit of spare fragmented space somewhere and this needs to be cleaned up.


Following this procedure managed to remove the Ghost Record:

1. Convert the database to Simple (only so the transaction logs don't go mental)
2. Back it up
3. Shrink the database
4. Rebuild all the indexes while in simple mode (a reorg helps sort out some fragmentation)
5. ReOrg the indexes (not really required)
6. exec DBCC CLEANTABLE
7. DeFrag the Disk 
8. DeFrag the database file with Contig (this will probably help a bit of performance if a heavily fragmented file)
9. Execute DBCC FORCEGHOSTCLEANUP (this is undocumented so run it at your own risk)
10. exec sp_clean_db_free_space for the database
11. Update all the Statistics in the database
12. Resize the database to its appropriate size (it was shrunk earlier)
13. Convert the database back to Full 
14. Do a full Backup

Side note 1; until I ran DBCC FORCEGHOSTCLEANUP; I executed the other steps several times and it didn't not clean up remove the ghost record, so clearly this is the key and most of the other steps could be run as a maintenance task.

Side note 2: most likely the Contig procedure should be run after resizing the database back to it's appropriate size as this would then keep the file in one piece!

Side note 3: If you want to see all the ghost records:

 SELECT DB_NAME(database_id), object_id, object_name(object_id) as Name, record_count, GHOST_RECORD_COUNT,Version_ghost_record_count,INDEX_TYPE_DESC, ALLOC_UNIT_TYPE_DESC, * 
        FROM sys.dm_db_index_physical_stats (null, NULL, NULL, NULL , 'DETAILED') where 
        index_level=0 and 
        GHOST_RECORD_COUNT = 1