Sql Server Materials: SQLDBA-Identity Utilization or identity crisis or identity_column_value

This article about Identity column utilization in sql server. We insert the current identity value in to a table and then we will send alert to specified mail or DL which id value utilization greater than 50percent.

--Table

USE [master]

/****** Object: Table [dbo].[identity_column_value_tracking] Script Date: 6/16/2025 3:51:36 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[identity_column_value_tracking](

[identity_column_value_tracking_id] [int] IDENTITY(1,1) NOT NULL,

[database_name] [sysname] NOT NULL,

[schema_name] [sysname] NOT NULL,

[table_name] [sysname] NOT NULL,

[column_name] [sysname] NOT NULL,

[type_name] [sysname] NOT NULL,

[maximum_identity_value] [bigint] NOT NULL,

[current_identity_value] [bigint] NULL,

[percent_consumed] [decimal](25, 4) NULL,

[actv_indcr] [int] NOT NULL,

[creatn_dt] [datetime] NOT NULL,

[creatn_usrnam] [varchar](100) NOT NULL,

[mdfctn_dt] [datetime] NOT NULL,

[mdfctn_usrnam] [varchar](100) NOT NULL,

[row_version] [timestamp] NOT NULL,

PRIMARY KEY CLUSTERED

(

[identity_column_value_tracking_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY]

-- Procedure to insert the data into above table

CREATE PROCEDURE [dbo].[win_identity_column_value_tracking]

/************************************************************************************

CREATED BY : Subbareddy Kovvuru

CREATED ON :

PURPOSE : To pull the detail about identity column consumed about 50 Percent

Test Code:

EXEC [dbo].[win_identity_column_value_tracking]

*************************************************************************************/

BEGIN

SET NOCOUNT ON;

DECLARE @Table_Name NVARCHAR(MAX);

DECLARE @Schema_Name NVARCHAR(MAX)

DECLARE @Sql_Command NVARCHAR(MAX) = ''

DROP TABLE IF EXISTS #identity_columns

CREATE TABLE #identity_columns

( [database_name] SYSNAME NOT NULL,

[schema_name] SYSNAME NOT NULL,

table_name SYSNAME NOT NULL,

column_name SYSNAME NOT NULL,

[type_name] SYSNAME NOT NULL,

maximum_identity_value BIGINT NOT NULL,

current_identity_value BIGINT NULL,

percent_consumed DECIMAL(25,4) NULL

);

EXEC sp_foreachdb

USE [?]

if ''?'' NOT in (''master'',''model'',''msdb'',''tempdb'')

INSERT INTO #identity_columns

([database_name], [schema_name], table_name, column_name, [type_name], maximum_identity_value, current_identity_value)

SELECT

DB_NAME() AS database_name,

schemas.name AS schema_name,

tables.name AS table_name,

columns.name AS column_name,

types.name AS type_name,

CASE

WHEN types.name = ''TINYINT'' THEN CAST(255 AS BIGINT)

WHEN types.name = ''SMALLINT'' THEN CAST(32767 AS BIGINT)

WHEN types.name = ''INT''THEN CAST(2147483647 AS BIGINT)

WHEN types.name = ''BIGINT'' THEN CAST(9223372036854775807 AS BIGINT)

--WHEN types.name IN (''DECIMAL'', ''NUMERIC'') THEN CAST(REPLICATE(9, ( CAST(columns.precision AS VARCHAR(MAX)) + '' - '' + CAST(columns.scale AS VARCHAR(MAX)) ) ) AS BIGINT)

WHEN types.name IN (''DECIMAL'', ''NUMERIC'') THEN CAST(REPLICATE(9, ( columns.precision - columns.scale )) AS BIGINT)

ELSE -1

END AS maximum_identity_value,

IDENT_CURRENT(''['' + schemas.name + ''].['' + tables.name + '']'') AS current_identity_value

FROM sys.tables

INNER JOIN sys.columns

ON tables.object_id = columns.object_id

INNER JOIN sys.types

ON types.user_type_id = columns.user_type_id

INNER JOIN sys.schemas

ON schemas.schema_id = tables.schema_id

WHERE columns.is_identity = 1;

UPDATE #identity_columns

SET percent_consumed = CAST(CAST(current_identity_value AS DECIMAL(25,4)) / CAST(maximum_identity_value AS DECIMAL(25,4)) AS DECIMAL(25,2)) * 100;

INSERT INTO DBA.dbo.identity_column_value_tracking

([database_name], [schema_name], table_name, column_name, [type_name], maximum_identity_value, current_identity_value,percent_consumed

,actv_indcr,creatn_dt,creatn_usrnam,mdfctn_dt,mdfctn_usrnam)

SELECT

[database_name],

[schema_name],

table_name,

column_name,

[type_name],

maximum_identity_value,

current_identity_value,

percent_consumed,

GETDATE(),

'DBA',

GETDATE(),

'DBA'

FROM #identity_columns

WHERE percent_consumed>50

END

--Job to send alerts every week day once

USE [msdb]

/****** Object: Job [ADM - Identity Column Value Tracking] Script Date: 6/16/2025 3:50:36 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Batch - L]] Script Date: 6/16/2025 3:50:36 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Batch - L]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Batch - L]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'ADM - Identity Column Value Tracking',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'Identity Column Value Tracking',

@category_name=N'[Batch - L]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Insert data into DBA.dbo.identity_column_value_tracking] Script Date: 6/16/2025 3:50:36 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into master.dbo.identity_column_value_tracking',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'EXEC master.[dbo].[win_identity_column_value_tracking]',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Send mail to DBA group] Script Date: 6/16/2025 3:50:36 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send mail to DBA group',

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'USE master

DECLARE @sub VARCHAR(100);

DECLARE @qry VARCHAR(MAX);

DECLARE @msg VARCHAR(250);

DECLARE @body NVARCHAR(MAX)

DECLARE @query NVARCHAR(MAX);

DECLARE @tab char(1) = CHAR(9)

DECLARE @query_attachment_filename NVARCHAR(520);

DECLARE @xml NVARCHAR(MAX)

SELECT @sub = ''Details of Tables with Identity Column >50% Utilization on SQL2-R21\SQL21 - WIN / Focalpoint Server'';

DECLARE @record_count INTEGER

--SELECT @msg = ''Please refer to the attached spread sheet for the report.'';

SET @xml = CAST(( SELECT [database_name] AS ''td''

,'''',[schema_name] AS ''td''

,'''',[table_name] AS ''td''

,'''', [column_name] AS ''td''

,'''', [type_name] AS ''td''

,'''', [maximum_identity_value] AS ''td''

,'''',[current_identity_value] AS ''td''

,'''', CONVERT(decimal(10,2),[percent_consumed]) AS ''td''

FROM DBA.dbo.identity_column_value_tracking

WHERE creatn_dt >= DATEADD(DAY,-6, GETDATE())

ORDER BY percent_consumed DESC

FOR XML PATH(''tr''), ELEMENTS ) AS NVARCHAR(MAX))

SET @record_count = (SELECT COUNT(1) FROM DBA.dbo.identity_column_value_tracking

WHERE creatn_dt >= DATEADD(DAY,-6, GETDATE()))

SET @body =''<html><body><H3>Identity Column Tracking Informatiom</H3>

<tr>

<th> Data Base Name </th> <th> Schema Name </th> <th> Table Name </th> <th> Column Name </th>

<th> Type Name </th> <th> Maximum Identity Value </th> <th> Current Identity Value </th>

<th> Percent Consumed(%) </th>

</tr>''

SET @body = @body + @xml +''</table></body></html>''

SELECT @query_attachment_filename = ''IdentityColumnTracking.csv'';

IF @record_count>0

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@recipients = ''ksubbareddy.sap@gmail.com'',

@body = @body,

@body_format =''HTML'',

@subject = @sub

END

GO',

@database_name=N'master',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every Monday morning 3 AM',

@enabled=1,

@freq_type=8,

@freq_interval=2,

@freq_subday_type=1,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=1,

@active_start_date=20241204,

@active_end_date=99991231,

@active_start_time=30000,

@active_end_time=235959,

@schedule_uid=NULL;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Sql Server Materials

Monday, 16 June 2025

SQLDBA-Identity Utilization or identity crisis or identity_column_value_tracking

No comments:

Post a Comment

SQL_DBA- Ip Address Ping

Report Abuse