Sunday, October 19, 2008

Defragmenting Tables in SQL Server

Over a period of time, because of the inserts, updates and deletes, the data and the index pages can get fragmented. Here's a script that can help to defragment the database:


--Re-indexes the specified database
CREATE PROCEDURE usp_DefragDatabase
-- We don't use sysname because it might not be long enough.
-- sysname is 128 chars, so we use double that.
@dbname nvarchar(256)
AS
BEGIN
-- Quote the database name with brackets
DECLARE @quoteddbname nvarchar(256)
set @quoteddbname = quotename( @dbname )

-- The outer EXEC is so we can do USE, not allowed in stored procs
-- The inner EXEC does the actual reindex on each table in the
-- specified database

EXEC('
USE '+ @quoteddbname +'
DECLARE @sTableName sysname
DECLARE PKMS_Tables CURSOR LOCAL FOR
select table_name from information_schema.tables
where table_type = ''base table'' order by 1
OPEN PKMS_Tables
FETCH NEXT FROM PKMS_Tables INTO @sTableName

WHILE @@FETCH_STATUS = 0
BEGIN
select @sTablename = quotename(@sTablename, ''[]'')
EXEC('' DBCC DBREINDEX ( ''+@sTableName+'') WITH NO_INFOMSGS'')
FETCH NEXT FROM PKMS_Tables INTO @sTableName
END
CLOSE PKMS_Tables')
END
GO

1 comment:

ps3 wireless controller said...

I think now we should dbcc indexdefrag because it is an online operation and users can continue working.