SQL 2005 Rebuild\Reorganize Indexes with Reports

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-- 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!

-- 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.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.