خانه SQL Server 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 ———————– ——————————- ۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۵۱:۰۰۹b 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 ۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹b:0001 LOP_XACT_CKPT ۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹b: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 ۰۰۰۰۰۰۴۷:۰۰۰۰۰۰۹b:0001 LOP_XACT_CKPT 2 Log Record ————————————————————————————————————- 0x000018 <snip> 780500000000000047000000940000000100040147000000940000000200000001 <snip> 21000000000000 0x000018 <snip> 780500000000000047000000940000000100040147000000940000000200000001 <snip> 21000000000000 0x000018 <snip> 780500000000000047000000940000000100040147000000940000000200000001 <snip> 21000000000000 … … ۷۹۰۵۰۰۰۰۰۰۰۰۰۰۰۰۴۷۰۰۰۰۰۰۹۹۰۰۰۰۰۰۰۱۰۰۰۴۰۱۴۷۰۰۰۰۰۰۹۹۰۰۰۰۰۰۰۲۰۰۰۰۰۰۰۱۰۰۰۰۰۰۰۲۰۰۰۰۰۰DC000000 دو 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 ، به مقاله زیر مراجعه کنید. چه رتبه ای میدهید؟ میانگین ۳.۷ / ۵. از مجموع ۳ اولین نفر باش دانلود مقاله Checkpoint چطور کار می کند و چه چیزی log می شود؟ فرمت PDF 6 صفحه حجم 1 مگابایت دانلود مقاله معرفی نویسنده مقالات 18 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده تورج عزیزی معرفی محصول ایمان باقری دوره آموزشی کوئری نویسی در SQL Server 2.190.000 تومان مقالات مرتبط ۰۲ آبان SQL Server ابزار Database Engine Tuning Advisor؛ مزایا، کاربردها و روش استفاده تیم فنی نیک آموز ۱۵ مهر SQL Server معرفی Performance Monitor ابزار مانیتورینگ SQL Server تیم فنی نیک آموز ۱۱ مهر SQL Server راهنمای جامع مانیتورینگ بکاپ ها در SQL Server تیم فنی نیک آموز ۰۸ مهر SQL Server Resource Governor چیست؟ آشنایی با نحوه پیکربندی و اهمیت های آن تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ پویان بهروزفر ۲۵ / ۱۱ / ۹۷ - ۰۷:۵۵ سلام لطفاً در مورد LSN، VLF و علائم اختصاری که در این مقاله به کار برده اید توضیح دهید. با تشکر پاسخ به دیدگاه مسعود طاهری ۲۷ / ۱۱ / ۹۷ - ۰۴:۳۸ Virtual Log File داخل لاگ فایل به تعدادی VLF تقسیم می شود Log Sequence Number هر لاگی که در لاگ فایل ثبت می شود برای خود آی دی دارد که به آن Log Sequence Number می گویند پاسخ به دیدگاه پویان بهروزفر ۲۵ / ۱۱ / ۹۷ - ۰۷:۵۵ سلام لطفاً در مورد LSN، VLF و علائم اختصاری که در این مقاله به کار برده اید توضیح دهید. با تشکر پاسخ به دیدگاه مسعود طاهری ۲۷ / ۱۱ / ۹۷ - ۰۴:۳۸ Virtual Log File داخل لاگ فایل به تعدادی VLF تقسیم می شود Log Sequence Number هر لاگی که در لاگ فایل ثبت می شود برای خود آی دی دارد که به آن Log Sequence Number می گویند پاسخ به دیدگاه ho.mehdi@gmail.com ۱۹ / ۰۹ / ۹۵ - ۰۵:۲۴ نمیدونم چراهرچی مطلب درباره لاگ فایل و چک پوینت و ریکاوری لاگ فایل میخونم،،نمیفهمم پاسخ به دیدگاه محسن صیادیان ۲۹ / ۰۱ / ۹۵ - ۱۰:۴۰ سلام بسیار عالی بود سپاسگزارم پاسخ به دیدگاه عاطفه حسن پور ۰۱ / ۱۱ / ۹۴ - ۰۷:۰۶ ممنون بابت مقاله خوبتون منم با نظر دوست عزیز جهت تبدیل مقالات به pdf موافقم چون استفاده کارامد تر خواهد شد باتشکر پاسخ به دیدگاه محمد اصلاحی ۱۹ / ۱۰ / ۹۴ - ۰۱:۵۱ اگر امکان داره امکان تبدل مقاله به pdf و سپس ذخیره سازی خودکار اون رو فراهم کنید.چون اینطوری امکان استفاده مفید تر از مطالب خوبتون بیشتر هست.تشکر پاسخ به دیدگاه سعید شیرزادیان ۰۸ / ۱۰ / ۹۴ - ۱۲:۱۲ سلام مهندس مقاله ای جالب و پر کاربرد بود فقط انسان باید چند مرتبه بخوونه تا مطالب جا بیفته پاسخ به دیدگاه نرگس ۰۳ / ۱۰ / ۹۴ - ۰۱:۳۲ سلام، مقالتون خوب بود، ممنون پاسخ به دیدگاه مهدی ربانی ذبیحی ۰۳ / ۱۰ / ۹۴ - ۰۶:۱۷ سلام مقاله بسیار خوب و عالی بود با تشکر از شما بابت مقاله های خوبی که در سایت نیک اموز قرار می دهید پاسخ به دیدگاه مسعود طاهری ۰۲ / ۱۰ / ۹۴ - ۱۱:۰۷ تورج جان عالی بود پاسخ به دیدگاه 1 2