{"id":2812,"date":"2015-11-12T09:53:23","date_gmt":"2015-11-12T14:53:23","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=2812"},"modified":"2023-10-17T05:38:52","modified_gmt":"2023-10-17T09:38:52","slug":"checkpoint","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/checkpoint\/","title":{"rendered":"Checkpoint"},"content":{"rendered":"

Checkpoint is a process that writes current in-memory dirty pages (modified pages) and transaction log records to physical disk. In SQL Server checkpoints are used to reduce the time required for recovery in the event of\u00a0system failure. Checkpoint is regularly issued for each database. The following set of operations starts when checkpoint occurs:<\/p>\n

    \n
  1. Log records from log buffer (including the last log record) are written to the disk.<\/li>\n
  2. All dirty data file pages (pages that have been modified since the last checkpoint or since they were read from disk) are written into the data file from the buffer cache.<\/li>\n
  3. Checkpoint\u00a0LSN is recorded in the database boot page.<\/li>\n<\/ol>\n

    \"Checkpoint<\/p>\n

    Types of Checkpoint<\/h2>\n

    The Database Engine supports four types of checkpoints. While some of them\u00a0are issued automatically in the background, other are triggered by user and some of them are triggered by certain system events.<\/p>\n

    Automatic Checkpoint<\/h3>\n

    An\u00a0<\/strong>automatic\u00a0checkpoint is the most common type that\u00a0is triggered by a background process. Server Configuration Option “Recovery Interval” is used by the SQL Server Database Engine to determine how often automatic checkpoints are issued on a given database. You can change it using sp_configure procedure. For example, execute the following command to set the recovery interval to 15 seconds:<\/p>\n

    EXEC<\/span> [sp_configure] 'recovery interval'<\/span>, 15\r\nGO<\/span>\r\nRECONFIGURE<\/span>\r\nGO<\/span><\/span><\/pre>\n
    In the simple recovery model an automatic checkpoint truncates the unused section of the transaction log. As far as full or bulk-logged recovery model is concerned, the transaction log is not truncated by automatic checkpoint.<\/p>\n
    Indirect\u00a0Checkpoint<\/h3>\n
    A new type of checkpoint introduced in SQL Server 2012 is an\u00a0<\/strong>Indirect checkpoint. Indirect checkpoint\u00a0also runs in the background, but it\u00a0meets user-specified target recovery time for a given database. By default TARGET_RECOVERY_TIME is 0, meaning\u00a0that the database will use automatic checkpoints. If TARGET_RECOVERY_TIME is set to >0, it will override the Recovery Interval specified for the server and avoid automatic checkpoints for that database.<\/p>\n
    Use the following command to set the target recovery time for a database:<\/p>\n
    ALTER DATABASE<\/span> database_name<\/em> SET<\/span> TARGET_RECOVERY_TIME = target_recovery_time<\/em> { SECONDS | MINUTES}<\/pre>\n
    Manual\u00a0Checkpoint<\/h3>\n
    Manual checkpoint runs like any other Transact-SQL command. It runs to completion by default. This type of checkpoint occurs in the current database only. It is also possible to set the time frame\u00a0in which you want your checkpoint completed. Use the following command to issue manual checkpoint:<\/p>\n
    CHECKPOINT [ checkpoint_duration (in seconds)<\/em> ]<\/span><\/span><\/pre>\n
    Internal\u00a0Checkpoint<\/h3>\n
    The fourth\u00a0type is I<\/strong>nternal<\/strong> checkpoint that cannot be controlled by user. It starts following specific transactions, such as:<\/p>\n
      \n
    • Some database files have been modified\u00a0(removed or added by T-SQL command ALTER DATABASE)<\/li>\n
    • Database backup is in progress<\/li>\n
    • Database snapshot is being created<\/li>\n
    • Shutdown operation occurred on all databases except when Shutdown is not clean (with NOWAIT)<\/li>\n
    • Recovery model has been changed\u00a0from Full or Bulk-Logged to Simple<\/li>\n
    • Database log is\u00a070% full (applies only to Simple recovery model)<\/li>\n
    • Minimally logged operation executed (applies only to Bulk-Logged recovery model)<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
      Checkpoint is a process that writes current in-memory dirty pages (modified pages) and transaction log records to physical disk. In SQL Server checkpoints are used to reduce the time required for recovery in the event of\u00a0system failure. Checkpoint is regularly issued for each database. The following set of operations starts when checkpoint occurs: Log records […]<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[157],"tags":[],"yoast_head":"\nCheckpoint - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server checkpoint\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/academy.sqlbak.com\/checkpoint\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Checkpoint - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server checkpoint\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/checkpoint\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-11-12T14:53:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:38:52+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Checkpoint-1.png\" \/>\n<meta name=\"author\" content=\"Alexandr Omelchenko\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alexandr Omelchenko\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/checkpoint\/\",\"url\":\"https:\/\/academy.sqlbak.com\/checkpoint\/\",\"name\":\"Checkpoint - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-11-12T14:53:23+00:00\",\"dateModified\":\"2023-10-17T09:38:52+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server checkpoint\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/checkpoint\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/checkpoint\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/checkpoint\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Checkpoint\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/academy.sqlbak.com\/#website\",\"url\":\"https:\/\/academy.sqlbak.com\/\",\"name\":\"Sql Server Backup Academy\",\"description\":\"All you need to know about Sql Server database backup\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/academy.sqlbak.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\",\"name\":\"Alexandr Omelchenko\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"caption\":\"Alexandr Omelchenko\"},\"sameAs\":[\"http:\/\/sqlbak.com\"],\"url\":\"https:\/\/academy.sqlbak.com\/author\/alexandr\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Checkpoint - Sql Server Backup Academy","description":"Learn about SQL Server checkpoint","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/academy.sqlbak.com\/checkpoint\/","og_locale":"en_US","og_type":"article","og_title":"Checkpoint - Sql Server Backup Academy","og_description":"Learn about SQL Server checkpoint","og_url":"https:\/\/academy.sqlbak.com\/checkpoint\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-11-12T14:53:23+00:00","article_modified_time":"2023-10-17T09:38:52+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Checkpoint-1.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/checkpoint\/","url":"https:\/\/academy.sqlbak.com\/checkpoint\/","name":"Checkpoint - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-11-12T14:53:23+00:00","dateModified":"2023-10-17T09:38:52+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server checkpoint","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/checkpoint\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/checkpoint\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/checkpoint\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Checkpoint"}]},{"@type":"WebSite","@id":"https:\/\/academy.sqlbak.com\/#website","url":"https:\/\/academy.sqlbak.com\/","name":"Sql Server Backup Academy","description":"All you need to know about Sql Server database backup","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/academy.sqlbak.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8","name":"Alexandr Omelchenko","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","caption":"Alexandr Omelchenko"},"sameAs":["http:\/\/sqlbak.com"],"url":"https:\/\/academy.sqlbak.com\/author\/alexandr\/"}]}},"_links":{"self":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2812"}],"collection":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/comments?post=2812"}],"version-history":[{"count":21,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2812\/revisions"}],"predecessor-version":[{"id":3878,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2812\/revisions\/3878"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=2812"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=2812"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=2812"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}