I am running the script to de-fragment indexes, but my index is still fragmented. Why??

I don't know about you guys but I have seen this type of situation so many times where no matter how many times you run your script.At the end, You will see that fragmentation has not been reduced. The one situation could be the obvious one that "the table is a HEAP", means there is no clustered index on that table.

The second reason could be your index is very small. In general, Fragmentation on small tables is often not controllable. As the pages of small indexes are stored on mixed extents. As we all knew that mixed extents are shared by up to eight objects so the fragmentation in a small index might not be reduced after re-organizing or rebuilding the indexes.


I have copied the below lines from Brent Ozar Blog which explains the case in better way:

In the case of our index fragmentation numbers, one of the source data fields is Page Count – the number of pages that an object has.  Size matters with fragmentation: small objects with only a handful of pages may appear to have very high fragmentation numbers, but they can’t actually be de-fragmented.  There’s only so much de-fragmentation you can do when a table only has three pages.  I’ve actually been on support escalation calls where customers demand to know why a defrag job doesn’t reduce all types of fragmentation to absolute zero, even for tables with just one page.

Microsoft’s best practices on SQL Server 2000 index defragmentation notes that:

“Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).”

With that in mind, I added a Page Count Group column and calculated it with a formula:

=IF(Table1[[#This Row],[page_count]]>50000,"Large",(IF(Table1[[#This Row],[page_count]]<10000,"Small","Medium")))

Thanks!!

Error "Page could not be moved because it is a work table page Cannot move all contents of file " During DBCC Shrink.

Today morning, I was assigned a task to remove data files from temp db.As On one of my Server having four datafiles in TempDB. We were doing some R&D so we needed to remove three datafiles from TempDB. I thought its the easiest job to do. All we have to do is Shrink and then remove the file.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev3' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
GO

Before this, every time it was working for me.But today when I was doing this, I got a surprising error. The Error was like "

=================================================================
DBCC SHRINKFILE: Page 4:209 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 1"

Cannot move all contents of file "tempdev3" to other places to complete the emptyfile operation.
====================================================================
As I got this error first time so, I searched the same on google and found some useful links. Which says that If there are some persisted tables in Temp DB and reside on the file you are trying to remove, it will not be able to Shrink or Remove. 

So, all we can do is that remove those tables from temp database and shrink it again. But now the problem is how we can see how many tables are there in my temporary database? On technet I got a useful query which will Find out all the tables which are in tempDB;

use tempdb
declare @id int
declare @dt smalldatetime
create table #spt_space_all
(
id int,
name varchar(500),
rows varchar(200) null,
reserved varchar(200) null,
data varchar(200) null,
index_size varchar(200)null,
unused varchar(200) null,
create_date smalldatetime null,
)
declare TMP_ITEMS CURSOR LOCAL FAST_FORWARD for
select id from sysobjects
where xtype='U'
open TMP_ITEMS
fetch next from TMP_ITEMS into @id
declare @pages int
WHILE @@FETCH_STATUS = 0
begin
create table #spt_space
(
id int,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null,
create_date smalldatetime null,
)
set nocount on
if @id is not null
set @dt = (select crdate from sysobjects where id=@id )
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
update #spt_space set create_date=@dt 
end
insert into #spt_space_all
select name = @id,object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB'),create_date
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
drop table #spt_space
FETCH NEXT FROM TMP_ITEMS
INTO @id
end
CLOSE TMP_ITEMS
DEALLOCATE TMP_ITEMS
select 'DROP TABLE '+[name]+'
GO' from #spt_space_all where [name] not like '%#spt_space_all%'
drop table #spt_space_all
GO


Copy the result of query and run the same in Temp database. It will drop all those tables.

Now you can run below DBCC commands. Of-course it will slowdown the performance of other databases. So, Please think about it if you running it on production servers.

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ( 'ALL')
GO

Now Run same query to remove the temp db data file:

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev3' , EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
GO

It worked in my case. If not then repeat the steps you will be get what you want at the end :)

Thanks!!!