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]
GO
/****** Object: Table [dbo].[identity_column_value_tracking] Script Date: 6/16/2025 3:51:36 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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]
GO
-- Procedure to insert the data into above table
CREATE PROCEDURE [dbo].[win_identity_column_value_tracking]
AS
/************************************************************************************
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,
1,
GETDATE(),
'DBA',
GETDATE(),
'DBA'
FROM #identity_columns
WHERE percent_consumed>50
END
GO
--Job to send alerts every week day once
USE [msdb]
GO
/****** 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
GO
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>
<table border = 1>
<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:
GO
No comments:
Post a Comment