nikamooz logo
nikamooz logo
0
سبد خرید
نحوه کارکرد Checkpoint در SQL Server

نحوه کارکرد Checkpoint در SQL Server

SQL Server دستورات SQL
نوشته شده توسط: تورج عزیزی
تاریخ انتشار: ۰۱ دی ۱۳۹۴
آخرین بروزرسانی: 02 اسفند 1403
زمان مطالعه: 6 دقیقه
۳
(۴)

در این مقاله قصد داریم در مورد Checkpoint در SQL Server و رکوردهای لاگ ناشی از انجام این عمل بحث کنیم. وقتی یک عمل checkpoint اتفاق می افتد، فارغ از اینکه چطور رخ میدهد؟ (برای مثال از طریق یک CHECKPOINT دستی، از یک full database backup یا differential backup یا به طور اتوماتیک).

عمل Checkpoint در SQL Server

• تمام صفحات داده ای dirty دیتابیس (صفحاتی که از زمانی که از دیسک خوانده شده اند یا از آخرین Checkpoint به بعد، در حافظه تغییر کرده اند) ، فارغ از وضعیت تراکنشی که تغییر را اعمال کرده (commit شده یا خیر) روی دیسک نوشته می شوند.
• قبل از اینکه Page روی دیسک نوشته شود، تمام رکوردهای لاگ تا آخرین رکورد لاگ که توصیفگر یک تغییر روی آن Page است روی دیسک نوشته می شوند (رکوردهای لاگ هم در حافظه cache می شوند). این فرآیند write-ahead logging نامیده می شود و فرآیند recovery را تضمین می کند. رکوردهای لاگ به شکل ترتیبی نوشته می شوند و رکوردهای لاگ چندین تراکنش  در فایل لاگ به هم آمیخته می شوند.
نوشتن لاگ روی دیسک نمی تواند به شکل گزینشی انجام شود. بنابراین نوشتن یک PageDirty که فقط یک رکورد لاگ تغییر را روی آن اعمال کرده می تواند به این معنی باشد که رکوردهای لاگ بیشتری که از قبل تولید شده اند ,و به تراکنش های دیگر مربوط می شوند هم روی دیسک نوشته خواهد شد.
• رکوردهای لاگ  توصیفگر Checkpoint تولید می شوند.
• LSN رکورد لاگ Checkpoint در boot page دیتابیس در فیلد dbi_checkptLSN  نوشته می شود.
• اگر در مدل احیای SIMPLE باشیم، VLF های موجود در log چک می شوند تا معلوم شود می توان آن را غیر فعال Markکرد. (که clear یا truncate لاگ نامیده می شود – که در هر دو علی رغم معنی نامشان چیزی به طور فیزیکی پاک نمی شود).

LSN آخرین Checkpoint در boot page دیتابیس ثبت می شود. این رکورد لاگ شروع recovery در فایل لاگ را تعیین می کند و اگر این page در دسترس نباشد، دیتابیس قابل attach، باز شدن یا پردازش به هیچ نحوی نخواهد بود- به این پیج، boot page گفته می شود از جهتی به این دلیل که تشخیص می دهد دیتابیس به شکل صحیح shut down شده یا خیر و دیگر اینکه boot page تنها جایی است که LSN آخرین رکورد Checkpointدر آن ثبت می شود. شما می‌توانید کوئری نویسی را به صورت گام‌به‌گام از نیک آموز فرا بگیرید. 
ممکن است بگوئید LSN آخرین رکورد Checkpoint در فایل لاگ دیتابیس هم ثبت می شود، اما اگر فایل لاگ به نحوی خراب شود چه؟ در این صورت تنها راه ممکن این است که  boot page تعمیر شود! رکوردهای لاگ checkponit هرگز توسط checkpoint های بعدی overwrite نمی شوند. یک رکورد لاگ update یا overwrite نمی شود –overwrite فقط وقتی رخ می دهد که SQL Server به انتهای فایل لاگ برسد و به ابتدای فایل لاگ چرخش کند (wrap-around) و تنها در این صورت است که از VLF های غیر فعال موجود دوباره استفاده می کند. 
در ادامه این پست به شما نشان خواهم داد هنگام اجرای Checkpoint تحت شرایط مختلف در transaction log چه اتفاقی می افتد. به مثال زیر توجه کنید:

CREATE DATABASE CheckpointTest;
GO
USE CheckpointTest;
GO
CREATE TABLE t1 (c1 INT);
GO
INSERT INTO t1 VALUES (1);
GO
CHECKPOINT;
GO
SELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);
GO
Current LSN Operation
———————– ——————————-
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۵۱:009b LOP_BEGIN_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۱:۰۰۰۱ LOP_END_CKPT

می توانیم در خروجی رکوردهای لاگ Checkpoint را ببینیم، در این مورد Checkpoint بسیار ساده است و فقط دو رکورد لاگ می‌بینیم شروع و پایان Checkpoint در SQL Server. اگر یک Checkpoint دیگر اجرا کنیم چه خواهیم دید؟

 CHECKPOINT;
GO
SELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);
GO
Current LSN Operation
———————– ——————————-
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۲:۰۰۰۱ LOP_BEGIN_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۳:۰۰۰۱ LOP_END_CKPT

اطلاعات مربوط به یک Checkpoint با LSN های متفاوت. این بدان معنی نیست که Checkpoint قبلی رونویسی شده بلکه به معنی آن است که ما یک Checkpoint اجرا کرده ایم. بنابراین بخش فعال لاگ به جلو منتقل شده و رکوردهای لاگ Checkpoint قبلی دیگر Active به حساب نمی آیند. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

حالا اگر یک تراکنش فعال ایجاد کنم چه؟ در یک connection دیگر این کار را انجام میدهم:

 USE CheckpointTest;
GO
BEGIN TRAN;
GO
INSERT INTO t1 VALUES (1);
GO

حالا اگر یک Checkpoint انجام داده و دوباره به log نگاه کنیم چه چیزی خواهیم دید؟

CHECKPOINT;
GO
SELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);
GO
Current LSN Operation
———————– ——————————-
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۱ LOP_BEGIN_XACT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۲ LOP_INSERT_ROWS
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۳ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۴ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۵ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۶ LOP_BEGIN_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۶:۰۰۰۱ LOP_XACT_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۶:۰۰۰۲ LOP_END_CKPT

ما شروع تراکنش باز را می بینیم، درج رکورد، به روزرسانی تعداد رکوردها در metadata و همینطور Checkpoint.

رکورد لاگ دیگری برای Checkpoint تولید شده – LOP_XACT_CKPT.
این رکورد لاگ فقط وقتی که تراکنش های فعال (uncommitted) وجود داشته باشد تولید می شوند و اطلاعاتی در مورد تراکنش های فعال در زمانی که Checkpoint شروع می شود را لیست می کند. پایگاه داده SQL Server از این رکورد لاگ در طول عملیات crash recovery برای دانستن اینکه چقدر در tranaction log باید به عقب برگردد تا عملیات REDO و UNDO را شروع کند استفاده می کند (و البته فقط UNDO نیاز به این عقبگرد خواهد داشت):

SELECT [Current LSN], [Operation], [Num Transactions], [Log Record]
FROM fn_dblog (NULL, NULL) WHERE [Operation] = ‘LOP_XACT_CKPT’;
GO
Current LSN Operation Num Transactions
———————– ——————————- —————-
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۶:۰۰۰۱ LOP_XACT_CKPT 1
Log Record
———————————————————————————————————–
0x000018 <snip> 780500000000000047000000940000000100040147000000940000000200000001 <snip> 6621000000000000

این رکورد لاگ حاوی اطلاعاتی راجع به هر تراکنش فعال (uncommitted) در زمان Checkpoint است. در مورد دو مقدار ۴۷۰۰۰۰۰۰۹۴۰۰۰۰۰۰۰۱۰۰۰ , ۴۷۰۰۰۰۰۰۹۴۰۰۰۰۰۰۰۲۰۰۰ دو چیز را می توانید ببینید:

• LSN رکورد لاگ LOP_BEGIN_XACTقدیمی ترین تراکنش فعال (۴۷۰۰۰۰۰۰۹۴۰۰۰۰۰۰۰۱۰۰۰ که می توانید آن را با LOP_BEGIN_XACT موجود در خروجی تطبیق دهید).
• LSN رکورد لاگ اول که برای آن تراکنش یک تغییر در دیتابیس اعمال کرده (۴۷۰۰۰۰۰۰۹۴۰۰۰۰۰۰۰۲۰۰۰ که می‌توانید آن را با LOP_INSERT_ROWS  موجود در خروجی تطبیق دهید).

مشاهده کامل‌ترین و بروزترین آموزش sql server در نیک آموز

اگر یک Checkpoint دیگر اجرا کنیم خروجی چطور خواهد بود؟

 CHECKPOINT;
GO
SELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);
GO
Current LSN Operation
———————– ——————————-
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۱ LOP_BEGIN_XACT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۲ LOP_INSERT_ROWS
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۳ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۴ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۵ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۶ LOP_BEGIN_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۶:۰۰۰۱ LOP_XACT_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۶:۰۰۰۲ LOP_END_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۷:۰۰۰۱ LOP_BEGIN_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۸:۰۰۰۱ LOP_XACT_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۸:۰۰۰۲ LOP_END_CKPT

این بار رکوردهای لاگ Checkpoint فعلی و قبلی را می بینیم، چون وقتی یک تراکنش فعال وجود دارد اهمیتی ندارد که چند Checkpoint اتفاق افتاده است. حالا اگر تراکنش فعال دیگری در یک Connection سوم شروع کنیم:

USE CheckpointTest;
GO
BEGIN TRAN;
GO
INSERT INTO t1 VALUES (2);
GO

و به Connection اصلی برگشته و یک Checkpoint دیگر انجام می دهیم و یکبار دیگر لاگ را می بینیم:

CHECKPOINT;
GO
SELECT [Current LSN], [Operation] FROM fn_dblog (NULL, NULL);
GO
Current LSN Operation
———————– ——————————-
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۱ LOP_BEGIN_XACT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۲ LOP_INSERT_ROWS
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۳ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۴ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۵ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۴:۰۰۰۶ LOP_BEGIN_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۶:۰۰۰۱ LOP_XACT_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۶:۰۰۰۲ LOP_END_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۷:۰۰۰۱ LOP_BEGIN_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۸:۰۰۰۱ LOP_XACT_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۸:۰۰۰۲ LOP_END_CKPT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۹:۰۰۰۱ LOP_BEGIN_XACT
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۹:۰۰۰۲ LOP_INSERT_ROWS
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۹:۰۰۰۳ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۹:۰۰۰۴ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۹:۰۰۰۵ LOP_COUNT_DELTA
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۹:۰۰۰۶ LOP_BEGIN_CKPT
۰۰۰۰۰۰۴۷:0000009b:0001 LOP_XACT_CKPT
۰۰۰۰۰۰۴۷:0000009b:0002 LOP_END_CKPT

همانطور که می بینید ما سه مجموعه رکورد لاگ Checkpoint داریم، و دو Connection فعال. همانطورکه به طور واضح می‌بینید رکوردهای لاگ رونویسی یا حذف نشده اند.

با نگاهی به همه رکوردهای می توانیم در خروجی ببینیم

 

SELECT [Current LSN], [Operation], [Num Transactions], [Log Record]
FROM fn_dblog (NULL, NULL) WHERE [Operation] = ‘LOP_XACT_CKPT’;
GO
Current LSN Operation Num Transactions
———————– ——————————- —————-
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۶:۰۰۰۱ LOP_XACT_CKPT 1
۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۸:۰۰۰۱ LOP_XACT_CKPT 1
۰۰۰۰۰۰۴۷:0000009b:0001 LOP_XACT_CKPT 2
Log Record
————————————————————————————————————-
0x000018 <snip> 780500000000000047000000940000000100040147000000940000000200000001 <snip> 21000000000000
0x000018 <snip> 780500000000000047000000940000000100040147000000940000000200000001 <snip> 21000000000000
0x000018 <snip> 780500000000000047000000940000000100040147000000940000000200000001 <snip> 21000000000000 …
… 79050000000000004700000099000000010004014700000099000000020000000100000002000000DC000000

دو Checkpoint اول فقط یک تراکنش فعال را لیست می کنند و آخری دو تراکنش، همانطوری که انتظارش را داشتیم. قسمت Payload دو رکورد لاگ اول یک تراکنش قدیمی باز مشابه را لیست می کند .قسمت Payload رکورد لاگ Checkpoint آخر هم همان تراکنش قدیمی باز را لیست می کند اما این رکورد یک تراکنش اضافی را هم لیست می کند و تعداد دو transaction  دارد.

و حالا اجازه دهید به boot page دیتابیس با استفاده از DBCC PAGE یا DBCC DBINFO نگاهی بیندازیم:

 DBCC TRACEON (3604);
GO
DBCC DBINFO (‘CheckpointTest’);
GO
DBINFO STRUCTURE:
DBINFO @0x6711EF64
dbi_dbid = 18 dbi_status = 65536 dbi_nextid = 2089058478
dbi_dbname = CheckpointTest dbi_maxDbTimestamp = 2000 dbi_version = 611
dbi_createVersion = 611 dbi_ESVersion = 0
dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2009-09-28 07:06:35.873
dbi_filegeneration = 0
dbi_checkptLSN =
m_fSeqNo = 71 m_blockOffset = 153 m_slotId = 6
dbi_RebuildLogs = 0 dbi_dbccFlags = 2
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000
<snip>

سخن پایانی

Checkpoint در SQL Server، مقدار dbi_checkptLSN  به شکل دسیمال نمایش داده شده (m_fSeqNo = ۷۱ m_blockOffset = ۱۵۳ m_slotId = ۶) – که تبدیل آن به هگز مقدار(۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹۹:۰۰۰۶) را به ما می دهد، که با LSN آخرین رکورد LOP_BEGIN_CKPT در بالا مطابقت دارد. ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.

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

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

اولین نفر باش

title sign
دانلود مقاله
نحوه کارکرد Checkpoint در SQL Server
فرمت PDF
6 صفحه
حجم 1 مگابایت
دانلود مقاله
وبینار رایگان نقشه گنج شغل های اینترنتی
آموزش مقدماتی ASP . NET Core
title sign
معرفی نویسنده
تورج عزیزی
مقالات
18 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
تورج عزیزی
title sign
معرفی محصول
ایمان باقری

آموزش کوئری نویسی در sql server
2.190.000 تومان
title sign
مقالات مرتبط
ابزار Database Engine Tuning Advisor
۰۲ آبان
SQL Server

ابزار Database Engine Tuning Advisor
تیم فنی نیک آموز
معرفی Performance Monitor ابزار مانیتورینگ SQL Server
۱۵ مهر
SQL Server

معرفی Performance Monitor ابزار مانیتورینگ SQL Server
تیم فنی نیک آموز
راهنمای جامع مانیتورینگ بکاپ ها در SQL Server
۱۱ مهر
SQL Server

راهنمای جامع مانیتورینگ بکاپ ها در SQL Server
تیم فنی نیک آموز
Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن
۰۸ مهر
SQL Server

Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن
تیم فنی نیک آموز
title sign
دیدگاه کاربران

    • سلام لطفاً در مورد LSN، VLF و علائم اختصاری که در این مقاله به کار برده اید توضیح دهید.
      با تشکر

      • Virtual Log File
        داخل لاگ فایل به تعدادی VLF تقسیم می شود
        Log Sequence Number
        هر لاگی که در لاگ فایل ثبت می شود برای خود آی دی دارد که به آن Log Sequence Number می گویند

    • سلام لطفاً در مورد LSN، VLF و علائم اختصاری که در این مقاله به کار برده اید توضیح دهید.
      با تشکر

      • Virtual Log File
        داخل لاگ فایل به تعدادی VLF تقسیم می شود

        Log Sequence Number
        هر لاگی که در لاگ فایل ثبت می شود برای خود آی دی دارد که به آن Log Sequence Number می گویند

    • نمیدونم چراهرچی مطلب درباره لاگ فایل و چک پوینت و ریکاوری لاگ فایل میخونم،،نمیفهمم

    • سلام

      بسیار عالی بود
      سپاسگزارم

    •     ممنون بابت مقاله خوبتون منم با نظر دوست عزیز جهت تبدیل مقالات به pdf   موافقم چون استفاده کارامد تر خواهد شد باتشکر

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

    •    سلام مهندس
      مقاله ای جالب و پر کاربرد بود فقط انسان باید چند مرتبه بخوونه تا مطالب جا بیفته

    • سلام، مقالتون خوب بود، ممنون

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

    •     تورج جان عالی بود

  • 1
  • 2
وبینار رایگان ورود به دنیای برنامه‌نویسی وب با ASP. NET Core   [کلیک کن]
close-image

دانلود رایگان: آموزش SQL Server

هر روز یک ویدئو آموزشی رایگان برای شما ایمیل خواهد شد!

پاپ آپ | SQL Server

  • این قسمت برای اهداف اعتبارسنجی است و باید بدون تغییر باقی بماند.