← Tüm yazılar

SQL Server: DBCC ile Veritabanı Tutarsızlıklarını Yakalamak ve Maille Bilgilendirmek

Merhaba,

Bugün sizlere yoğun transaction’a maruz kalmış canlı operasyonel bir veritabanının belirli periyotlarla tutarsızlıklarını, bozukluklarını ve hatalarını nasıl yakalayabileceğimizi ve bunları rapor olarak ilgili kişilere müdahale edebilmeleri için mail ile nasıl gönderebileceğimizi anlatmaya çalışacağım. Sanırım uzun bir giriş cümlesi oldu 😀

Öncelikle veritabanımızda DBCC adında bir veritabanı oluşturalım. Oluşturduğumuz veritabanında aşağıdaki sql kodu ile tablomuzu yaratalım.

[sourcecode language=sql]
CREATE TABLE [dbo].[DBCC_RESULT](
[Error] [bigint] NULL,
[Level] [bigint] NULL,
[State] [bigint] NULL,
[MessageText] [nvarchar](max) NULL,
[RepairLevel] [nvarchar](max) NULL,
[Status] [bigint] NULL,
[DbId] [bigint] NULL,
[DbFragId] [bigint] NULL,
[ObjectId] [bigint] NULL,
[IndexId] [bigint] NULL,
[PartitionId] [bigint] NULL,
[AllocUnitId] [bigint] NULL,
[RidDbId] [bigint] NULL,
[RidPruId] [bigint] NULL,
[File] [bigint] NULL,
[Page] [bigint] NULL,
[Slot] [bigint] NULL,
[RefDbId] [bigint] NULL,
[RefPruId] [bigint] NULL,
[RefFile] [bigint] NULL,
[RefPage] [bigint] NULL,
[RefSlot] [bigint] NULL,
[Allocation] [bigint] NULL,
[TimeStamp] [datetime] NULL CONSTRAINT [DF_dbcc_history_TimeStamp] DEFAULT (getdate())
)
[/sourcecode]

Tablomuzu oluşturduktan sonra da aşağıdaki sql kodu ile bir tane view oluşturalım.

[sourcecode language=sql]
CREATE VIEW [dbo].[VW_DBCC_RESULT]
AS
SELECT
R.[Error],
R.[Level],
R.[State],
R.[MessageText],
R.[RepairLevel],
R.[Status],
R.[DbId],
R.[DbFragId],
ISNULL((Select name from [mdb].sys.objects where object_id=R.ObjectId),’mdb’) ObjectId,
R.[IndexId],
R.[PartitionId],
R.[AllocUnitId],
R.[RidDbId],
R.[RidPruId],
R.[File],
R.[Page],
R.[Slot],
R.[RefDbId],
R.[RefPruId],
R.[RefFile],
R.[RefPage],
R.[RefSlot],
R.[Allocation],
R.[TimeStamp]
FROM DBCC_RESULT R
[/sourcecode]

Tüm bunları tamamladıktan sonra sıra asıl işi yapacak olan stored procedure’ımızı oluşturmaya geldi. Bu stored procedure canlı veritabanını analiz edip sonuçları tabloya yazdıktan sonra ilgili kişilere veritabanı durumu hakkında bilgi gönderecek.

Stored Procedure’ımızı da aşağıdaki T-SQL kodunu kullanarak oluşturabiliriz.

[sourcecode language=sql]
CREATE PROC [dbo].[DBCC_CHECK_MDB]
AS
SET NOCOUNT ON

DECLARE @result NVARCHAR(MAX)
DECLARE @table NVARCHAR(MAX)
DECLARE @result2 NVARCHAR(MAX)
DECLARE @table2 NVARCHAR(MAX)
DECLARE @message NVARCHAR(MAX)
DECLARE @transfer_start_date DATETIME
DECLARE @transfer_end_date DATETIME
DECLARE @sub NVARCHAR(MAX)
DECLARE @time BIGINT
DECLARE @total NVARCHAR(MAX)
DECLARE @summary NVARCHAR(MAX)
DECLARE @css NVARCHAR(MAX)
DECLARE @today datetime = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
declare @last datetime = DATEADD(d,0,DATEADD(mm, DATEDIFF(m,0,@today),0))
DECLARE @error BIGINT
DECLARE @level BIGINT
DECLARE @state BIGINT
DECLARE @messagetext NVARCHAR(MAX)
DECLARE @status BIGINT
DECLARE @objectid NVARCHAR(MAX)
DECLARE @timestamp DATETIME

SET @transfer_start_date=GETDATE()

—————VERITABANI ANALIZ EDILIYOR VE SONUÇLARI TABLOYA AKTARIRILIYOR———————————————

INSERT INTO DBCC_RESULT ([Error], [Level], [State], [MessageText], [RepairLevel], [Status], [DbId], [DbFragId], [ObjectId], [IndexId], [PartitionId], [AllocUnitId], [RidDbId], [RidPruId], [File], [Page], [Slot], [RefDbId], [RefPruId], [RefFile], [RefPage], [RefSlot], [Allocation])
EXEC (‘dbcc checkdb(”mdb”) with tableresults’)

SET @transfer_end_date=GETDATE()

————–HTML TABLOLARIN DAHA GUZEL GOZUKMESI ICIN CSS KULLANIYORUZ—————-

SET @css=’

‘

SET @result = @css + ‘

Sayın Yetkili,

SERVICE DESK DBCC CHECK ayrıntıları aşağıdaki gibidir.

Service Desk DBCC Check Daily Report

‘
SET @result2 = ‘

Service Desk DBCC Check Montly Report

Error Level State Message Status Object Time Stamp

‘

———————CURSOR ILE HTML GUNLUK RAPOR OLUŞTURULUYOR—————————————

DECLARE cursLOG CURSOR FOR

SELECT [Error],[Level],[State],[MessageText],[Status],[ObjectId],[TimeStamp] FROM VW_DBCC_RESULT WHERE MessageText like ‘CHECKDB%’ AND DATEADD(dd, 0, DATEDIFF(dd, 0, [TimeStamp]))=@today

OPEN cursLOG
FETCH NEXT FROM cursLOG INTO @error, @level, @state, @messagetext, @status, @objectid, @timestamp
WHILE @@FETCH_STATUS = 0 BEGIN

set @table=’

‘

set @result=@result+@table

FETCH NEXT FROM cursLOG INTO @error, @level, @state, @messagetext, @status, @objectid, @timestamp
END
CLOSE cursLOG
DEALLOCATE cursLOG

————————CURSOR ILE HTML ILE AYLIK RAPOR OLUŞTURULUYOR————————————–

DECLARE cursLOG2 CURSOR FOR

SELECT [Error],[Level],[State],[MessageText],[Status],[ObjectId],[TimeStamp] FROM VW_DBCC_RESULT WHERE MessageText LIKE ‘CHECKDB%’ AND MessageText LIKE ‘%mdb%’ AND DATEADD(dd, 0, DATEDIFF(dd, 0, [TimeStamp])) BETWEEN @last AND @today ORDER BY [TimeStamp] DESC

OPEN cursLOG2
FETCH NEXT FROM cursLOG2 INTO @error, @level, @state, @messagetext, @status, @objectid, @timestamp
WHILE @@FETCH_STATUS = 0 BEGIN

set @table2=’

‘

set @result2=@result2+@table2

FETCH NEXT FROM cursLOG2 INTO @error, @level, @state, @messagetext, @status, @objectid, @timestamp
END
CLOSE cursLOG2
DEALLOCATE cursLOG2

SET @sub=’SERVICE DESK DBCC CHECK REPORT’

————————JOB CALISMA SURESI HESAPLANIYOR————————–

SET @time=(SELECT DATEDIFF(SECOND,MIN(@TRANSFER_START_DATE),MAX(@transfer_end_date)))
SET @total=(SELECT CONVERT(NVARCHAR(100), DATEADD(ms, @time * 1000, 0), 114))

————————JOB CALISMA SURESI YAZILIYOR—————————–

SET @summary=’

Service Desk System Report

Error Level State Message Status Object Time Stamp
‘+CAST(ISNULL(@error,”) AS NVARCHAR(100))+’ ‘+CAST(ISNULL(@level,”) AS NVARCHAR(100))+’ ‘+CAST(ISNULL(@state,”) AS NVARCHAR(100))+’ ‘+ISNULL(@messagetext,”)+’ ‘+CAST(ISNULL(@status,”) AS NVARCHAR(100))+’ ‘+ISNULL(@objectid,”)+’ ‘+CONVERT(NVARCHAR(10),@timestamp,104)+’ ‘+CONVERT(NVARCHAR(5),@timestamp,108)+’
‘+CAST(ISNULL(@error,”) AS NVARCHAR(100))+’ ‘+CAST(ISNULL(@level,”) AS NVARCHAR(100))+’ ‘+CAST(ISNULL(@state,”) AS NVARCHAR(100))+’ ‘+ISNULL(@messagetext,”)+’ ‘+CAST(ISNULL(@status,”) AS NVARCHAR(100))+’ ‘+ISNULL(@objectid,”)+’ ‘+CONVERT(NVARCHAR(10),@timestamp,104)+’ ‘+CONVERT(NVARCHAR(5),@timestamp,108)+’

Toplam Çalışma Süresi
‘+ISNULL(@total,”)+’

‘

———————–AYLIK VE GUNLUK SONUCLAR YAZILIYOR————————

set @message=(select @result + ‘

‘+@result2+’

‘ + @summary + ‘

Bilginize,


—————————————————-

Bu e-posta mesajı ve ekleri sadece gönderildiği kişi veya kuruma özeldir.
Doğru alıcıya ulaşmamış olması halinde, bu mesajın başka bir alıcıya yönlendirilmesi, kopyalanması veya kullanılması yasaktır.
—————————————————-
This e-mail and any attachments transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.
If you are not the intended recipient you are hereby notified that any forwarding, copying or use of the information is prohibited.
‘)

—————-HTML SONUÇLAR MAIL ILE ILGILI KISILERE GONDERILIYOR————–

EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name =’SERVICE_DESK_DW_MAIL’, @blind_copy_recipients = ‘[email protected]’, @body = @message, @subject = @sub, @body_format=’HTML’
[/sourcecode]

Tüm bu işlemleri tamamladıktan sonra stored procedure’ımıza günlük çalışacak şekilde görev atayabiliriz.

Görev çalıştıktan aşağıdaki formatta bir mail gelecektir.

Kolay gelsin 🙂

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir