Alert for low identities available
Hi,
simply create the proc and run this one:
EXEC monitor.dbo.CTRL_IdentityAvailability
@SendMail = 'TRUE'
, @EMailAddress = 'youraddress@yourdomain.com'
, @EMailCC = ''
, @EMailBCC = ''
, @Debug = 0
, @MailFormat = 'TEXT'
The proc show for each table, for each db, the identity fields near to the higher limit
The proc has internally set the alert thresholds like this:
INSERT INTO #MaxValues(TypeIdentity, MaxValue, PercAlert)
VALUES ('bigint', 9223372036854775807, 1)
, ('int', 2147483647, 5)
, ('smallint', 32767, 10)
, ('tinyint', 255, 10)
You can change the values 1, 5, 10 to what you need. These are the higher limits after which the mail (or the alert) starts.
The parameter @Sendmail enable the creation (with @mailformat TEXT or HTML) and send the alert message to @EmailAddress, @EMailCC and @EMailBCC.
@Debug = 1 shows the intermediate results without sending any mail.
You can also create a schedule to monitor periodically the amount of id available
Happy coding
Gianmarco
ALTER PROCEDURE CTRL_IdentityAvailability (
@SendMail bit = 'TRUE'
, @EMailAddress varchar(1000) = ''
, @EMailCC varchar(1000) = ''
, @EMailBCC varchar(1000) = ''
, @MailFormat varchar(10) = 'TEXT'
, @Debug int = 0
)
AS
BEGIN
DECLARE @DBName varchar(1000)
, @TableName varchar(1000)
, @TypeIdentity varchar(100)
, @IDActual bigint
, @MaxVal bigint
, @Perc int
, @PercReal decimal(10,6)
, @SQL varchar(8000)
, @NRows int = 0
, @SubjectMail varchar(1000) = ''
, @BodyMail varchar(8000) = ''
, @BodyFormat varchar(10) = 'TEXT'
CREATE TABLE #Tables (
DBName varchar(1000)
, SchemaName varchar(100)
, TabName varchar(1000)
, Ident bigint
, MaxValue bigint
, Perc int
, RealPerc decimal(20,4)
)
CREATE TABLE #DBAtWork (
DBName varchar(1000)
)
CREATE TABLE #TablesAtWork (
DBName varchar(1000)
, SchemaName varchar(100)
, TabName varchar(1000)
, Ident bigint
, Data_Type varchar(100)
)
CREATE TABLE #MaxValues(
TypeIdentity varchar(100)
, MaxValue bigint
, PercAlert int
)
INSERT INTO #DBAtWork (DBName)
SELECT name
FROM sys.databases
WHERE state = 0
AND name NOT IN ('master','tempdb','model','msdb')
IF @debug = 1
BEGIN
SELECT * FROM #DBAtWork
END
DECLARE curDB CURSOR FOR
SELECT DBName FROM #DBAtWork
OPEN curDB
FETCH NEXT FROM curDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = ';WITH TableWithDb AS (SELECT ''' + @DBName + ''' DbName, * FROM ' + @DBName + '.sys.tables)
INSERT INTO #TablesAtWork(DBName, SchemaName, TabName, Ident, Data_Type)
SELECT ''' + @DBName + ''' DBName, SM.name, ST.name, IDENT_CURRENT (ST.DbName + ''.'' + SM.name + ''.'' + ST.name), IC.Data_Type
FROM TableWithDb ST
INNER JOIN ' + @DBName + '.INFORMATION_SCHEMA.COLUMNS IC ON IC.TABLE_NAME = ST.name
INNER JOIN ' + @DBName + '.sys.columns SC ON ST.object_id = SC.object_id AND IC.COLUMN_NAME = SC.Name
INNER JOIN ' + @DBName + '.sys.schemas SM ON ST.schema_id = SM.schema_id
WHERE SC.is_identity = 1
ORDER BY 1'
IF @debug = 1
BEGIN
print 'Processing db: ' + @DBName
print @sql
END
EXEC (@SQL)
FETCH NEXT FROM curDB INTO @DBName
END
CLOSE curDB
DEALLOCATE curDB
IF @debug = 1
BEGIN
SELECT * FROM #TablesAtWork
END
INSERT INTO #MaxValues(TypeIdentity, MaxValue, PercAlert)
VALUES ('bigint', 9223372036854775807, 1)
, ('int', 2147483647, 5)
, ('smallint', 32767, 10)
, ('tinyint', 255, 10)
IF @debug = 1
BEGIN
SELECT * FROM #MaxValues
END
INSERT INTO #Tables(DBName, SchemaName, TabName, Ident, MaxValue, Perc, RealPerc)
SELECT ST.DBName, ST.SchemaName, ST.TabName TableName
, ST.Ident AS Current_Identity, MV.MaxValue, MV.PercAlert Perc, (MV.MaxValue - ST.Ident) * 100.0 / MV.MaxValue
FROM #TablesAtWork ST
INNER JOIN #MaxValues MV ON ST.Data_Type = MV.TypeIdentity
WHERE (MV.MaxValue - ST.Ident) * 100.0 / MV.MaxValue < MV.PercAlert
SELECT @NRows = @@ROWCOUNT
IF @debug = 1
BEGIN
SELECT * FROM #Tables
SELECT @NRows NRows
SELECT @EMailAddress EMailAddress, @EMailCC EMailCC, @EMailBCC EMailBCC, @Sendmail Sendmail
END
IF @Sendmail = 'TRUE' AND (@EMailAddress <> '' OR @EMailCC <> '' OR @EMailBCC <> '')
BEGIN
IF @NRows > 0
BEGIN
IF @debug = 1
BEGIN
SELECT @SubjectMail SubjectMail, @BodyMail BodyMail
END
SET @SubjectMail = 'WARNING. Low number of ID available'
IF @MailFormat = 'HTML'
BEGIN
SET @BodyMail = @BodyMail + '<HTML><BODY><TABLE>' + char(13) + char(10)
SET @BodyMail = @BodyMail + '<TR>'
SET @BodyMail = @BodyMail + '<TD>Table</TD>'
SET @BodyMail = @BodyMail + '<TD>Next ID</TD>'
SET @BodyMail = @BodyMail + '<TD>ID Max</TD>'
SET @BodyMail = @BodyMail + '<TD>% Limit</TD>'
SET @BodyMail = @BodyMail + '<TD>% Actual</TD>'
SET @BodyMail = @BodyMail + '</TR>' + char(13) + char(10)
END
ELSE
BEGIN
SET @BodyMail = @BodyMail + LEFT('Table' + SPACE(70), 70)
SET @BodyMail = @BodyMail + LEFT('Next ID' + SPACE(30), 30)
SET @BodyMail = @BodyMail + LEFT('ID Max' + SPACE(30), 30)
SET @BodyMail = @BodyMail + LEFT('% Limit' + SPACE(10), 10)
SET @BodyMail = @BodyMail + LEFT('% Actual' + SPACE(10), 10)
SET @BodyMail = @BodyMail + char(13) + char(10)
END
DECLARE curTabOut CURSOR FOR
SELECT DBName + '.' + SchemaName + '.' + TabName, Ident, MaxValue, Perc, RealPerc FROM #Tables
OPEN curTabOut
FETCH NEXT FROM curTabOut INTO @TableName, @IDActual, @MaxVal, @Perc, @PercReal
WHILE @@FETCH_STATUS = 0
BEGIN
IF @MailFormat = 'HTML'
BEGIN
SET @BodyMail = @BodyMail + '<TR>'
SET @BodyMail = @BodyMail + '<TD>' + @TableName + '</TD>'
SET @BodyMail = @BodyMail + '<TD>' + CAST(@IDActual AS varchar(30)) + '</TD>'
SET @BodyMail = @BodyMail + '<TD>' + CAST(@MaxVal AS varchar(30)) + '</TD>'
SET @BodyMail = @BodyMail + '<TD>' + CAST(@Perc as varchar(10)) + '</TD>'
SET @BodyMail = @BodyMail + '<TD>' + CAST(@PercReal as varchar(100)) + '</TD>'
SET @BodyMail = @BodyMail + '</TR>' + char(13) + char(10)
END
ELSE
BEGIN
SET @BodyMail = @BodyMail + LEFT(@TableName + SPACE(70), 70)
SET @BodyMail = @BodyMail + LEFT(CAST(@IDActual AS varchar(30)) + SPACE(30), 30)
SET @BodyMail = @BodyMail + LEFT(CAST(@MaxVal AS varchar(30)) + SPACE(30), 30)
SET @BodyMail = @BodyMail + LEFT(CAST(@Perc as varchar(10)) + SPACE(10), 10)
SET @BodyMail = @BodyMail + LEFT(CAST(@PercReal as varchar(100)) + SPACE(10), 10)
SET @BodyMail = @BodyMail + char(13) + char(10)
END
FETCH NEXT FROM curTabOut INTO @TableName, @IDActual, @MaxVal, @Perc, @PercReal
END
CLOSE curTabOut
DEALLOCATE curTabOut
IF @MailFormat = 'HTML'
BEGIN
SET @BodyMail = @BodyMail + '</TABLE></BODY></HTML>' + char(13) + char(10)
SET @BodyFormat = 'HTML'
END
IF @debug = 1
BEGIN
SELECT @SubjectMail SubjectMail, @BodyMail BodyMail
END
IF @debug = 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YOUR PROFILE NAME'
, @recipients = @EMailAddress
, @copy_recipients = @EMailCC
, @blind_copy_recipients = @EMailBCC
, @body = @BodyMail
, @subject = @SubjectMail
, @body_format = @BodyFormat
END
ELSE
BEGIN
print @BodyMail
END
END
END
ELSE
BEGIN
SELECT * FROM #Tables order by 2 desc
END
DROP TABLE #DBAtWork
DROP TABLE #Tables
DROP TABLE #TablesAtWork
DROP TABLE #MaxValues
END 
