Automation Scripts

Automatically Reseeding Identity Values on SQL Server

By Tracy Boggiano

The Problem

You have tables that have a lot of data inserted into them and deleted that use identity values and run out integers to use.  I have over 3000+ databases where this can occur so we have an alerts setup that checks the tables then checks a table to see if setup to be auto reseeded based on rather of database engineers have indicated it is safe to do so.  If it is that table is auto reseed either to one the maximum negative number for the datatype else we are alerted and we check with our database engineers on how to handle that table.  Keep in mind we are reseeding tables that have been deemed OK to reseed automatically.

The Solution

Step 1 – Setting up an SQL Agent Job to Alert You

The first step is to create a table that will hold the tables they we predefined as OK to reseed automatically for us.  At this stage, this table will be empty but it is needed for the procedure that checks to see if an alert needs to be generated for tables requiring your attention.  Our first step will be to create procedure that will check all our user database to check tables that are 90% near the identity the value.  This is parameter you can customize for the procedure.  The code for the procedure is below and you setup a job to run the procedure or add it your own monitoring tool.  Be sure to setup something that will alert if the job fails so you can take action.

CREATE TABLE dbo.[IdentityReseedWhiteList](
	[DatabaseName] [NVARCHAR](128) NOT NULL,
	[SchemaName] [NVARCHAR](128) NOT NULL,
	[TableName] [NVARCHAR](128) NOT NULL,
	[ReseedValue] [BIGINT] NULL,
	[RowModifiedBy] [NVARCHAR](128) NOT NULL,
	[RowModifiedTimeGMT] [DATETIME] NOT NULL,
 CONSTRAINT [PK_IdentityReseedWhiteList] PRIMARY KEY CLUSTERED 
(
	[DatabaseName] ASC,
	[SchemaName] ASC,
	[TableName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) 
) 
GO

ALTER TABLE dbo.[IdentityReseedWhiteList] ADD  CONSTRAINT [DF_IdentityReseedWhiteList_RowModifiedBy]  DEFAULT (SUSER_SNAME()) FOR [RowModifiedBy]
GO

ALTER TABLE dbo.[IdentityReseedWhiteList] ADD  CONSTRAINT [DF_IdentityReseedWhiteList_RowModifiedTimeGMT]  DEFAULT (GETUTCDATE()) FOR [RowModifiedTimeGMT]
GO
CREATE PROCEDURE dbo.Identity_CheckSeed
(
	@PercentToAlert TINYINT = 90
) 
AS
SET NOCOUNT ON;

DECLARE	@SQL NVARCHAR(MAX),
	@Message NVARCHAR(MAX) = '',
	@NumToReseed INT,
	@DatabaseName NVARCHAR(128),
	@SchemaName NVARCHAR(128),
	@TableName NVARCHAR(128);

CREATE TABLE #IdentityInfo
(
	DatabaseName NVARCHAR(128),
	SchemaName NVARCHAR(128),
	TableName NVARCHAR(128),
	DataType NVARCHAR(128),
	CurrentValue BIGINT
);

CREATE TABLE #ReseedInfo
(
	DatabaseName NVARCHAR(128),
	SchemaName NVARCHAR(128),
	TableName NVARCHAR(128),
	MinValue BIGINT,
	CurrentValue BIGINT
);

--Get all identity tables and the current value
SELECT @SQL = REPLACE(REPLACE(
	'USE {{Database}}
	INSERT INTO #IdentityInfo 
		(DatabaseName, 
		SchemaName, 
		TableName, 
		DataType, 
		CurrentValue) 
	SELECT c.Table_Catalog AS DatabaseName, 
		c.Table_Schema AS SchemaName, 
		c.Table_Name AS TableName, 
		c.Data_type AS DataType,
		IDENT_CURRENT(c.TABLE_SCHEMA + "." + c.Table_Name) AS CurrentValue
	FROM {{Database}}.INFORMATION_SCHEMA.COLUMNS c 
		INNER JOIN {{Database}}.INFORMATION_SCHEMA.TABLES t ON t.Table_Catalog = c.TABLE_CATALOG 
			AND t.TABLE_SCHEMA = c.TABLE_SCHEMA 
			AND t.TABLE_NAME = c.TABLE_NAME
	WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + "." + c.Table_Name), COLUMN_NAME, "IsIdentity") =  1
		AND t.TABLE_TYPE = "BASE TABLE"; '
	,'{{Database}}', name)
	,'"', '''')
FROM sys.databases d
WHERE d.database_id > 4
        AND d.database_id <> 32767
	AND d.name <> N'distribution'
	AND d.name <> N'SSISDB'
	AND d.name <> N'ReportServer'
	AND d.name <> N'ReportServerTempDB'

EXEC (@SQL);

INSERT INTO #ReseedInfo
	( DatabaseName,
		SchemaName,
		TableName,
		MinValue,
		CurrentValue)
SELECT Databasename, 
	SchemaName, 
	TableName,
	MinValue,
	CurrentValue
FROM (
	SELECT DatabaseName, 
		SchemaName, 
		TableName, 
		CurrentValue,
		CASE 
			WHEN DataType = 'TINYINT' THEN 255
			WHEN DataType = 'SMALLINT' THEN 32767
			WHEN DataType = 'INT' THEN 2147483647
			WHEN DataType = 'BIGINT' THEN 9223372036854775807
			ELSE 1
		END AS MaxValue,
		CASE 
			WHEN DataType = 'TINYINT' THEN 0
			WHEN DataType = 'SMALLINT' THEN -32767
			WHEN DataType = 'INT' THEN -2147483647
			WHEN DataType = 'BIGINT' THEN -9223372036854775807
			ELSE 0
		END AS MinValue
	FROM #IdentityInfo 
	) AS I
WHERE (CurrentValue / MaxValue) * 100 > @PercentToAlert;
	
--Reseed tables that have MinValue @PercentFreeToReseed of MaxValue
IF @@ROWCOUNT > 0
BEGIN
	EXEC msdb.dbo.sp_start_job @job_name = 'DBA - Identity Auto Reseed'
END

-- Print message if table not reseeded
SELECT @Message = @Message + r.Databasename + '.' + r.SchemaName + '.' + r.TableName + ' has a identity column close to the max value.' + CHAR(13)
FROM #ReseedInfo AS r
	LEFT OUTER JOIN dbo.IdentityReseedWhiteList AS wl 
		ON wl.DatabaseName = r.DatabaseName
			AND wl.SchemaName = r.SchemaName 
			AND wl.TableName = r.TableName
WHERE wl.SchemaName IS NULL;

DROP TABLE #IdentityInfo;
DROP TABLE #ReseedInfo;

IF LEN(@Message) > 0
BEGIN
	SET @Message = 'Idenity Reseed Needed Action Requried: ' + @Message
	RAISERROR(@Message, 16, 1)
END
GO

STEP 2 – SETUP procedure to AUTOMATICALLY RESEED IDENTITY FIELDS

In the second step, we setup a table to record when auto seeding occurs because being the good DBAs we are will still need to know when our automated process actually run just in case.  Then we setup a job called “DBA – Identity Auto Reseed” to call procedure IdentityReseed_CheckandReseedValue that will reseed anything that we have inserted into our white list table.

CREATE TABLE dbo.[IdentityReseedHistory](
	[RowID] [INT] IDENTITY(1,1) NOT NULL,
	[DatabaseName] [NVARCHAR](128) NOT NULL,
	[SchemaName] [NVARCHAR](128) NOT NULL,
	[TableName] [NVARCHAR](128) NOT NULL,
	[CurrentValue] [BIGINT] NOT NULL,
	[ReseedValue] [BIGINT] NOT NULL,
	[ReseedDateTimeGMT] [DATETIME] NOT NULL,
 CONSTRAINT [PK_DBMaintIdentityReseedHistory] PRIMARY KEY CLUSTERED 
(
	[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) 
) 
GO

ALTER TABLE dbo.[IdentityReseedHistory] ADD  CONSTRAINT [DF_IdentityReseedHistory_ReseedDateTimeGMT]  DEFAULT (GETUTCDATE()) FOR [ReseedDateTimeGMT]
GO

CREATE PROCEDURE dbo.IdentityReseed_CheckandReseedValue
(
	DECLARE @PercentToAlert TINYINT = 90
) 
AS
SET NOCOUNT ON;

DECLARE	@SQL NVARCHAR(MAX);

CREATE TABLE #IdentityInfo
(
	DatabaseName NVARCHAR(128),
	SchemaName NVARCHAR(128),
	TableName NVARCHAR(128),
	DataType NVARCHAR(128),
	CurrentValue BIGINT
);

CREATE TABLE #ReseedInfo
(
	DatabaseName NVARCHAR(128),
	SchemaName NVARCHAR(128),
	TableName NVARCHAR(128),
	MinValue BIGINT,
	CurrentValue BIGINT
);

--Get all identity tables and the current value
SELECT @SQL = REPLACE(REPLACE(
	'USE {{Databse}}
	INSERT INTO #IdentityInfo 
		(DatabaseName, 
		SchemaName, 
		TableName, 
		DataType, 
		CurrentValue) 
	SELECT c.Table_Catalog AS DatabaseName, 
		c.Table_Schema AS SchemaName, 
		c.Table_Name AS TableName, 
		c.Data_type AS DataType,
		IDENT_CURRENT(c.TABLE_SCHEMA + "." + c.Table_Name) AS CurrentValue
	FROM {{Databse}}.INFORMATION_SCHEMA.COLUMNS c 
		INNER JOIN {{Databse}}.INFORMATION_SCHEMA.TABLES t ON t.Table_Catalog = c.TABLE_CATALOG 
			AND t.TABLE_SCHEMA = c.TABLE_SCHEMA 
			AND t.TABLE_NAME = c.TABLE_NAME
	WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + "." + c.Table_Name), COLUMN_NAME, "IsIdentity") =  1
		AND t.TABLE_TYPE = "BASE TABLE"; '
	,'{{Databse}}', name),
	'"', '''')
	FROM sys.databases d
	WHERE d.database_id > 4
		AND d.database_id <> 32767
		AND d.name <> N'distribution'
		AND d.name <> N'SSISDB'
		AND d.name <> N'ReportServer'
		AND d.name <> N'ReportServerTempDB'

EXEC (@SQL);

INSERT INTO #ReseedInfo
	( DatabaseName,
		SchemaName,
		TableName,
		MinValue,
		CurrentValue)
SELECT Databasename, 
	SchemaName, 
	TableName,
	MinValue,
	CurrentValue
FROM (
	SELECT DatabaseName, 
		SchemaName, 
		TableName, 
		CurrentValue,
		CASE 
			WHEN DataType = 'TINYINT' THEN 255
			WHEN DataType = 'SMALLINT' THEN 32767
			WHEN DataType = 'INT' THEN 2147483647
			WHEN DataType = 'BIGINT' THEN 9223372036854775807
			ELSE 1
		END AS MaxValue,
		CASE 
			WHEN DataType = 'TINYINT' THEN 0
			WHEN DataType = 'SMALLINT' THEN -32767
			WHEN DataType = 'INT' THEN -2147483647
			WHEN DataType = 'BIGINT' THEN -9223372036854775807
			ELSE 0
		END AS MinValue
	FROM #IdentityInfo 
	) AS I
WHERE (CurrentValue / MaxValue) * 100 > @PercentToAlert;
	
--Reseed tables that have MinValue @PercentFreeToReseed of MaxValue
IF @@ROWCOUNT > 0
BEGIN
	SELECT @SQL = REPLACE(REPLACE(REPLACE(REPLACE(
		'USE {{DatabaseName}}; DBCC CHECKIDENT ("{{Schema}}.{{Table}}", RESEED , ' +	
			CASE 
				WHEN ReseedValue IS NOT NULL THEN CAST(ReseedValue AS VARCHAR(20)) 
				ELSE CAST(MinValue AS VARCHAR(20)) 
			END + ');
						
		INSERT INTO dbo.IdentityReseedHistory 
			(DatabaseName, 
			SchemaName, 
			TableName,
			CurrentValue,
			ReseedValue)
		VALUES ("{{DatabaseName}}", 
			"{{Schema}}", 
			"{{Table}}",' +
				CAST(r.CurrentValue AS VARCHAR(20)) + ', ' +
				CASE 
					WHEN ReseedValue IS NOT NULL THEN CAST(ReseedValue AS VARCHAR(20)) 
					ELSE CAST(MinValue AS VARCHAR(20)) 
				END + ');'
		,'{{DatabaseName}}', r.DatabaseName)
		,'{{Schema}}', r.SchemaName)
		,'{{Table}}', r.TableName)
		,'"', '''')
	FROM #ReseedInfo AS r
		INNER JOIN dbo.IdentityReseedWhiteList AS wl 
			ON wl.DatabaseName =  r.DatabaseName
			AND wl.SchemaName = r.SchemaName 
			AND wl.TableName = r.TableName

	EXEC (@SQL);
END

DROP TABLE #IdentityInfo;
DROP TABLE #ReseedInfo;
GO

STEP 3 – inserting values into white list table for automatic reseeding

Now that all the pieces are in place all you have to do is when the first job fails consult the developers who know whether it is safe to automatically reseed the table or not and you can insert data into your white list table.

To get the most negative number possible as your starting point you would run the following on your server subbing in your values for DatabaseName, SchemaName, and TableName:

INSERT INTO dbo.[IdentityReseedWhiteList] (DatabaseName, SchemaName, TableName)
VALUES ('MyDatabase', 'dbo', 'MyTable')

To get the identity value to start with run the following subbing in your values:

INSERT INTO dbo.[IdentityReseedWhiteList] (DatabaseName, SchemaName, TableName, ReseedValue)
VALUES ('MyDatabase', 'dbo', 'MyTable', 1)

Summary

You now have two tables, two stored procedures, and two SQL Agent jobs.  When the first job fails you check the messages for which table and database needs a reseed and consult your database engineer to see if can be adding to your white list.  Then use the insert statements in the last part of the post to place those tables in your white list and for now on you don’t have to worry about reseeding you those tables.

Related Posts

Automation PowerShell

Using Azure Automation and Runbooks to Run Azure SQL Database Maintenance Tasks

By Tracy Boggiano
Automation

Script to Install the Tools I Use on My Jumpbox

By Tracy Boggiano

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.