{"id":698,"date":"2015-08-26T12:32:21","date_gmt":"2015-08-26T19:32:21","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=698"},"modified":"2017-04-13T09:19:27","modified_gmt":"2017-04-13T16:19:27","slug":"auto_close-and-the-sql-server-errorlog","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/","title":{"rendered":"AUTO_CLOSE and the SQL Server ERRORLOG"},"content":{"rendered":"<p>Today I opened up a SQL Server ERRORLOG and saw these two messages repeated every 20 seconds or so:<\/p>\n<p style=\"text-align: left; padding-left: 30px;\">Starting up database &#8216;AdventureWorks2014&#8217;.<\/p>\n<p style=\"text-align: left; padding-left: 30px;\">CHECKDB for database &#8216;AdventureWorks2014&#8217; finished without errors on 2015-08-23 02:15:08.070 (local time).\u00a0 This is an information message only; no user action required.<\/p>\n<p>When you initially see these two messages repeated over and over, it might seem like SQL Server is caught in some issue with recovery.\u00a0 Or you might think it&#8217;s running CHECKDB over and over.\u00a0 Neither are true.\u00a0 The database has AUTO_CLOSE enabled.\u00a0 (And you see the CHECKDB message because it&#8217;s reading the boot page and noting the last time CHECKDB ran successfully&#8230;to see what updates that entry, check out my post <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/what-checks-update-dbcclastknowngood\/\">What DBCC Checks Update dbccLastKnownGood?<\/a>)<\/p>\n<p>When AUTO_CLOSE is enabled, after the last user exits the database, the database shuts down and its resources are freed.\u00a0 When someone tries to access the database again, the database reopens.\u00a0 You might be thinking that for databases that are not accessed that often, this might be a good thing.\u00a0 After all, freeing resources and giving them back to SQL Server for use elsewhere sounds useful.\u00a0 Not so much.\u00a0 There&#8217;s a cost associated with that shut down, and a cost to open the database back up when a user connects.\u00a0 For example &#8211; shutting down a database removes all plans for that database from cache.\u00a0 The next time a user runs a query, it will have to be compiled.\u00a0 If the user disconnects, the plan is freed from cache.\u00a0 If someone connects one minute later and runs the same query, it has be compiled again.\u00a0 You get the point: this is inefficient.\u00a0 And really, how many databases in your production environment do you really <em>not<\/em> access?\u00a0 If you&#8217;re not accessing the database, why is it in a production instance?\u00a0 If you want a few more details on AUTO_CLOSE, check out the entry for <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-transact-sql-set-options\">ALTER DATABASE<\/a> in Books Online.<\/p>\n<p>I am sure (maybe?) that there are valid cases for having AUTO_CLOSE enabled.\u00a0 But I haven&#8217;t found one yet \ud83d\ude42<\/p>\n<p>On top of the resource use, realize that every time the database starts up, you&#8217;re going to get the above two messages in the ERRORLOG.\u00a0 In the log I was looking at, there were multiple databases with this option enabled, so the log was <em>flooded<\/em> with these messages.\u00a0 In general, I&#8217;m a huge fan of cycling the ERRORLOG on a regular basis (just set up an Agent job that runs <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-cycle-errorlog-transact-sql\">sp_cycle_errorlog<\/a> every week), and I try to reduce &#8220;clutter&#8221; in the log as much as possible.\u00a0 This means don&#8217;t enable a setting like AUTO_CLOSE which can put in all those messages, and use<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/database-console-commands\/dbcc-traceon-trace-flags-transact-sql\"> trace flag 3226<\/a> to stop logging successful backup messages (they still go to msdb).<\/p>\n<p>Oh yes, to disable AUTO_CLOSE:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">ALTER DATABASE 'AdventureWorks2014' SET AUTO_CLOSE OFF;\r\n\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I opened up a SQL Server ERRORLOG and saw these two messages repeated every 20 seconds or so: Starting up database &#8216;AdventureWorks2014&#8217;. CHECKDB for database &#8216;AdventureWorks2014&#8217; finished without errors on 2015-08-23 02:15:08.070 (local time).\u00a0 This is an information message only; no user action required. When you initially see these two messages repeated over and [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>AUTO_CLOSE and the SQL Server ERRORLOG - Erin Stellato<\/title>\n<meta name=\"description\" content=\"If you&#039;re seeing &quot;Starting up database...&quot; in the ERRORLOG repeatedly, check to see if AUTO_CLOSE is enabled.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"AUTO_CLOSE and the SQL Server ERRORLOG - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"If you&#039;re seeing &quot;Starting up database...&quot; in the ERRORLOG repeatedly, check to see if AUTO_CLOSE is enabled.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2015-08-26T19:32:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:27+00:00\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\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:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/\",\"name\":\"AUTO_CLOSE and the SQL Server ERRORLOG - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2015-08-26T19:32:21+00:00\",\"dateModified\":\"2017-04-13T16:19:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"If you're seeing \\\"Starting up database...\\\" in the ERRORLOG repeatedly, check to see if AUTO_CLOSE is enabled.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"AUTO_CLOSE and the SQL Server ERRORLOG\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"AUTO_CLOSE and the SQL Server ERRORLOG - Erin Stellato","description":"If you're seeing \"Starting up database...\" in the ERRORLOG repeatedly, check to see if AUTO_CLOSE is enabled.","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:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/","og_locale":"en_US","og_type":"article","og_title":"AUTO_CLOSE and the SQL Server ERRORLOG - Erin Stellato","og_description":"If you're seeing \"Starting up database...\" in the ERRORLOG repeatedly, check to see if AUTO_CLOSE is enabled.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/","og_site_name":"Erin Stellato","article_published_time":"2015-08-26T19:32:21+00:00","article_modified_time":"2017-04-13T16:19:27+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/","name":"AUTO_CLOSE and the SQL Server ERRORLOG - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2015-08-26T19:32:21+00:00","dateModified":"2017-04-13T16:19:27+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"If you're seeing \"Starting up database...\" in the ERRORLOG repeatedly, check to see if AUTO_CLOSE is enabled.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/auto_close-and-the-sql-server-errorlog\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"AUTO_CLOSE and the SQL Server ERRORLOG"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/698"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=698"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/698\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=698"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=698"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=698"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}