How to find current Identity value and check remaining values based on data type
Few days back I came across situation when one of the tablein production was INT with identity defined on it. However as we know the limitof INT is 2147483647 and when maximum value crossed 2147483647 then followingerror started showing up
Msg 8115,Level 16, State 1, Line 1
Arithmeticoverflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
Now I went ahead and changed it to BIGINT (Limit 9223372036854775807)and everything was fine after that.
So I decided to create an alert which will tell me if any ofthe columns is reaching the limit of the assigned data type. So I wrotefollowing SP
This SP has input parameter of threshold at which we wantthe alert to trigger. It will also log the entries in a logging table. Thetable will log following details
| DATABASE_NAME |
| TABLE_NAME |
| COLUMN_NAME |
| DATA_TYPE |
| MAX_VALUE |
| REMAINING_VALUES |
| PERCENTLEFT |
| STATUS |
| SERVER_NAME |
| LOGDATE |
- SP will run on all the databases on a server
- It will first find all tables with IDENTITY column
- Find data type of the column
- Find current maximum value
- Compare the current maximum value with the limit of the data type of that column
- Based on the threshold find out which tables are coming close to the upper limit
- Load the data in a logging table
- Email details of tables which are above the threshold
Here is the code for the SP
USE MYadminDB
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ES_IDENTITY_VALUE_CHECK]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].ES_IDENTITY_VALUE_CHECK
GO
create PROCEDUREES_IDENTITY_VALUE_CHECK @threshold decimal(3,2) = NULL
AS
SET NOCOUNT ON
--EXEC MYadminDB.DBO.ES_IDENTITY_VALUE_CHECK 0.90
Create Table #identityStatus
(
DATABASE_NAME varchar(256)
,TABLE_NAME varchar(256)
,COLUMN_NAME varchar(256)
,DATA_TYPE varchar(256)
,LAST_VALUE bigint
,MAX_VALUE bigint
);
Execute sp_msforeachdb '
Use[?];
INSERTINTO #identityStatus
select''?'' AS [DATABASE_NAME], T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE,IDENT_CURRENT(T.TABLE_NAME) as LAST_VALUE
,Case
WhenC.DATA_TYPE = ''tinyint'' Then 255
WhenC.DATA_TYPE = ''smallint'' Then 32767
WhenC.DATA_TYPE = ''int'' Then 2147483647
WhenC.DATA_TYPE = ''bigint'' Then 9223372036854775807
EndAs [MAX_VALUE]
fromINFORMATION_SCHEMA.COLUMNS C
JOININFORMATION_SCHEMA.TABLES T
onT.TABLE_TYPE = ''BASE TABLE''
andT.TABLE_NAME = C.TABLE_NAME
ANDCOLUMNPROPERTY(object_id(T.TABLE_NAME), C.COLUMN_NAME, N''IsIdentity'') = 1
WHERET.TABLE_NAME NOT LIKE ''%_STG''
'
--DEFAULT THRESHOLD TO 85%
IF @thresholdIS NULL
SET@threshold = 0.90;
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IDENTITY_VALUE_WARNINGS]') AND type in (N'U'))
CREATE TABLE [dbo].[IDENTITY_VALUE_WARNINGS](
[DATABASE_NAME] [varchar](256) NULL,
[TABLE_NAME] [varchar](256) NULL,
[COLUMN_NAME] [varchar](256) NULL,
[DATA_TYPE] [varchar](256) NULL,
[MAX_VALUE] [bigint] NULL,
[REMAINING_VALUES] [bigint] NULL,
[PERCENTLEFT] [real] NULL,
[STATUS] [varchar](30) NOT NULL,
[SERVER_NAME] [nvarchar](128) NULL,
[LOGDATE] [datetime] NOT NULL
) ON [PRIMARY]
ELSE
DELETE FROM IDENTITY_VALUE_WARNINGS
WHERE CONVERT(VARCHAR(12),LOGDATE,112) = CONVERT(VARCHAR(12),GETDATE(),112)
INSERT INTO IDENTITY_VALUE_WARNINGS
SELECTDATABASE_NAME,TABLE_NAME,COLUMN_NAME,DATA_TYPE,MAX_VALUE,(MAX_VALUE-LAST_VALUE) AS REMAINING_VALUES
,Case
WhenLAST_VALUE < 0 Then100
Else (1 - Cast(LAST_VALUE As float(4)) / MAX_VALUE) * 100
End As [PERCENTLEFT]
, Case
When Cast(LAST_VALUE As float(4)) / MAX_VALUE >=@threshold
Then'WARNING: APPROACHING MAX LIMIT'
Else 'VALUE IS OK'
End As [STATUS]
,@@SERVERNAME AS SERVER_NAME
,GETDATE() ASLOGDATE
FROM#identityStatus
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IDENTITY_VALUE_WARNINGS_RESULTS]') AND type in (N'U'))
DROP TABLE [dbo].IDENTITY_VALUE_WARNINGS_RESULTS
SELECT
CONVERT(VARCHAR(10),DATABASE_NAME) AS DATABASE_NAME,
CONVERT(VARCHAR(25),TABLE_NAME) AS TABLE_NAME,
CONVERT(VARCHAR(25),COLUMN_NAME) AS COLUMN_NAME,
CONVERT(VARCHAR(10),DATA_TYPE) AS DATA_TYPE,
MAX_VALUE,
REMAINING_VALUES,
PERCENTLEFT,
CONVERT(VARCHAR(30),[STATUS] ) AS STATUS
INTOIDENTITY_VALUE_WARNINGS_RESULTS
FROMIDENTITY_VALUE_WARNINGS
WHEREPERCENTLEFT < @threshold
ORDER BY DATABASE_NAME,TABLE_NAME
Declare @sub varchar(100)
Declare @qry varchar(1000)
Declare @msg varchar(250)
Select @sub = @@SERVERNAME + ' : DATA TYPE LIMIT CHECK ' +Convert(Varchar(10), GetDate(), 101)
Select @msg = 'RUN FOLLOWING QUERY TO GETRESULTS' + CHAR(10) + CHAR(10) +
'SELECT* FROM MYadminDB.DBO.IDENTITY_VALUE_WARNINGS_RESULTS ORDER BYDATABASE_NAME,TABLE_NAME' + CHAR(10) + CHAR(10)
Select @qry = 'SET NOCOUNT ON
SELECT* FROM MYadminDB.DBO.IDENTITY_VALUE_WARNINGS_RESULTS ORDER BYDATABASE_NAME,TABLE_NAME'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMail',
@recipients ='admin@company.com',
@body =@msg,
@subject =@sub ,
@query =@qry ;
