{"id":1843,"date":"2014-01-18T11:00:36","date_gmt":"2014-01-18T16:00:36","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1843"},"modified":"2014-11-13T13:44:00","modified_gmt":"2014-11-13T18:44:00","slug":"log-reuse-waits-explained-database_mirroring","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/","title":{"rendered":"Log Reuse Waits Explained: DATABASE_MIRRORING"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nThere are eight reasons SQL Server might report when it cannot truncate the transaction log. Any one of these reasons results in a growing log file. This short series is looking at each of them in detail, explaining what is causing it and what you can do to resolve it. Today's log reuse wait reason is: DATABASE_MIRRORING<\/span>\n<\/p>\n

Database Mirroring<\/h3>\n

\nDatabase mirroring is deprecated since SQL Server 2012<\/a>. So this wait type will go away at some point. But for now there are still quite a few installations out there. Therefore I decided to cover it anyway.\n<\/p>\n

\nDatabase mirroring allows you to have a second exact copy of your database on a different server. There are two operating modes: high performance and high-safety. In high-safety mode a transaction can only be committed if it has been applied to both databases. This can cause higher transaction latency but you will not lose any data if one of the two servers suddenly dies.\n<\/p>\n

\nIn high-performance mode on the other hand, transactions are committed on the primary server first and then transferred over to the secondary server. That reduces the latency impact on the primary server, but you might run the risk of some data loss if the secondary is behind at the time the primary server goes down.\n<\/p>\n

Waiting for the Secondary<\/h3>\n

\nIf you set database mirroring up in high-performance mode, a background process reads records for committed transaction from the transaction log and then sends enough information over to the secondary to repeat the action that transaction executed.\n<\/p>\n

\nIf the secondary falls behind, for example because of an interrupted network connection, the log reading process will wait until it can continue to transmit transaction information. During that time, transactions in the log that have not been processed by the mirroring agent cannot be purged and the virtual log files containing these records can't be reused. SQL Server will return a log_reuse_wait_desc<\/span> value of DATABASE MIRRORING<\/span> if it runs out of virtual log files because of this.\n<\/p>\n

\nTo solve this issue make sure the server housing the database mirror is adequately sized to keep up with the transaction load and the network connection between the two is stable. Also make sure there is enough room for the transaction log of the source database to grow in case of a mirror delay.\n<\/p>\n

Summary<\/h3>\n

\nSQL Server database mirroring is a deprecated high availability solution. It allows us to have copy of a database on a secondary server that is automatically kept in synch, either synchronously or asynchronously. In high-performance mode, the transaction log information of committed transactions is used to apply the changes asynchronously to the secondary. A log_reuse_wait_desc<\/span> value of DATABASE MIRRORING<\/span> indicates that this process is falling behind and can't process log records quickly enough.\n<\/p>\n

\nThe high-safety mode on the other hand applies all changes synchronously and therefore has no impact on transaction log reuse.\n<\/p>\n

Log Reuse Wait Series<\/h3>\n

\nThis post is part of a short series about circumstances that prevent transaction log truncation.
\nBelow is a list of links to the posts that are already available.\n<\/p>\n

\n
\n

Eight Reasons why your Transaction Log Files keep growing<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: CHECKPOINT<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: LOG_BACKUP<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: ACTIVE_BACKUP_OR_RESTORE<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: ACTIVE_TRANSACTION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: DATABASE_MIRRORING<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: REPLICATION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: DATABASE_SNAPSHOT_CREATION<\/a><\/h2>\n
<\/div>\n<\/div>\n
\n

Log Reuse Waits Explained: LOG_SCAN<\/a><\/h2>\n
<\/div>\n<\/div>\n<\/div>\n\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Is your database log file growing out of bound and reporting DATABASE_MIRRORING as reason? Read on to find out what that means and what you can do about it.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[5,75,61,27,62],"tags":[76,15,63,64],"class_list":["post-1843","post","type-post","status-publish","format-standard","hentry","category-general","category-high-availability","category-log-reuse-wait","category-series","category-transaction-log","tag-mirroring","tag-sql-server","tag-transaction-log-2","tag-transaction-log-reuse"],"acf":[],"yoast_head":"\nLog Reuse Waits Explained: DATABASE_MIRRORING - sqlity.net<\/title>\n<meta name=\"description\" content=\"Is your database log file growing out of bound and reporting DATABASE_MIRRORING as reason? Read on to find out what that means and what you can do about it.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Log Reuse Waits Explained: DATABASE_MIRRORING - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Is your database log file growing out of bound and reporting DATABASE_MIRRORING as reason? Read on to find out what that means and what you can do about it.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-18T16:00:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T18:44:00+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Log Reuse Waits Explained: DATABASE_MIRRORING\",\"datePublished\":\"2014-01-18T16:00:36+00:00\",\"dateModified\":\"2014-11-13T18:44:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/\"},\"wordCount\":568,\"commentCount\":1,\"keywords\":[\"mirroring\",\"SQL Server\",\"transaction log\",\"transaction log reuse\"],\"articleSection\":[\"General\",\"High Availability\",\"Log Reuse Wait\",\"Series\",\"Transaction Log\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/\",\"name\":\"Log Reuse Waits Explained: DATABASE_MIRRORING - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-01-18T16:00:36+00:00\",\"dateModified\":\"2014-11-13T18:44:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Is your database log file growing out of bound and reporting DATABASE_MIRRORING as reason? Read on to find out what that means and what you can do about it.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1843\\\/log-reuse-waits-explained-database_mirroring\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Log Reuse Waits Explained: DATABASE_MIRRORING\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Log Reuse Waits Explained: DATABASE_MIRRORING - sqlity.net","description":"Is your database log file growing out of bound and reporting DATABASE_MIRRORING as reason? Read on to find out what that means and what you can do about it.","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:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/","og_locale":"en_US","og_type":"article","og_title":"Log Reuse Waits Explained: DATABASE_MIRRORING - sqlity.net","og_description":"Is your database log file growing out of bound and reporting DATABASE_MIRRORING as reason? Read on to find out what that means and what you can do about it.","og_url":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-18T16:00:36+00:00","article_modified_time":"2014-11-13T18:44:00+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Log Reuse Waits Explained: DATABASE_MIRRORING","datePublished":"2014-01-18T16:00:36+00:00","dateModified":"2014-11-13T18:44:00+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/"},"wordCount":568,"commentCount":1,"keywords":["mirroring","SQL Server","transaction log","transaction log reuse"],"articleSection":["General","High Availability","Log Reuse Wait","Series","Transaction Log"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/","url":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/","name":"Log Reuse Waits Explained: DATABASE_MIRRORING - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-01-18T16:00:36+00:00","dateModified":"2014-11-13T18:44:00+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Is your database log file growing out of bound and reporting DATABASE_MIRRORING as reason? Read on to find out what that means and what you can do about it.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1843\/log-reuse-waits-explained-database_mirroring\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Log Reuse Waits Explained: DATABASE_MIRRORING"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-tJ","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1843","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1843"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1843\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1843"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}