Maksimum IDENTITY Değerleri

- Enes Kütük
- 2.150
- Orta
Veritabanlarındaki kritik IDENTITY değerlerini kontrol edip mail atan kod.
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
Yorum yazabilmek için üye girişi yapmanız gerekiyor!