How to Rebuild All Indexes Online for a SQL Server Database - Article on {essentialDevTips.com}

How to Rebuild All Indexes Online for a SQL Server Database

by

This article, discusses about, how you can rebuild all indexes online for a SQL Server database and provides useful T-SQL scripts that can help you perform this task.

The below script makes use of the undocumented SQL Server stored procedure “sp_MSforeachtable” and with the proper syntax, it rebuilds all SQL Server indexes online for all tables in a database, along with keeping the default Fill Factor for each index.

 

Rebuild all indexes online along with keeping up the the default fill factor:

USE [DATABASE_NAME];
GO
EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ?
REBUILD WITH (ONLINE=ON)";
GO

Note: The above script assumes that your current edition of SQL Server supports online index rebuild. In a different case you can modify the script to perform the index rebuild offline.

By modifying the above script, you can have different variations of the rebuild statement (i.e. run the rebuild offline, set the Fill Factor, etc.).

Here are some more examples/variations of the above script.

 

Rebuild all indexes online along with setting up the fill factor value:

USE [SampleDB1];
GO
EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON, FILLFACTOR=[FILL_FACTOR_PERCENTAGE])";
GO

 

Rebuild all indexes offline along with keeping up the default fill factor value:

USE [SampleDB1];
GO
EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)";
GO

 

Rebuild all indexes offline along with setting up the fill factor value:

USE [SampleDB1]; 
GO 
EXEC sp_MSforeachtable @command1="print 'rebuilding indexes in table: ?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF, FILLFACTOR=[FILL_FACTOR_PERCENTAGE])"; 
GO

 

I have written an article on SQLNetHub, on which you can find more info and examples. Check out the article here.

 

Learn more SQL Server Administration tips like this! Get the Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course
(Lifetime Access, Downloadable Resources, Certificate of Completion and more!)

Enroll to the Course

 

Recommended Online Courses:

 

Read Also:

 

Reference: {essentialDevTips.com} (https://www.essentialdevtips.com/)

© essentialDevTips.com

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Post Views: 1,275