Basit Farooq:

Bug fixed: it now rebuilds indexes, with space in their name.

Originally posted on Basit's SQL Server Tips:

In my previous post here, I’ve discussed how we can detect fragmentation in SQL Server databases indexes using dynamic management view function sys.dm_db_index_physical_stats. In this post, I’m sharing my stored procedure which I’ve created a while ago to rebuild fragmented indexes based on the fragmentation level. This stored procedure is based on sys.dm_db_index_physical_stats and resides in master database. This procedure automatically rebuilds indexes if the fragmentation level is above 30% and reorganises indexes if fragmentation is less than 30%.

Code for the procedure is as follow:

1
USE [master]
GO

SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[sp_RebuildIndex] @FragmentationLevel [int] = 30
,@SortInTempDB [varchar] (8) = 'OFF'
,@Online [varchar] (8) = 'OFF'
,@LOBCompaction [varchar] (8) = 'ON'
,@ScanMode [varchar] (16) = 'DETAILED'
,@MinRowsInTable [int] = 1000
--Stored Procedure: [dbo].[sp_RebuildIndex]
--Author: Basit A. Farooq
--Version #: v1.0.0
--********************
-- Purpose/Comments
--********************
--Stored procedure to rebuild the indexes of database.
--Indexes will be…

View original 1,460 more words

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s