Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

SQL 2005 Rebuild\Reorganize Indexes with Reports

By Farhan Sabzaali,

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

 

Total article views: 7037 | Views in the last 30 days: 6
 
Related Articles
FORUM

Rebuild Index

Rebuild Index

FORUM

Rebuilding indexes

Rebuilding indexes

SCRIPT

Smart Index Rebuild - All Databases

Rebuilds all indexes from all databases over a specified fragmentation threshold.

FORUM

Rebuild Index Task fails

Rebuild index Job failed

FORUM

Index Rebuild

Rebuilding index or re-index

Tags
defrag index    
indexing    
rebuild index    
reorganize index    
 
Contribute

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.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones