Hi Friends,
This is my 27th blog on SQL Server Trace Flag 661 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.
Whenever we delete any record from leaf level of an index, immediately that record will be deleted logically not physically. Such records are known as Ghost Record in SQL Server. These records will be physically deleted by an internal process known as Ghost Cleanup Task. This internal process wakes up after a periodic interval (may be 5 or 10 seconds, it depends on your SQL Server version).
Now the question is: Can we disable this internal process? Yes, we can do this by using trace flag 661. Be careful to use this trace flag, it will increase the space consumption because it will not free the space occupied by ghost records. Let me show you this practically.
--With trace flag 661
USE [master]
GO
if DB_ID('Trace661') > 0
DROP DATABASE Trace661
GO
CREATE DATABASE Trace661
ON PRIMARY
( NAME = N'Trace661', FILENAME = N'E:\Trace661.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB )
LOG ON
( NAME = N'Trace661_log', FILENAME = N'E:\Trace661_log.ldf' , SIZE = 512KB , MAXSIZE = 2048GB , FILEGROWTH = 512KB)
GO
DBCC TRACEON(661,-1)
go
use Trace661
go
create table xtTrace661
(
id int not null identity(1,1) primary key,
TestData char(1000)
)
go
insert into xtTrace661 values('Welcome to SQLServerGeeks.com')
go 6
--we are going to delete only 3 rows here
delete from xtTrace661 where id%2=0
go
Now I want to check the Page Id allocated to this clustered index of this table.
DBCC TRACEON(3604,-1)
go
DBCC IND('Trace661','xtTrace661',1)
Go
Now it is clear that data page id for this clustered index is 231. Now let’s check the entry for page id 231 on PFS page. First PFS page is always Page id 1 in every database. So let’s check on PFS page for Ghost records.
DBCC PAGE('Trace661',1,1,3)
Go
In the above output it is showing “Has Ghost” for page id 231 i.e. Ghost record\records are there on page id 231. Now you can check those records on page id 231.
DBCC PAGE('Trace661',1,231,3)
Go
From page header you can find out the total number of Ghost records on this page.
We can check the Ghost records on this page by checking the records having Record Type as GHOST_DATA_RECORD.
Finally turn off the trace flags:
DBCC TRACEOFF(3604,-1) go DBCC TRACEOFF(661,-1) Go
Now Ghost Removal Task has been enabled again after turning off the trace flag 661. If you will check above page entries again then you will not find out any Ghost Record.
PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.
HAPPY LEARNING!
Regards:
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook
