Fragmentation problem in databases...........

Question

Post reply

Fragmentation problem in databases...........

espanolanthony

SSCarpal Tunnel

Points: 4204
More actions
September 29, 2009 at 7:03 am

#135955

I use SQL Server 2005. I have a fragmentation problem in some of my databases.

I use the below script to see the fragmentation:-

USE SS_ORG

DECLARE @TableName sysname

DECLARE cur_showfragmentation CURSOR FOR

SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'

OPEN cur_showfragmentation

FETCH NEXT FROM cur_showfragmentation INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT 'Show fragmentation for the ' + @TableName + ' table'

DBCC SHOWCONTIG (@TableName)

FETCH NEXT FROM cur_showfragmentation INTO @TableName

END

CLOSE cur_showfragmentation

DEALLOCATE cur_showfragmentation

-----------------------

or

--------------------------

USE SS_ORG

GO

EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"

GO

-------------------------

and i see fragmentation on some databases.so i use the below script to remove fragmentation

---------------------

CREATE PROC ind_rebuild

AS

DECLARE @TableName sysname

DECLARE cur_reindex CURSOR FOR

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'base table'

OPEN cur_reindex

FETCH NEXT FROM cur_reindex INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Reindexing ' + @TableName + ' table'

DBCC DBREINDEX (@TableName, ' ', 80)

FETCH NEXT FROM cur_reindex INTO @TableName

END

CLOSE cur_reindex

DEALLOCATE cur_reindex

GO

---------------------------- and alo this

USE SS_ORG

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables

WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,' ',90)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

and let me tell you friends that these scripts does not remove fragmentation at all. so does any one have some script that will really remove fragmentation?