This custom stored procedure is an enhancement of script "Rebuild and Reorganize Indexes in SQL 2005" By Lennart Gerdvall that removes few unused parameters, adds functionality to handle mirrored databases, to record results for further analysis and provides option to email summary or detail information to operators.
Once executed, a stored procedure (SP) " usp_ReorgRebuildIndexes" is created in Master DB. Sample method to execute SP is shown below. Once executed, results are saved in table "ReorgRebuildIndexesJobLog" in Master DB.
Sample execution method is:
EXEC master.dbo.usp_ReorgRebuildIndexes
@databasename ='', --If you specify database name, only that database is worked on - else, all databases on server are selected
@FragCheck = 10.0,
@DensityCheck = 75.0,
@RebuildThreshold = 30.0,
@online = 1,
@runrebuild = 1, --if you just want to get code, set option to 0 - setting to 1 will execute alter index statements
@DBMirrorPerf = 1, --Work with mirror databases - if 0, mirror databases are ignored
@ChangeDBRecovery = 1,
@SendEmail = 1, --if operators are setup and you want results to be emailed then set this to 1 else 0
@SendSummaryOnly = 0, --if you want detailed emails, set this to 0 else 1 will provide summary only
@MaxDaysofLog = 14, --number of days for which results are saved
@MaxErrors = 10 --max number of errors before SP quits
Sample Summary Email:
Following is summary report Reorg\Rebuild Job ran on XYZ
Database: AdventureWorks
Status: Ok
Indexes Reorg\Rebuild: 8
Duration: 2 Second(s)
Sample Detailed Email:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- START OF INDEX DEFRAG FOR DATABASE XYZ AT 2009-02-16 00:05:59
-- No of processes with connections active for the last 15 minutes in DB XYZ is 5
-- Rebuild and/or reorganization ONLINE (users allowed) of indexes in database XYZ will now be executed!
-- Processing STANDARD table XYZ.ABC, CLUSTERED index ALL,
-- partition 1, avg frag in percent 98.4282, avg page space used in percent 63.4742
-- Executed: ALTER INDEX ALL ON [XYZ].[XYZ].[ABC] REBUILD WITH (ONLINE = ON, FILLFACTOR = 90, MAXDOP = 0) ;
-- Results: avg frag in percent 0.445214, avg page space used in percent 89.907
-- Returned execution status for master.dbo.usp_RebuildIndexes after processing XYZ on SQL Server ABC is Index rebuild OK!
-- END OF INDEX DEFRAG FOR DATABASE XYZ AT 2009-02-16 00:06:17
-- Processing time for database XYZ was 18 seconds.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If you find any bugs or have ideas to improve this further, please do share.
Thanks,
Farhan Sabzaali
