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.

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())
)

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

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 

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.

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='
<style type="text/css">
table.imagetable {
	font-family: verdana,arial,sans-serif;
	font-size:11px;
	color:#333333;
	border-width: 1px;
	border-color: #999999;
	border-collapse: collapse;}
table.imagetable th {
	background:#b5cfd2;
	border-width: 1px;
	padding: 8px;
	border-style: solid;
	border-color: #999999;}
table.imagetable td {
	background:#dcddc0;
	border-width: 1px;
	padding: 8px;
	border-style: solid;
	border-color: #999999;}
</style>

'

SET @result = @css + '<html><body bgcolor="#F2F2F2">

<font size="2" face="Tahoma">Sayın <b>Yetkili,</b>

SERVICE DESK DBCC CHECK ayrıntıları aşağıdaki gibidir.
<h2>Service Desk DBCC Check Daily Report</h2>
<table class="imagetable" cellpadding="0" cellspacing="0" width="100%">
<th>Error</th>
<th>Level</th>
<th>State</th>
<th>Message</th>
<th>Status</th>
<th>Object</th>
<th>Time Stamp</th>

'
SET @result2 = '
<h2>Service Desk DBCC Check Montly Report</h2>
<table class="imagetable" cellpadding="0" cellspacing="0" width="100%">
<th>Error</th>
<th>Level</th>
<th>State</th>
<th>Message</th>
<th>Status</th>
<th>Object</th>
<th>Time Stamp</th>

'

---------------------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='
<tr><left>
<td align="center">'+CAST(ISNULL(@error,'') AS NVARCHAR(100))+'</td>
<td align="center">'+CAST(ISNULL(@level,'') AS NVARCHAR(100))+'</td>
<td align="center">'+CAST(ISNULL(@state,'') AS NVARCHAR(100))+'</td>
<td align="left">'+ISNULL(@messagetext,'')+'</td>
<td align="center">'+CAST(ISNULL(@status,'') AS NVARCHAR(100))+'</font></td>
<td align="center">'+ISNULL(@objectid,'')+'</td>
<td align="center">'+CONVERT(NVARCHAR(10),@timestamp,104)+' '+CONVERT(NVARCHAR(5),@timestamp,108)+'</td>

</center></tr>

'

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='
<tr><left>
<td align="center">'+CAST(ISNULL(@error,'') AS NVARCHAR(100))+'</td>
<td align="center">'+CAST(ISNULL(@level,'') AS NVARCHAR(100))+'</td>
<td align="center">'+CAST(ISNULL(@state,'') AS NVARCHAR(100))+'</td>
<td align="left">'+ISNULL(@messagetext,'')+'</td>
<td align="center">'+CAST(ISNULL(@status,'') AS NVARCHAR(100))+'</td>
<td align="center">'+ISNULL(@objectid,'')+'</td>
<td align="center">'+CONVERT(NVARCHAR(10),@timestamp,104)+' '+CONVERT(NVARCHAR(5),@timestamp,108)+'</td>

</center></tr>

'

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='
<h2>Service Desk System Report</h2>
<table class="imagetable" cellpadding="0" cellspacing="0" width="100%">
<th>Toplam Çalışma Süresi</th>


<tr><left>
<td align="center"><font size="2" face="Tahoma">'+ISNULL(@total,'')+'</font></td>
</tr>

</table>

'

-----------------------AYLIK VE GUNLUK SONUCLAR YAZILIYOR------------------------

set @message=(select @result + '</table>

'+@result2+'</table>

' + @summary + '

Bilginize,

<img src="http://www.ebi.com.tr/wp-content/uploads/yootheme/demo_pie.gif"></img>
----------------------------------------------------

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.
</font></body></html>')

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

EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name ='SERVICE_DESK_DW_MAIL', @blind_copy_recipients = 'ad.soyad@sirket.com.tr', @body = @message, @subject = @sub, @body_format='HTML'

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 Cevap Yazın