Checkpoint چطور کار می کند و چه چیزی log می شود؟

SQL Server دستورات SQL

نوشته شده توسط: تورج عزیزی

تاریخ انتشار: ۰۱ دی ۱۳۹۴

آخرین بروزرسانی: ۱۹ مهر ۱۴۰۲

زمان مطالعه: 15 دقیقه

مقدمه

سلام دوستان

در این مقاله قصد دارم در مورد عمل Checkpoint و رکوردهای لاگ ناشی از انجام این عمل بحث کنم.

وقتی یک عمل checkpoint اتفاق می افتد، فارغ از اینکه چطور رخ میدهد (برای مثال از طریق یک CHECKPOINT دستی، از یک full database backup یا differential backup یا به طور اتوماتیک)

مجموعه ای از عملیات انجام میشود

• تمام صفحات داده ای 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.

اگر یک 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 به حساب نمی آیند.

حالا اگر یک تراکنش فعال ایجاد کنم چه؟ در یک 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 موجود در خروجی تطبیق دهید)

اگر یک 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>

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

برای بدست آوردن اطلاعات بیش‌تر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید.