Transaction Log doluluk oranını tespit etmek

"SQL Server Kurulumlarında Gözden Kaçanlar - 8" başlıklı yazımda belirttiğim maddelerden biri olan Transaction Log dosyasının doluluk oranını takip etmek için örneğin 3. parti bir yazılım olan Quest'in Stoplight ürününü de kullanabilirsiniz, aşağıda anlattığım yöntemi de. 3. ürün paralı, anlatacağım yöntem beleş ;)

Normal şartlar altında Transaction Log dosyalarının boyutları ve içlerinin doluluk oranları gibi bilgileri DBCC SQLPERF(LOGSPACE) komutuyla alabilirsiniz. Zaten yazdığım Script'te de bunu kullandım. Ayrıca bu işlemi parametrik bir hale getirdim. Parametreler için bir tablo ve sonuçları almak için de bir SP iş görüyor. Eğer her SQL Server Instance'ınızda DBA araçları için oluşturduğunuz bir DBA veritabanınız varsa bu iki nesneyi de o veritabanında tutabilirsiniz.

Ben DBA isimli bir SCHEMA kullanıyorum DBA araçları için, siz de isterseniz böyle yapabilirsiniz veya kendi standardınız varsa onu kullanabilirsiniz.

Tüm bu işlemlere başlamadan önce, komutları nesneleri yaratmak istediğiniz veritabanında çalıştıracağınızdan emin olun. Yeni bir SCHEMA yaratmak için (örneğin DBA):

CREATE SCHEMA [DBA]

Tablonun Script'i şöyle:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [DBA].[GET_USED_LOG_PERCENTAGE_PARAMS](
[dbname] [nvarchar](250) NOT NULL,
[major] [tinyint] NOT NULL,
[critical] [tinyint] NOT NULL
) ON [PRIMARY]
GO

dbname: Bu alana, hangi veritabanının Transaction Log doluluk oranını izlemek istiyorsak, o veritabanının adını yazıyoruz.
major: Bu alana, kritik hatadan önce gelmesini istediğimiz uyarı değerini giriyoruz. Yani örneğin dosyanın boyutu 10GB ve dosyanın %60'ı doldu ve eğer major değer olarak "60" girdiyseniz, kendinize bu eşik değere ulaşıldığında (örneğin Database Mail ile) e-posta göndertebilirsiniz.
critical: Bu alana da kritik eşik değerini giriyoruz. Bu değere ulaşıldığında artık kesinkes haberdar edilmek istersiniz, bu değeri buna göre ayarlamalısınız. Örneğin "85" gibi.

Tabloyu oluşturduktan sonra, Transaction Log'ların doluluk oranını denetleyecek Stored Procedure (SP)'u da oluşturabilirsiniz:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DBA].[GET_USED_LOG_PERCENTAGE]
AS
IF EXISTS(SELECT * from tempdb..sysobjects where name like '#LogSpaceUsed%')
DROP TABLE #LogSpaceUsed
CREATE TABLE #LogSpaceUsed (dbname nvarchar(200) not null, logsize dec not null, spaceused dec not null, [status] tinyint, )
INSERT INTO #LogSpaceUsed exec sp_executesql N'dbcc sqlperf(logspace)'
SELECT PRM.dbname [Database], spaceused [Percentage Used], PRM.[major], PRM.[critical], 'CRITICAL' AS [Severity] FROM #LogSpaceUsed AS LSU
INNER JOIN DBA.GET_USED_LOG_PERCENTAGE_PARAMS AS PRM ON PRM.dbname = LSU.dbname
WHERE PRM.dbname NOT IN ('model') AND LSU.[spaceused] >= PRM.[critical]
Union all
SELECT PRM.dbname [Database], spaceused [Percentage Used], PRM.[major], PRM.[critical], 'MAJOR' AS [Severity] FROM #LogSpaceUsed AS LSU
INNER JOIN DBA.GET_USED_LOG_PERCENTAGE_PARAMS AS PRM ON PRM.dbname = LSU.dbname
WHERE PRM.dbname NOT IN ('model') AND (LSU.[spaceused] >= PRM.[major] AND LSU.[spaceused] < PRM.[critical])

Bu SP ile, önceden oluşturduğumuz [DBA].[GET_USED_LOG_PERCENTAGE_PARAMS] isimli tablodaki parametreler kullanılarak takip edilmesi istenen veritabanlarının Transaction Log'larını her bir veritabanı için ayrı ayrı belirlediğiniz Major ve Critical değerlere göre sorgulayabilirsiniz. Çıkan sonucu ister HP Openview gibi bir izleme uygulaması ile alırsınız, isterseniz Database Mail ile kendinize e-posta göndertebilirsiniz veya RAISERROR ile Log'lara da kaydettirebilirsiniz ihtiyacınıza göre.

İlgili bağlantılar:
SQL Server Kurulumlarında Gözden Kaçanlar - 8