Maksimum IDENTITY Değerleri

Bir IDENTITY kolon, veri tipinin maksimum değerine (int için 2.147.483.647, smallint için 32.767, tinyint için 255) ulaştığında tabloya daha fazla insert yapılamaz; SQL Server kolonun tipini otomatik olarak değiştiremediğinden de tablonun bir an önce alter edilmesi gerekir. Bu tür bir sorunu daha oluşmadan tespit edip gerekli önlemleri almak adına IDENTITY kolonlarının maksimum değerlerini kontrol edip kritik değeri aşanların detaylı bilgilerini mail atan bir store procedure oluşturalım:

USE MASTER

GO

CREATE PROCEDURE [dbo].[Get_MaxIdentityValues]
AS
BEGIN

-- Create edeceğimiz tablolar halihazırda varsa drop edelim:

IF OBJECT_ID('master.dbo.MaxIdentityValues', 'U') IS NOT NULL
  DROP TABLE master.dbo.MaxIdentityValues; 
IF OBJECT_ID('tempdb.dbo.#database_names', 'U') IS NOT NULL
  DROP TABLE #database_names; 
IF OBJECT_ID('tempdb.dbo.#database_names1', 'U') IS NOT NULL
  DROP TABLE #database_names1; 

IF OBJECT_ID('tempdb.dbo.#control_tables', 'U') IS NOT NULL
  DROP TABLE #control_tables; 

IF OBJECT_ID('tempdb.dbo.#control_columns', 'U') IS NOT NULL
  DROP TABLE #control_columns; 

-- Sunucudaki tüm veritabanlarını kontrol edip kritik IDENTITY değerlerini MaxIdentityValues tablosuna INSERT edelim:

CREATE TABLE master..MaxIdentityValues
(
Seed int,
Increment int,
Current_Identity bigint,
Database_Name varchar(50),
[Schema] varchar(50),
[Table] varchar(250)
)

-- Cursor tüm Database leri dolaşarak MaxIdentityValues tablosuna INSERT yapacak

SELECT * INTO #database_names 
from
(SELECT name from sys.databases where database_id>4 and state_desc = 'ONLINE')enes

DECLARE crs_length CURSOR FOR 
  SELECT name
  FROM   #database_names 
DECLARE @VAR_Name VARCHAR(2000) 
DECLARE @SQLString NVARCHAR(3000) 
OPEN crs_length 
FETCH next FROM crs_length INTO @VAR_Name

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @SQLString = 
N'USE '+@VAR_Name+'
INSERT INTO master..MaxIdentityValues (Seed, Increment, Current_Identity, Database_Name, [Schema], [Table]) SELECT
IDENT_SEED(IST.TABLE_SCHEMA + '+ Char(39) + '.' + Char(39) +'+ IST.TABLE_NAME) AS Seed,
IDENT_INCR(IST.TABLE_SCHEMA + '+ Char(39) + '.' + Char(39) +'+ IST.TABLE_NAME) AS Increment,
CASE Counts.RowCnt
WHEN 0 THEN 0 ELSE IDENT_CURRENT(IST.TABLE_SCHEMA + '+ Char(39) + '.' + Char(39) +'+ IST.TABLE_NAME) END AS Current_Identity,
'+ Char(39) +@VAR_Name+ Char(39) + ' AS Database_Name,
IST.TABLE_SCHEMA AS [Schema],
 IST.TABLE_NAME AS [Table]
FROM
INFORMATION_SCHEMA.TABLES IST
JOIN
(
SELECT
sc.name +'+ Char(39) + '.' + Char(39) +'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM
sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE
ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY
sc.name,ta.name
) Counts ON Counts.TableName = IST.TABLE_SCHEMA + '+ Char(39) + '.' + Char(39) +'+ IST.TABLE_NAME
WHERE
OBJECTPROPERTY(OBJECT_ID(IST.TABLE_SCHEMA + '+ Char(39) + '.' + Char(39) +'+ IST.TABLE_NAME), '+ Char(39) +'TableHasIdentity'+ Char(39) +') = 1
AND IST.TABLE_TYPE = '+ Char(39) +'BASE TABLE'+ Char(39) +
'AND IDENT_CURRENT(IST.TABLE_SCHEMA + '+ Char(39) + '.' + Char(39) +'+ IST.TABLE_NAME) > 2000000000 order by 3 desc'
--print @SQLString
      execute Sp_executesql 
       @SQLString 

      FETCH next FROM crs_length INTO @VAR_Name
  END 

CLOSE crs_length 
DEALLOCATE crs_length

-- Tabloya INSERT edilen kritik kolonların tipi ve bulunduğu tablonun adı bilgilerini çekelim: 

CREATE TABLE #control_tables(
	[Seed] [int] NULL,
	[Increment] [int] NULL,
	[Current_Identity] [bigint] NULL,
	[Database_Name] [varchar](50) NULL,
	[Schema] [varchar](50) NULL,
	[Table] [varchar](250) NULL
) ON [PRIMARY] 



CREATE TABLE #control_columns(
	[Table] [varchar](250) NULL,
	[Column] varchar (250) NULL,
	[Type] varchar(50) NULL
) ON [PRIMARY] 



INSERT INTO #control_tables(
	[Seed],
	[Increment],
	[Current_Identity],
	[Database_Name],
	[Schema],
	[Table])
SELECT * from [master].[dbo].[MaxIdentityValues]
where Current_Identity >= 2000000000


SELECT * INTO #database_names1 
from
(SELECT * from #control_tables)enes

DECLARE crs_length CURSOR FOR 
  SELECT 	[Database_Name],[Table] FROM   #database_names1
DECLARE @VAR_DBName VARCHAR(2000)  
DECLARE @VAR_Name1 VARCHAR(2000) 
DECLARE @SQLString1 NVARCHAR(3000) 
OPEN crs_length 
FETCH next FROM crs_length INTO @VAR_DBName,@VAR_Name

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @SQLString1 = 
N'USE '+@VAR_DBName+'
INSERT INTO #control_columns([Table],[Column], [Type]) 
SELECT t.name as [Table], c.name as [Column], TY.name as [Type] from sys.tables as t
inner join
sys.all_columns as c on t.object_id = c.object_id
INNER JOIN sys.[types] TY ON c.[system_type_id] = TY.[system_type_id] AND c.[user_type_id] = TY.[user_type_id]   
where c.is_identity = 1
and t.name = '+ Char(39) + @VAR_Name1 + Char(39) +''
--print @SQLString
      execute Sp_executesql @SQLString1 

      FETCH next FROM crs_length INTO @VAR_DBName,@VAR_Name1
  END 

CLOSE crs_length 
DEALLOCATE crs_length 

-- Son olarak çıkan sonucu kendimize mail atalım:

DECLARE @HTML  NVARCHAR(MAX) ;
		SET @HTML =
		N'<H3><font color="blue">IDENTITY MAX VALUE</font></H3>' +
		N'<table border="1">' +
		N'<tr><th><font color="red">DatabaseName</font>' +
		N'<th><font color="red">Schema</font>' +
		N'<th><font color="red">Table</font>' +
		N'<th><font color="red">Column</font>' +
		N'<th><font color="red">Type</font>' +
		N'<th><font color="red">CurrentIdentity</font></th><th><font color="red">Seed</font></th>'+
		CAST ( ( SELECT 						  
						 td = [Database_Name] , '',
						 td = [Schema] , '',
						 td = cc.[Table] , '',
						 td = [Column] , '',
						 td = [Type] , '',
						 td = [Current_Identity] , '',
						 td = [Seed], ''
				 	 FROM #control_tables as ct
					 inner join #control_columns as cc on ct.[Table] = cc.[Table]
				 WHERE Type != 'bigint'
				Order By Current_Identity desc
		  FOR XML PATH('tr'), TYPE 
		) AS NVARCHAR(MAX) ) +
		N'</table>'+

		N'<br>'
		
		--Şayet oluşturulan tablo ile aşağıdaki koşul sağlanmıyorsa mail atmıyor
		SELECT top 1 cc.* FROM #control_tables as ct
					 inner join #control_columns as cc on ct.[Table] = cc.[Table]
				 WHERE Type != 'bigint'
				 if @@ROWCOUNT>0
				 begin
	--query sonucunu mail atan bölüm
	EXEC msdb.dbo.sp_send_dbmail 
		@profile_name = 'profil_adi',
		@recipients = 'mail_adresi1;mail_adresi2',
		@subject = 'Maksimum Identity Degerleri',
		@body = @HTML,
		@body_format = 'HTML';

		end

END

NOT: IDENTITY kolonların hemen hepsi integer tipinde olduğu için diğer veri tiplerini gözardı ettim, umarım işinize yarar :)

İyi Çalışmalar