sql server trace flag 661

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

traceflag661_1

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

traceflag661_2

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.

traceflag661_3

We can check the Ghost records on this page by checking the records having Record Type as GHOST_DATA_RECORD.

traceflag661_4

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

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *