How do I Defrag my MS SQL database?

Step 1


Because of the inserts, updates and deletes, the data and the index pages can get fragmented (just as your PC). The follwoing script can help you defrag your database:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
--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

Step2


Once you have created the stored procedure (by executing the script above).
You will need to run 'usp_DefragDatabase' against your database in order to defrag it.

Add Feedback