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

Rate

Share

Share

Rate