For SQL 2000.Scan all indexes for fragmentation
This script creates an error message with a valid error number, and alert associated with the error message.
(everything when it doesn't exists). Then captures all the user indexes in the database and
executes DBCC ShowContig for each one. It calculates the % of fragmentation on leaf level and fires the alert when is fragmented.
Then saves a log file with all the scanned indexes and info for the ones that need to be defrag.
It can be inserted in a job to check on schedule for fragmentation on indexes, or executed on demand.
/*
** Author: Rodrigo Acosta
** Email: acosta_rodrigo@hotmail.com
**
** Script to be included in a Job that manages fragmentation
** in all indexes in the executing database.
*/
Set Nocount On
/*
** *************Error Message Definition********************
*/
/* Declare Variables that wil be used for the error message */
Declare @Error_no Int
Declare @AddMessage Varchar(1500)
/* Calculates the last error number of the message */
Set @Error_no = (Select Top 1 error From master.dbo.sysmessages Order by error Desc)
/*
First checks if the message that will be added exists.
If it doesn't, calculates the available error number
and creates the message.
*/
If Not Exists
(Select description from master.dbo.sysmessages
Where description Like ('%View C:\Frag_Info.log for more info.'))
Begin
/*
If no user define error messages were added (Error < 50001).
Creates the message with the number 50001.
*/
If @Error_no < 50001
Begin
Set @Error_no = 50001
Set @AddMessage = 'EXEC Sp_addmessage @msgnum=' + Convert(VarChar(5), @Error_no) + ',
@severity=19,
@msgtext="%s index %s on table %s is %s percent fragmented. Consider re-building the index. View C:\Frag_Info.log for more info.",
@with_log=True'
EXEC (@AddMessage)
End
Else
/*
If there is an used define error (Error > 50001). Calculates the
first available error number and creates the error message.
*/
Begin
Set @Error_no = @Error_no + 1
Set @AddMessage='EXEC Sp_addmessage @msgnum=' + Convert(VarChar(5), @Error_no) + ',
@severity=19,
@msgtext="%s index %s on table %s is %s percent fragmented. Consider re-building the index. View C:\Frag_Info.log for more info.",
@with_log=True'
EXEC (@AddMessage)
End
End
/*
** *************Alert Definition********************
*/
/*
** If the Alerts Does not exists, it is created to response to the Error message
** created before.
*/
If Not Exists
(Select Name From msdb.dbo.sysalerts Where name = 'Fragmentation above 50% in user index.')
Begin
EXECUTE msdb.dbo.sp_add_alert @name = 'Fragmentation above 50% in user index.',
@message_id = @Error_no,
@Severity = 0,
@Enabled = 1,
@delay_between_responses = 0,
@category_name = '[Uncategorized]'
End
/*
** *************Selecting Sysindexes columns********************
*/
/* Creates a Temp Table to hold the results from DBCC ShowContig */
/* Creates a Temp Table to show the results from DBCC ShowContig */
Create Table #SaveResults
(ObjectName Sysname,
ObjectId int,
IndexName Sysname,
Indexid tinyint,
[Level] int,
Pages Int,
[Rows] Int,
MinimumRecordSize Int,
MaximumRecordSize Int,
AverageRecordSize Int,
ForwardedRecords Int,
Extents Int,
ExtentSwitches Int,
AverageFreeBytes Int,
AveragePageDensity Int,
ScanDensity Int,
BestCount tinyint,
ActualCount TinyInt,
LogFragmentation TinyInt,
ExtentFragmentation Int)
/* Creates a Temp Table to show the modified results from DBCC ShowContig */
Create Table ##ShowResults
(TableName sysname,
IndexName sysname,
IndexType Varchar (12),
[%Frag] TinyInt,
Defrag VarChar (3),
Pages Int,
AvgFreeBytes Int)
/*
Declares a cursor and variables that holds the table and index Type (Clustered or Nonclustered)
and the FillFactor (original) from user tables with indexes.
*/
Declare @TableName sysname
Declare @IndexName sysname
Declare vCursor Cursor For
Select Object_name (i.id) As 'TableName',
i.name As 'IndexName'
From Sysindexes i Inner Join sysobjects o
On i.id=o.id
Where (Indid = 1 Or Indid Between 2 And 250) And xtype = 'U'
Declare @cmdDBCC Varchar (200)
Open vCursor
Fetch Next From vCursor Into @TableName, @IndexName
While @@Fetch_Status = 0
Begin
Set @cmdDBCC = 'DBCC ShowContig (' + @TableName + ', ' + @IndexName +
') With TableResults'
Insert #SaveResults
Exec (@cmdDBCC)
/* Fills the #SaveResults Table with all the results of the execution*/
Fetch Next From vCursor Into @TableName, @IndexName
End
/* Close the cursor because it don't use it anymore */
Close vCursor
Deallocate Vcursor
/* Declare variables to save info from the #SaveResults */
Declare @ObjectName Sysname
Declare @IndexName2 Sysname
Declare @ScanDensity Int
Declare @IndexType Varchar(13)
Declare @Pages Int
Declare @AverageFreeBytes Int
Declare @Rows Int
Declare @Defrag Char(3)
Declare vCursor2 Cursor For
Select ObjectName, IndexName, ScanDensity,
[IndexType]=
Case
When IndexId = 1 Then 'Clustered'
Else 'NonClustered'
End
,
Pages, [Rows], AverageFreeBytes
From #SaveResults
Open vCursor2
Fetch Next From vCursor2 Into @ObjectName, @IndexName2, @ScanDensity, @IndexType,
@Pages, @Rows, @AverageFreeBytes
While @@Fetch_Status = 0
Begin
/* If fragmentation is above 50 % Then fire the error */
If @ScanDensity < 50
Begin
/* Builts the raise error sentence */
Declare @Raise Varchar(150)
Set @Defrag = 'Yes'
Set @Raise = 'Raiserror (' + Convert(Varchar(10),@Error_no) +
', 18, 1,' + '''' + @IndexType + '''' + ',' + '''' + @IndexName2+ '''' + ',' + '''' +
@ObjectName + '''' + ',' + '''' + Convert(Varchar(20),@ScanDensity) + '''' + ')'
EXEC (@Raise)
End
/* If not just alter the variable to indicate no defragmentation is needed*/
Else
Begin
Set @Defrag = 'No'
End
/* Insert the results into the temp table created to show the results */
Insert ##ShowResults
Values
(@ObjectName, @IndexName2, @IndexType,
@ScanDensity, @Defrag, @Pages,
@AverageFreeBytes)
Fetch Next From vCursor2 Into @ObjectName, @IndexName2, @ScanDensity, @IndexType,
@Pages, @Rows, @AverageFreeBytes
End
Close vCursor2
Deallocate vCursor2
Drop Table #SaveResults
/*
** *************Log File generation********************
*/
Declare @cmd Varchar(1000)
Set @cmd=' EXEC master.dbo.xp_cmdShell ' + '''' +
'OSQL -E -q"Set Nocount On Select Substring(@@ServerName,1,20) as [Executed On Server:] Select Substring(db_name(),1,20) ' +
'AS [Executed On Database:] Select Getdate() as [LogFile generated with the ' +
'results of fragmentation in all user Indexes. Date:] Select ' +
'SubString(TableName,1,20) as TableName,IndexType, Substring(IndexName,1,20) ' +
'as IndexName, [%Frag] as [%Defrag] , Defrag As [Need Defrag.?] from ##ShowResults" -oC:\Frag_Info.log' + ''''
EXEC (@cmd)
Drop Table ##ShowResults
