SQL SERVER – What Are Ghost Records and How to Clean Them Up?

What are Ghost Records and How to Clean Them Up?

I loved this question when I received it last week in my SQL Server Performance Tuning Practical Workshop.

SQL SERVER - What Are Ghost Records and How to Clean Them Up? ghostrecords-800x200

Well, in simple possible words – Ghost records are those records which are deleted logically from the table but physically still exists on the disk.

For example, if there is a large amount of insert, update or delete happens on your database. SQL Server indeed reflects the changes when you select the data. However, it does not delete the record physically. These records are called Ghost Records.

Here is the script which you can use to identify the ghost record count.

FROM sys.dm_db_index_physical_stats(DB_ID(N'SQLAuthority'), OBJECT_ID(N'TestTable'), NULL, NULL , 'DETAILED')

Here is another script which you can use to clear all the ghost records from your database.

USE master;
EXEC sp_clean_db_free_space @dbname = N'SQLAuthority'

I see lots of people executing above script before they take backup. Honestly, I think it is alright to take backup without executing above SP as SQL Server frequently clears up all the ghost records. However, with that said, if you feel comfortable running above SP before taking a backup, I think it is totally fine too. Just an extra few minutes before the backup would not hurt you if your backup is finished in your maintenance window.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Backup, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Simple Explanation of AT TIME ZONE Feature
Next Post
SQL SERVER – How to Enable Backup Compression On by Default

Related Posts

6 Comments. Leave new

  • I would probably get rid of the ghost before I did a backup. But I also fold my trash neatly before I throw it out. :-) If you ever heard of obsessive compulsive disorder? Decades ago one of my favorite waitresses at a bar I went to on a regular basis would come by my table, unroll the napkin that I put in my glass to indicate I was finished, crumple it, and leave it on top. She knew it would drive me nuts. But eventually she got me over my OCD. :)

  • One doubt sir ,if I create another db and then import export the data with all dependent objects then will these ghost record be ignored ?

  • Dayakar Reddy
    March 15, 2018 8:54 am

    does this query execution satisfy dod 5220.22m standards?

  • Hi Sir,

    I had a scenario, in which deletion of records done on where conditions. the deletion is successful. when I run select statement with same where condition the record count is zero, but without where clause I can see all the record count(Which includes deleted and remaining). My total record count is 18 crore and deleting record count is 17 crore. Please let me know how it is possible to get all record count.


Leave a Reply