Index rebuild on large database – SQL Agent Powershell job


Index rebuild on a large database could take longer time. One of our customer was looking for a solution to rebuild index one by one  when index  fragmentation is greater than 30%.  Related  newsgroup thread: http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/4faae7c8-3e58-4117-8538-f397e342743f

In this blog post, I have written a SQL Powershell script that identifies indexes on tables that have fragmentation greater than 30% and performs a Index Rebuild one by one. You can also control the number of indexes to process in single script run. By this approach, you can run this SQL powershell script as a recurring scheduled SQL Agent job in non-peak hours.

PowerShell code:

You can download powershell script from rebuildIndexes.ps1

SQL agent job from Indexrebuild_agentjob.sql

About these ads

Developer @ SQL Server Team, Microsoft

Posted in DB Management, Powershell, SQL Agent, SQL Power shell, SQL Server
6 comments on “Index rebuild on large database – SQL Agent Powershell job
  1. […] I have written a SQL Powershell script that identifies indexes on tables that have fragmentation greater than 30% and performs a Index Rebuild one by one. You can also control the number of indexes to process in single script run. You can run this SQL powershell script as a recurring scheduled SQL Agent job .  More details at: http://sethusrinivasan.wordpress.com/2012/02/14/index-rebuild-on-large-database-sql-agent-powershell…  […]

  2. Arun Kumar Allu says:

    Hi Sethu,
    You script looks great. I am a .NET developer, know about powershell but haven’t worked with it a lot.
    Just a couple of clarifications needed.
    You are using an Index Object and calling Rebuild all indexes on it. “$index.RebuildAllIndexes()”
    Is it going to rebuild all indexes of the table or that specific index.
    Can you please point me to the URL where all these are documented.

    Thanks Again.
    Arun Kumar Allu

  3. JohnS says:

    Your script looks great. Do you know if there is a way to do an online index rebuild using powershell/smo? I want to do the equivalent of setting ‘online=on’ in an alter index rebuild.

    • # Online Index operations using SMO, Run this script in SQL Powershell
      #script assumes that your machine name is sqldemo and mdw database exists in default instance
      CD SQLSERVER:\SQL\sqldemo\DEFAULT\Databases\mdw\Tables\snapshots.disk_usage\indexes\NonClusteredIndex-20120326-132208
      $idx = (get-item .)
      $idx.OnlineIndexOperation = $true
      $idx.Rebuild()

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: