Rajendra Gupta
PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage output in Grid view

IDENTITY columns threshold using PowerShell SQL Server DBATools

March 25, 2019 by

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.

Data Type

Max Value

Storage

Tinyint

255

1 Byte

SmallInt

32,767

2 Bytes

Int

231-1 (2,147,483,647)

4 Bytes

BigInt

263-1 (9,223,372,036,854,775,807)

8 bytes

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
  • 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

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

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage

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.

IDENTITY column Artihmetic overflow error

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.

IDENTITY column Artihmetic overflow error

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.

PowerShell SQL Server to check IDENTITY threshold

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.

PowerShell SQL Server to check IDENTITY threshold - 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.

PowerShell SQL Server to check IDENTITY threshold - Get-DbaDbIdentity

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.

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage

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.

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage

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.

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage

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.

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage output in Grid view

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.

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage output in Grid view

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).

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage output in Grid view

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.

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage output in Grid view with threshold

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

PowerShell SQL Server to check IDENTITY threshold - Test-DbaIdentityUsage output in Grid view with threshold

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

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

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 rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
163 Views