# Ignition Historian Index Rebuild Script

**URL:** <https://forum.inductiveautomation.com/t/ignition-historian-index-rebuild-script/9071>\
**Category:** Ignition\
**Created:** [September 18, 2014, 12:36am UTC](https://forum.inductiveautomation.com/t/ignition-historian-index-rebuild-script/9071 "2014-09-18T00:36:04Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![cody.tamaki](https://avatars.discourse-cdn.com/v4/letter/c/b487fb/32.png) [@cody.tamaki](https://forum.inductiveautomation.com/u/cody.tamaki)\
**Post date:** [September 18, 2014, 12:36am UTC](https://forum.inductiveautomation.com/t/ignition-historian-index-rebuild-script/9071/1 "2014-09-18T00:36:04Z")

</div>

I thought I’d share a script that I came up with today. If you’re using MSSQL, you can use it to automate index rebuilds on the currently active Historian partition.

```auto
/*--------------------------------------------------------------------------------------

	Description: Rebuilds indexes for active partition on Ignition Historian.
	Date: September 17, 2014

	Written By: Cody Warren
	Company: Tamaki Control Ltd.

--------------------------------------------------------------------------------------*/
DECLARE @Index VARCHAR(MAX); -- Index Name
DECLARE @Table VARCHAR(MAX); -- Table Name
DECLARE @SQL VARCHAR(MAX); -- Dynamic SQL Query
DECLARE @PartitionCursor CURSOR; -- Cursor for list of indexes

-- Create list of indexes for currently active partition table
SET @PartitionCursor = CURSOR FOR
SELECT
	'[' + s.[name] + '].' + '[' + t.[name] + ']' as [table],
	'[' + i.[name] + ']' as [index]
FROM [sys].[indexes] i

INNER JOIN [sys].[tables] t ON
	i.[object_id] = t.[object_id]
AND
	i.[name] IS NOT NULL

INNER JOIN [sys].[schemas] s ON
	t.[schema_id] = s.[schema_id]

INNER JOIN 
(
	SELECT TOP 1
		[pname]
	FROM [dbo].[sqlth_partitions]
	ORDER BY [start_time] DESC
) p ON
	t.[name] = p.[pname]

OPEN @PartitionCursor;
FETCH NEXT FROM @PartitionCursor INTO @Table, @Index;

SET @SQL = '
			USE [Historian]
			';

-- Loop through indexes in currently used partition and create rebuild SQL statement
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = @SQL + ' 
		ALTER INDEX ' + @index + ' ON ' + @Table + ' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
		'
	FETCH NEXT FROM @PartitionCursor INTO @Table, @Index;
END

--SELECT @SQL; -- Debug Only
EXECUTE(@SQL); -- Execute dynamic SQL
```