In this article, we will review PowerShell SQL Server module DBATools to identify IDENTITY columns about to reach the threshold.
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.
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.
Let us view the issue with an IDENTITY column using an example.
- Create a Demo table 1234567Use SQLSHACKDEMOGOCREATE 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 column1select 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.
CREATE TABLE [dbo].[DemoIdentityInsert_1] (
[id] [int] IDENTITY(2147483644 , 1) PRIMARY KEY NOT NULL
,[Name] [nvarchar](20) NULL
) ON [PRIMARY]
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.
Let us explore these DBATools(Test-DbaIdentityUsage and Get-DbaDbIdentity) functions in this article.
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.
>Get-DbaDbIdentity -SqlInstance Kashish\SQL2019CTP -Database SQLShackDemo -Table DemoIdentityInsert
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.
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.
- Does not contain
- 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.
>Test-DbaIdentityUsage -SqlInstance Kashish\SQL2019CTP -Database sqLShackDemo | Out-GridView
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.
>Test-DbaIdentityUsage -SqlInstance Kashish\SQL2019CTP -Database sqLShackDemo -Threshold 20 | Out-GridView
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.
In the following command, you can notice both instance names in SqlInstance parameter.
>Test-DbaIdentityUsage -SqlInstance Kashish\SQL2019CTP,Kashish\SQL2019 -Threshold 20 | Out-GridView
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.
>Test-DbaIdentityUsage -SqlInstance Kashish\SQL2019CTP,Kashish\SQL2019 -Threshold 70 | Out-GridView
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
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at email@example.com
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- An overview of native differential backups in AWS RDS SQL Server - December 9, 2019
- An overview of SQL Server data types - December 9, 2019
- Sequence Containers in SSIS packages - December 6, 2019