هشدار خودکار برای صفحاتی از بانک اطلاعاتی که SQL Server به آنها مضنون است

هشدار خودکار برای صفحاتی از بانک اطلاعاتی که SQL Server به آنها مضنون است

نوشته شده توسط: سید محمد حسینی
۱۷ بهمن ۱۳۹۴
زمان مطالعه: 6 دقیقه
۰
(۰)

مقدمه

من فکر می کنم که مسائلی در مورد دیسک وجود دارد که بهتر است در صورتی که SQL Server با هرگونه خرابی در هر یک از دیتا فایل های ذخیره شده بر روی دیسک مواجه شد، این مشکلات را به شکلی، مثلا به صورت ایمیل به ما هشدار دهد.
 خوشبختانه، در SQL Server 2005 جدول جدیدی در بانک اطلاعاتی msdb معرفی شد که suspect_pages نامیده می شود، که در این جدول، هر صفحه ای که مضنون به مشکل داشتن است لاگ می شود. هر زمان که موتور بانک اطلاعاتی به سراغ id صفحه ای برود و گمان کند که دارای مشکل است(از طریق خواندن صفحه در پرسوجو، یک عملیات DBCC، Backup/Restore و…)، id این صفحه به همراه اطلاعات جزئی تری در مورد رویداد مربوطه در این جدول درج/به روزرسانی می شود.

این اطلاعات جزئی را می توان در ستون event_type در این جدول مورد جستجو قرار داد که شرح هر یک از این نوع ها در ادامه آورده شده است. توجه داشته باشید که event_typeهای ۴ و ۵ و ۷ در واقع نشان دهنده این هستند که مشکل، حل شده است.
 
 البته به این نکته مهم باید توجه داشت که جدول suspect_pages توسط DBA یا هر کاربری که دارای مجوزهای لازم جهت انجام برخی به روزرسانی ها در بانک اطلاعاتی msdb می باشد، نگهداری می شود. این جدول محدودیتی دارد و بیش از ۱۰۰۰ سطر در آن نمی توان درج نمود، به همین علت، در صورتی که جدول به طور کامل پر شده باشد برای درج رکوردهای جدید، در صورت نیاز باید رکوردهای قبلی حذف شوند تا امکان درج رکوردهای جدید فراهم گردد. برای اطلاعات بیشتر در مورد این جدول می توانید به این لینک مراجعه کنید.
اولین چیزی که مورد نیاز است، پرسوجویی برای واکشی اطلاعات از جدول suspect_pages می باشد. با توجه به اینکه این جدول عموما حاوی شناسه هایی عددی بوده و این شناسه ها دارای توصیف مناسبی نیستند، با کمک گرفتن از جداول sys.databases و sys.master_files و همچنین نمایش سایر اطلاعات مورد نیاز، می توان مشکل را بهتر رهگیری و حل نمود. این پرسوجو در ادامه آورده شده است.
select sp.database_id "Database ID",
d.name "Database",
sp.file_id "File ID",
mf.physical_name "File",
sp.page_id "Page ID",
case when sp.event_type = 1 then '823 or 824 error other than a bad checksum or a torn page'
when sp.event_type = 2 then 'Bad checksum'
when sp.event_type = 3 then 'Torn Page'
when sp.event_type = 4 then 'Restored (The page was restored after it was marked bad)'
when sp.event_type = 5 then 'Repaired (DBCC repaired the page)'
when sp.event_type = 7 then 'Deallocated by DBCC'
end as "Event Desc",
sp.error_count "Error Count",
sp.last_update_date "Last Updated"
from msdb.dbo.suspect_pages sp
inner join sys.databases d on d.database_id=sp.database_id
inner join sys.master_files mf on mf.database_id=sp.database_id and mf.file_id=sp.file_id
تا اینجا ما پرسوجویی با تمامی اطلاعات مورد نیاز را در اختیار داریم. اکنون جهت ارسال یک ایمیل در هنگامی که داده ای یافت شد، می بایست این پرسوجو را با برخی موارد دیگر ترکیب نماییم. با استفاده از ایمیل بانک اطلاعاتی و برخی از موارد قالب بندی html و برخی نمونه های دیگر(اطلاعات بیشتر در این مورد)، می توانیم قالب یک ایمیل هشدار مناسب را تهیه کنیم. کد TSQL زیر برای این منظور است و می توان جهت مانیتور کردن اینستنس، آن را در قالب یک job ایجاد نمود.
 declare @count integer
declare @tableHTML nvarchar(MAX);
declare @subj nvarchar(100)
select @count=count(1)
from msdb.dbo.suspect_pages;
set @subj = 'Suspect Pages Found in ' + @@SERVERNAME;
set @tableHTML =
N'<H1>Suspect Pages Found in ' + @@SERVERNAME + ', details are below.</H1>' +
N'<table border="1">' +
N'<tr><th>Database ID</th><th>Database</th>' +
N'<th>File ID</th><th>File</th><th>Page ID</th>' +
N'<th>Event Desc</th><th>Error Count</th><th>Last Updated</th></tr>' +
cast ( ( select td = sp.database_id, '',
td = d.name, '',
td = sp.file_id, '',
td = mf.physical_name, '',
td = sp.page_id, '',
td = case when sp.event_type = 1 then '823 or 824 error other than a bad checksum or a torn page'
when sp.event_type = 2 then 'Bad checksum'
when sp.event_type = 3 then 'Torn Page'
when sp.event_type = 4 then 'Restored (The page was restored after it was marked bad)'
when sp.event_type = 5 then 'Repaired (DBCC repaired the page)'
when sp.event_type = 7 then 'Deallocated by DBCC'
end, '',
td = sp.error_count, '',
td = sp.last_update_date
from msdb.dbo.suspect_pages sp
inner join sys.databases d on d.database_id=sp.database_id
inner join sys.master_files mf on mf.database_id=sp.database_id and mf.file_id=sp.file_id
for xml path('tr'), TYPE
) as nvarchar(max) ) +
N'</table>' ;
IF @count > 0
exec msdb.dbo.sp_send_dbmail
@recipients=N'testuser@test.com',
@body= @tableHTML,
@subject = @subj,
@body_format = 'HTML',
@profile_name ='testprofile'
به عنوان یک مورد اضافه تر، فرض کنید که این کد در بازه های روزانه، هفتگی و ماهیانه با توجه به نیاز سازمان شما در حال اجرا می باشد، می توان در انتهای job مربوطه اقدام به خالی/کوچک کردن این جدول کرد. برای این کار با استفاده از کد TSQL زیر که در انتهای job مورد نظر اضافه می شود، هر زمان که تاریخ رکوردهای جدول، مربوط به بیش از ۹۰ روز قبل باشد می توان آنها را حذف نمود. البته می توانیم این بازه را با توجه به نیاز کمتر یا بیشتر کنیم.
delete from msdb.dbo.suspect_pages
where last_update_date < getdate()-90
جدول suspect_pages موجب بررسی تمامی بانک های اطلاعاتی اینستنس شما می شود و من امیدوارم که چیزی در بانک های شما پیدا نکند.

چه رتبه ای می‌دهید؟

میانگین ۰ / ۵. از مجموع ۰

اولین نفر باش

title sign
معرفی نویسنده
سید محمد حسینی
مقالات
11 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
سید محمد حسینی
پروفایل نویسنده
title sign
دیدگاه کاربران

    • با سلام و خسته نباشید خدمت شما
      از بابت این مقاله ارزشمند بسیار سپاسگزارم
      لطف کردید

      • با سلام و سپاس از همراهی شما دوست عزیز

    • با سلام و خسته نباشید خدمت شما
      از بابت این مقاله ارزشمند بسیار سپاسگزارم
      لطف کردید

    • با سلام و عرض ادب

      ممنون بابت مقاله بسیار زیبا و کاربردی شما دوست عزیز

    • سلام ممنون بابت مقاله جامع و کاملی که ارائه کردید و از جناب طاهری عزیز به خاطر نکات تکمیلی تشکر میکنم.   

    •     با سلام و تشکر از مقاله بسیار خوبی که ارائه کردید

    • سلام

      بابت مطلب خوبی که ارائه دادید تشکر میکنم و یک سئوال از خدمت تون دارم.
      آیا استفاده از این مورد برای بانک های اطلاعاتی بزرگ با تعداد کاربران بالای ۵۰ کاربر، سربار اضافی هم برای سرور ایجاد میکنه؟(البته با توجه به اینکه jobهای دیگه ای هم از قبل برای بانک اطلاعاتی ایجاد کردیم).
    • ۱ – این موضوع بستگی به نحوه تعریف Job و… دارد. ضمنا فرآیند ارسال ایمیل یک پروسه جداگانه دارد که زیر نظر DB Engine می باشد. و به صورت Asynchronous می باشد. (استفاده از SSSB پشت قضیه)

      ۲- یکی از سازوکارهایی که می توان برای هشدار آنلاین برخی از مشکلات این تیپی می توان از ان استفاده کرد Alert است.
      ۳- یکی دیگر از سازو کارها استفاده از Event Notification است که بر پایه SSSB از آن استفاه می شود. البته باید کاری که می خواید انجام دهید را دقیق بررسی کنید تا Event و… آن پشتیبانی شود. 
      از هر دو این حالت ها مخصوصا مدل ۳ برای انجام برخی از کارهای بسیار حیاتی در دیتابیس استفاده کرده ام. 
      سناریو  خودم این بود اگر بنا به دلیلی Queue مربوظ به SSSB یکی از بانک ها Disable شود باید SMS ارسال شود. 
ثبت نام رایگان در همایش Tehran .NET Conf 2023 ، همین الان کلیک کنید
ثبت نام رایگان..
close-image