{"id":2829,"date":"2015-11-30T05:40:58","date_gmt":"2015-11-30T10:40:58","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=2829"},"modified":"2023-10-17T05:31:05","modified_gmt":"2023-10-17T09:31:05","slug":"transaction-log","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/transaction-log\/","title":{"rendered":"Transaction Log"},"content":{"rendered":"

The transaction log is a crucial part of each database that records all transactions and database changes made by each transaction. The purpose of the transaction log is to keep record of all changes that were made in the database.\u00a0Let’s consider the example from the picture below:\"Transaction<\/p>\n

    \n
  1. The user makes\u00a0some changes in the database.<\/li>\n
  2. This query is executed and this operation is immediately recorded\u00a0in the transaction log.<\/li>\n
  3. When the record is saved, the query returns to the user. But what about the record in the data file?<\/li>\n
  4. Data file is later updated during a\u00a0checkpoint. It works this\u00a0way because the data file may need to expand to accommodate the changes.<\/li>\n<\/ol>\n

    One more thing that has to be mentioned is that a database can have one or more transaction log files. Using two or\u00a0more transaction logs will not make your database work faster, because writing can occur\u00a0only into one file at a time, meaning that parallel I\/O operations are not possible. The scenario under which\u00a0multiple transaction log files are recommended is when the first one can not grow anymore.<\/p>\n

    Virtual Log Files<\/h2>\n

    The transaction log stores every transaction made in a database, except those that are minimally logged (such as SELECT INTO or BULK IMPORT). Each transaction log record has its own unique number, called the Log Sequence Number (LSN), and is stored in the transaction log’s Virtual Log File (VLF), whose size is not fixed. A transaction log can have any number of virtual log files:\"Transaction<\/p>\n

    Transaction Log Truncation<\/h2>\n

    The log file will grow until it fills all free space on the disk where it is located unless log records are removed. As time passes, old log records become unnecessary for the recovery process, so they must be deleted to free space for new log records. Such a process in SQL Server where all unnecessary records are removed to reduce the size of the log file is called transaction log truncation<\/em>.<\/p>\n

    The way\u00a0this truncation happens is dependent on the recovery model that was selected for\u00a0your database.<\/p>\n

    Simple Recovery Model<\/h3>\n

    The transaction log is truncated when\u00a0checkpoint occurs. At that time, all committed transactions are written to the data file\u00a0and virtual log files can be reused. Look at the picture below:\u00a0\"Transaction<\/p>\n

    After the checkpoint occurs, virtual log files 1 and 2 are no longer in use because transactions 11 and 12 have been committed. SQL Server marks virtual log files 1 and 2 as reusable. Such a process is known as truncation of the transaction log. All committed transactions have been truncated, but the physical size of the transaction log remains the same. If there is a need to make the transaction log file smaller, turn AUTO_SHRINK option on and it will physically shrink the log file (where possible) at periodic intervals:<\/p>\n

    ALTER DATABASE<\/span> your_database<\/em> SET<\/span> AUTO_SHRINK ON\r\n<\/span>ALTER DATABASE<\/span> your_database<\/em> SET AUTO_SHRINK OFF<\/span><\/pre>\n
    To set up auto shrink using SSMS, right-click on the database for which you want to set up transaction log auto shrink, select \u201cProperties\u201d, then \u201cOptions\u201d. Choose \u201cAuto Shrink\u201d and switch from “False” to “True”, and then click \u201cOK\u201d.<\/p>\n
    \"Transaction<\/p>\n
    So after the transaction has been committed, SQL Server can reuse the space. But be careful with this option as there is no way to control when auto shrink starts. Also, such operations where the file grows then shrinks, then again grows, and again shrinks, take more resources.<\/p>\n
    Full or Bulk-Logged Recovery Models<\/h3>\n
    In this case, the transaction log is truncated only during the transaction log backup. In the full or bulk-logged recovery model when the checkpoint occurs, all dirty pages are written to the disk, but SQL Server will not mark any of the virtual log files as reusable. The transaction log will be truncated only after the transaction log backup is completed:<\/p>\n
    BACKUP<\/span> LOG<\/span> your_database <\/em>TO DISK<\/span> = 'log.bak'<\/span><\/pre>\n
    \"Trunating<\/p>\n
    Remember, if the database is running under the full or bulk-logged recovery model and the full backup has been never made, the transaction log will be truncated at each checkpoint.<\/p>\n
    Maintenance of the Transaction Log<\/h2>\n
    As mentioned above, the transaction log is automatically truncated under the simple recovery model, but how can you manage the transaction log if the database uses a full or bulk-logged recovery model? This is achieved through regular transaction log backups.<\/p>\n
    Moreover, it is recommended to monitor the transaction log space. Use the following command:<\/p>\n
    DBCC<\/span> SQLPERF(LOGSPACE)<\/pre>\n
    That returns the following table:<\/p>\n
    \"DBCC<\/p>\n
      \n
    • Database Name<\/strong> – name of the database<\/li>\n
    • Log Size (MB)<\/strong> – current size of transaction log<\/li>\n
    • Log Space Used (%)<\/strong> – shows how the percentage occupied with transaction log information in the log file<\/li>\n
    • Status<\/strong> – log file status (always ‘0’)<\/li>\n<\/ul>\n
      Another\u00a0useful command that can help to find out how many virtual logs are in the transaction log file\u00a0that is being currently used:<\/p>\n
      DBCC<\/span> LOGINFO<\/pre>\n
      \"DBCC<\/p>\n
        \n
      • FileId<\/strong> – shows in which physical file the VLF is stored.<\/li>\n
      • FileSize<\/strong> – size of the transaction log file\u00a0(in bytes).<\/li>\n
      • StartOffset<\/strong> – \u00a0used as the sort column for the output. Note that the first VLF is always 8,192 bytes.<\/li>\n
      • FSeqNo (File Sequence Number)<\/strong> – indicates the order of usage of the VLF. The VLF with the highest FSeqNo number is the VLF where current log records are being written.<\/li>\n
      • Status<\/strong> – there are two possible values: 0 and 2. VLF with 0 indicates that it can be reused, the VLF with value 2 indicates that it is not reusable.<\/li>\n
      • Parity<\/strong> – has two values: 64 and 128. It switches every time when a VLF is reused.<\/li>\n
      • CreateLSN<\/strong> – indicates when the VLF was created. If the value is 0 it means that VLF was\u00a0created when the database was created. If the\u00a0VLFs have the same value it means that they were created at the same time.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
        The transaction log is a crucial part of each database that records all transactions and database changes made by each transaction. The purpose of the transaction log is to keep record of all changes that were made in the database.\u00a0Let’s consider the example from the picture below:<\/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":"\nTransaction Log - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Transaction Log\" \/>\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\/transaction-log\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Transaction Log - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Transaction Log\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/transaction-log\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-11-30T10:40:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:31:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Transaction-Log-Record-2-648x350.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=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/transaction-log\/\",\"url\":\"https:\/\/academy.sqlbak.com\/transaction-log\/\",\"name\":\"Transaction Log - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-11-30T10:40:58+00:00\",\"dateModified\":\"2023-10-17T09:31:05+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Transaction Log\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/transaction-log\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/transaction-log\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/transaction-log\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Transaction Log\"}]},{\"@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":"Transaction Log - Sql Server Backup Academy","description":"Learn about SQL Server Transaction Log","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\/transaction-log\/","og_locale":"en_US","og_type":"article","og_title":"Transaction Log - Sql Server Backup Academy","og_description":"Learn about SQL Server Transaction Log","og_url":"https:\/\/academy.sqlbak.com\/transaction-log\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-11-30T10:40:58+00:00","article_modified_time":"2023-10-17T09:31:05+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Transaction-Log-Record-2-648x350.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/transaction-log\/","url":"https:\/\/academy.sqlbak.com\/transaction-log\/","name":"Transaction Log - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-11-30T10:40:58+00:00","dateModified":"2023-10-17T09:31:05+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Transaction Log","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/transaction-log\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/transaction-log\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/transaction-log\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Transaction Log"}]},{"@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\/2829"}],"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=2829"}],"version-history":[{"count":33,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2829\/revisions"}],"predecessor-version":[{"id":3873,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2829\/revisions\/3873"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=2829"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=2829"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=2829"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}