Here is a Windows Powershell function I wrote to call a generic stored procedure that will rebuild all indexes in a database. (I found the stored procedure here: http://www.wisesoft.co.uk/scripts/t-sql_defrag_indexes_for_database.aspx) This turns out to be extremely useful for rebuilding indexes on a SQL Server-based connected directory at the end of each run of a Microsoft Forefront Identity Manager (FIM) MA that has undergone a lot of changes.
PowerShell function:
Function Run-RebuildIndexes
{
param([string]$DBName,[string]$SQLServerName=“localhost”,[string]$SQLServerPort=“1433”)
$log.debug(“Run-RebuildIndexes for MA=”+$ma.MAName +” DBName=”+$DBName)
$SqlConnection=New-ObjectSystem.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString =“Data Source=”+$SQLServerName+“;Initial Catalog=”+$DBName+“;Integrated Security=True”;
$SqlCommand=New-ObjectSystem.Data.SqlClient.SqlCommand;
$SqlCommand.CommandText =“DECLARE @return_value int EXEC @return_value = [dbo].[USP_REBUILD_ALL_IDX] SELECT ‘Success’ = @return_value”;
$SqlCommand.Connection =$SqlConnection;
$SqlCommand.CommandType = [System.Data.CommandType]‘Text’;
$SqlConnection.Open();
$SqlAdapter=New-ObjectSystem.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand =$SqlCommand;
$DataSet=New-ObjectSystem.Data.DataSet
$nRecs=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close();
$results=New-ObjectSystem.Collections.ArrayList;
if ( $nRecs-gt 0 )
{
$ActiveDataSet=$DataSet.tables[0]
foreach($recin$ActiveDataSet)
{
$success=$rec.Success;
$log.Debug(“Index rebuild result code: “+$rec.Success);
if($success-ne“0”)
{
return$false;
}
else
{
return$true;
}
}
}
else
{
return$false;
}
}
$SqlConnection=New-ObjectSystem.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString =“Data Source=”+$SQLServerName+“;Initial Catalog=”+$DBName+“;Integrated Security=True”;
$SqlCommand=New-ObjectSystem.Data.SqlClient.SqlCommand;
$SqlCommand.CommandText =“DECLARE @return_value int EXEC @return_value = [dbo].[USP_REBUILD_ALL_IDX] SELECT ‘Success’ = @return_value”;
$SqlCommand.Connection =$SqlConnection;
$SqlCommand.CommandType = [System.Data.CommandType]‘Text’;
$SqlConnection.Open();
$SqlAdapter=New-ObjectSystem.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand =$SqlCommand;
$DataSet=New-ObjectSystem.Data.DataSet
$nRecs=$SqlAdapter.Fill($DataSet)
$SqlConnection.Close();
$results=New-ObjectSystem.Collections.ArrayList;
if ( $nRecs-gt 0 )
{
$ActiveDataSet=$DataSet.tables[0]
foreach($recin$ActiveDataSet)
{
$success=$rec.Success;
$log.Debug(“Index rebuild result code: “+$rec.Success);
if($success-ne“0”)
{
return$false;
}
else
{
return$true;
}
}
}
else
{
return$false;
}
}
T-SQL stored procedure:
GO
USE [DBName]
GO
/****** Object: StoredProcedure [dbo].[USP_REBUILD_ALL_IDX] Script Date: 01/05/2012 14:14:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Nicholas Roy>
— Create date: <Jan 5, 2012>
— Description: <Rebuild all indexes in database>
— =============================================
CREATE PROCEDURE [dbo].[USP_REBUILD_ALL_IDX]
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =(
SELECT N’ALTER INDEX ALL ON ‘ + QUOTENAME(s.name) + ‘.’ + QUOTENAME(t.name) + N’ REBUILD
‘
FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id
FOR XML PATH(”),TYPE).value(‘.’,‘NVARCHAR(MAX)’)
exec sp_executesql@SQL
END
GO
USE [DBName]
GO
/****** Object: StoredProcedure [dbo].[USP_REBUILD_ALL_IDX] Script Date: 01/05/2012 14:14:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: <Nicholas Roy>
— Create date: <Jan 5, 2012>
— Description: <Rebuild all indexes in database>
— =============================================
CREATE PROCEDURE [dbo].[USP_REBUILD_ALL_IDX]
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL =(
SELECT N’ALTER INDEX ALL ON ‘ + QUOTENAME(s.name) + ‘.’ + QUOTENAME(t.name) + N’ REBUILD
‘
FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id
FOR XML PATH(”),TYPE).value(‘.’,‘NVARCHAR(MAX)’)
exec sp_executesql@SQL
END
GO