{"id":4233,"date":"2013-11-04T16:09:37","date_gmt":"2013-11-05T00:09:37","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4233"},"modified":"2017-05-20T07:56:50","modified_gmt":"2017-05-20T14:56:50","slug":"limiting-error-log-file-size-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/","title":{"rendered":"Limiting error log file size in SQL Server 2012"},"content":{"rendered":"<p style=\"text-align: justify;\">It&#8217;s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running <em>sp_cycle_errorlog<\/em> every day at midnight (see <a href=\"http:\/\/sqlmag.com\/blog\/how-prevent-enormous-sql-server-error-log-files\" target=\"_blank\" rel=\"noopener noreferrer\">this post<\/a> on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server.<\/p>\n<p style=\"text-align: justify;\">One thing that hasn&#8217;t been possible before is setting the maximum size of individual SQL Server error logs before SQL Server triggers cycling to a new error log. Well it turns out that in SQL Server 2012 you can!<\/p>\n<p style=\"text-align: justify;\">While in my post-con workshop at SQL Intersection in Las Vegas last week,\u00a0Jan K\u00e5re Lokna (a former Immersion Event attendee from Norway) discussed some code he&#8217;s been experimenting with and I just heard from him that he got it to work.<\/p>\n<p>The following code will set the maximum size of each error log file to 5MB on a SQL Server 2012 instance:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUSE &#x5B;master];\r\nGO\r\n\r\nEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',\r\n    N'Software\\Microsoft\\MSSQLServer\\MSSQLServer',\r\n    N'ErrorLogSizeInKb', REG_DWORD, 5120;\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">I&#8217;ve tested this on SQL Server 2008 R2 and it does not work there, so the registry key must be new for SQL Server 2012. This is really useful to protect against gigantic error log files caused by repeated crash dumps, for instance when messing around with <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/dbcc-writepage\/\" target=\"_blank\" rel=\"noopener noreferrer\"><em>DBCC WRITEPAGE<\/em><\/a> :-)<\/p>\n<p>[Edit: 10\/23\/15] Also, for SQL Server 2014 the registry keys have changed again:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUSE &#x5B;master];\r\nGO\r\n-- Limit size of each file\r\nEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',\r\nN'SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQLServer',\r\nN'ErrorLogSizeInKb', REG_DWORD, 1024;\r\nGO\r\n\r\n-- Number of ErrorLog Files\r\nEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',\r\nN'SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL12.MSSQLSERVER\\MSSQLServer',\r\nN'NumErrorLogs', REG_DWORD, 8;\r\nGO\r\n<\/pre>\n<p style=\"text-align: justify;\">Note: I&#8217;ve had anecdotal reports that on SQL Server 2014, the SQL Server 2012 method is the only one that works. Please experiment and see which works in your situation. I imagine the difference may be due to fresh-install vs. upgraded-in-place.<\/p>\n<p>You can read a more in-depth description on Jan&#8217;s blog <a href=\"http:\/\/lokna.no\/?p=1473\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>It&#8217;s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server. One thing [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[95],"tags":[],"class_list":["post-4233","post","type-post","status-publish","format-standard","hentry","category-tools"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Limiting error log file size in SQL Server 2012 - Paul S. Randal<\/title>\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\/paul\/limiting-error-log-file-size-in-sql-server-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Limiting error log file size in SQL Server 2012 - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"It&#8217;s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server. One thing [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2013-11-05T00:09:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-05-20T14:56:50+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\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\/paul\/limiting-error-log-file-size-in-sql-server-2012\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/\",\"name\":\"Limiting error log file size in SQL Server 2012 - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2013-11-05T00:09:37+00:00\",\"dateModified\":\"2017-05-20T14:56:50+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Limiting error log file size in SQL Server 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Limiting error log file size in SQL Server 2012 - Paul S. Randal","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\/paul\/limiting-error-log-file-size-in-sql-server-2012\/","og_locale":"en_US","og_type":"article","og_title":"Limiting error log file size in SQL Server 2012 - Paul S. Randal","og_description":"It&#8217;s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server. One thing [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/","og_site_name":"Paul S. Randal","article_published_time":"2013-11-05T00:09:37+00:00","article_modified_time":"2017-05-20T14:56:50+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/","name":"Limiting error log file size in SQL Server 2012 - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2013-11-05T00:09:37+00:00","dateModified":"2017-05-20T14:56:50+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/limiting-error-log-file-size-in-sql-server-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Limiting error log file size in SQL Server 2012"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4233","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4233"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4233\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4233"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4233"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4233"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}