In this article, we will review PowerShell SQL Server module DBATools to identify IDENTITY columns about to reach the threshold.

Overview

In relational database engine, it is best practice to use an IDENTITY column to auto-generate column values. We define an identity column with a seed and step value. Once we insert a row into a SQL table, it automatically increases the value for that column. We can combine identity with a primary key to provide a unique identifier for a row. We can use integers and numeric data type for the identity column. We cannot insert value in the IDENTITY column explicitly.

We can have the following data types for identity column along with the maximum values.

We should use an appropriate data type to have better space utilization in the database. It is a important requirement while designing database tables.

Syntax

We need to define identity in a column with the following parameter.

IDENTITY [(Seed, increment)]

• Seed: Initial value of IDENTITY
• Increment: Next value in an IDENTITY column is the last identity value plus seed. For example, in a table, the last IDENTITY value is 5, and we have defined seed 1, then the next value will be 6

Suppose you are doing an insert for SQL Table. You have defined identity on a column with data type Tinyint. You have reached the maximum limit for the identity column. If you still try to insert a row in that table, the query fails. We cannot insert any data in that situation. We either have to change the data type for that column or delete existing records and reseed IDENTITY.

• Note: In this article, I am using DBATools using Azure Data Studio. You can also run these commands in Windows PowerShell.

Example

Let us view the issue with an IDENTITY column using an example.

• Create a Demo table
  
  1 2 3 4 5 6 7

  Use SQLSHACKDEMO GO CREATE TABLE [dbo].[DemoIdentityInsert] (     [id] [tinyint] IDENTITY(1, 1) PRIMARY KEY NOT NULL     ,[Name] [nvarchar](20) NULL     ) ON [PRIMARY] GO

• We have defined IDENTITY on a column having data type as tinyint. Insert 255 rows in demo table using the following query
  

  Let us verify the current maximum value in the identity column

  1	select max(id) as MaxIdentityValue from  DemoIdentityInsert

In the following screenshot, we can see we have reached to value 255.

We can have a maximum of 255 values in the identity column for tinyint data type. Let us try to insert one more record in this table, and we get following Arithmetic overflow error. It is because it failed to insert value 256 in the identity column.

We can reproduce this error using int data type as follows. In the following query, we have set an initial value for the identity column as 2147483644. It will help us to reproduce the issue with a minimum number of rows.

We can have maximum 2147483644 values in identity column of the integer data type. Let us try to insert few records in this table. We get the similar message – Arithmetic overflow error converting IDENTITY to data type int.

If we are in a production environment, it might cause an outage where no users can insert data in an existing table. We should have a mechanism to identify that we are approaching near to maximum value in the identity column. We can take appropriate actions before it becomes an issue for us. We can write t-SQL code to know about identity values in our database. It requires you to be good in writing t-SQL along with knowledge about internal system tables.

PowerShell SQL Server to check IDENTITY threshold

We can use PowerShell SQL Module DBATools for tracking identity values in the SQL Server database. You can follow my earlier article (see TOC at the bottom) to install it.

Once installed, run the following code to get DBATools functions related to identity keyword.

It gives following function in PowerShell SQL Server Module DBATools.

1. Test-DbaIdentityUsage
2. Get-DbaDbIdentity
3. Set-DbaDBIdentity

Let us explore these DBATools(Test-DbaIdentityUsage and Get-DbaDbIdentity) functions in this article.

Get-DbaDbIdentity

We can get information about current identity value in a table using Get-DbaDbIdentity function.

First, let us get information about this PowerShell SQL Server module function Get-DbaDbIdentity.

In the following example, we want to check IDENTITY value in DemoIdentityInsert table.

It performs DBCC CHECKIDENT command with NORESEED option and returns the last value of identity column. By default, it gives output in the following format. You can use Format-Table –AutoSize or Out-GridView parameters to get output in a user-friendly way.

Get-DbaDbIdentity

We want to check IDENTITY seed usage as per the maximum value supported in a particular data type. We can use DBATools function Test-DbaIdentityUsage to retrieve useful information. It works on SQL Server 2008 and higher versions.

In the following image, we can see the syntax and description of this. We can take help from the URL in the related links section as well.

We have the following parameters in Test-DbaIdentityUsage.

• SqlInstance – We need to specify the instance name here. We can also specify multiple instances in this parameter
• SqlCredential – By default, DBATools command connects using Windows authentication. If we want to use SQL authentication, specify SQL login credentials here
• Databases –We can also filter results for a particular database using this parameter. We cannot see this parameter in syntax because it is a dynamic parameter
• Threshold – We can define a percentage threshold for the IDENTITY column
• Excludesystem – usually, we do not create user objects in system databases. We can ignore checking identity for the system databases

Let us perform IDENTITY checks against all online databases in our SQL instance.

We might have a large number of databases and tables with IDENTITY columns. It returns output for each identity column in SQL instance. In the following screenshot, we can see a sample of it.

We can get a better representation of this information using Out-GridView format. Execute the following code, and it gives results in another window in a user-friendly way.

We need to look at following columns in the Test-DbaIdentityUsage output.

• LastValue: it shows the current maximum value in the IDENTITY column
• Percentused: it gives percentage usage of identity value against its data type maximum supported value
• Seedvalue: It gives an increment value for an IDENTITY column

Let us filter results using grid view filter. Click on Add Criteria and select a database from the drop-down list.

We can have multiple filters comparison filters to apply.

• Equals
• Contains
• Does not contain
• Equals
• Does not equal
• Ends with
• Is empty
• Is not empty

In the following screenshot, we set the filter for database SQLShackDemo.

In earlier demonstration, we used tables DemoIdentityInsert and DemoIdentityInsert_1 to show identity issues. We can see in Test-DbaIdentityUsage output; the identity column is 100 percent used. We get all IDENTITY columns in the specified database.

We can perform these checks regularly and be sure of any identity threshold issues.

Let us filter out the result for a particular database using Test-DbaIdentityUsage command.

Get-DbaDbIdentity with threshold

We do not want output for all identity tables in our database. Suppose we want to set a warning threshold of 20%. If the percentage used for identity columns moves beyond 20% (80% of maximum data type value as possible identity values), we should get those entries in output. We need to specify -Threshold parameter like the following command.

In the following screenshot, we can see records for those tables exceeding a threshold value ( less than 80% possible identity values).

As of now, we performed a test for a single instance having multiple databases. Suppose we want to perform the test against multiple SQL instances. We need to specify multiple instance name separated by a comma.

For example, let us Test-DbaIdentityUsage command for the following instances.

1. Kashish\SQL2019
2. Kashish\SQL2019CTP

In the following command, you can notice both instance names in SqlInstance parameter.

We can see in the output identity columns for all databases in both instances exceeding a threshold value.

Let us rerun the command with a modified threshold of 70%. It shows tables having used identity values greater than 70% in both instances.

Conclusion

We can proactively identify identity max out issues in SQL instances using PowerShell SQL Server module DBATools. You should implement regular checks on this to avoid any last minute surprise. We will cover more such validations in my further articles.

Table of contents

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023