{"id":498,"date":"2009-05-05T08:37:00","date_gmt":"2009-05-05T08:37:00","guid":{"rendered":"https:\/\/brainlitter.itgroove.net\/2009\/05\/05\/massive-sql-errorlogregain-control\/"},"modified":"2009-05-05T08:37:00","modified_gmt":"2009-05-05T08:37:00","slug":"massive-sql-errorlogregain-control","status":"publish","type":"post","link":"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/","title":{"rendered":"Massive SQL ERRORLOG \u2013 Regain Control"},"content":{"rendered":"
\n

If you have a massive, giant, huge, big (throwing the words in there, for folks on Google to find this post \ud83d\ude42 SQL ERRORLOG file, you will want to do TWO things (don’t just perform step #2, it will<\/span> likely be back!)<\/p>\n<\/p>\n

SQL ERRORLOG growth, when it starts, will grow quickly. I’ve seen it fill 40GB on a systems C: in less than a day, due to challenges with a Database finding its associated logfile. Because the file is so large, one would think that analyzing it would be difficult (but it isn’t so bad, and loads easily \u2013 see ‘Analyze the ERRORLOG file’). But the key is, find out what is wrong, deal with it and then clean up the big file. Unless the location has been overridden from the defaults, you are likely to find the ERRORLOG file in: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG<\/p>\n<\/p>\n

Analyze the ERRORLOG file<\/h1>\n<\/p>\n

To open the latest ERRORLOG file, fire up SQL Query Analyzer and perform the following:<\/p>\n

sp_readerrorlog<\/strong><\/span><\/p>\n<\/p>\n

And if the current one is small and has nothing of interest, append the ‘next number’ to the end of the instruction, to instruct SQL to open the previous version (ERRORLOG.1, etc.), such as the example below which would be looking at the previous (1st<\/sup> version since being reset) ERRORLOG file.<\/span><\/p>\n

sp_readerrorlog 1<\/strong><\/span><\/p>\n<\/p>\n

In our most recent discovery of a massive ERRORLOG file, it became pretty clear what was being logged, over and over and over again (to 40GB!)<\/p>\n

\"\"<\/p>\n<\/p>\n

Then \u2026 Cycle the ERRORLOG file<\/h1>\n<\/p>\n

After you have discovered the problem and are working towards resolution, you will want to get rid of the ERRORLOG file and here’s two ways:<\/p>\n

    \n
  1. Stop the SQL Service – Every time SQL Server is started, the current error log is renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on.<\/li>\n
  2. Perform a sp_cycle_errorlog<\/strong><\/span> \u2013 this enables you to cycle the error log files without stopping and starting the server. If you have difficulty resolving the problem causing this inflated ERRORLOG file, considering scheduling this until such time that you can<\/li>\n<\/ol>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

    If you have a massive, giant, huge, big (throwing the words in there, for folks on Google to find this post \ud83d\ude42 SQL ERRORLOG file, you will want to do TWO things (don’t just perform step #2, it will likely be back!) SQL ERRORLOG growth, when it starts, will grow quickly. I’ve seen it fill … <\/a><\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":"","_jetpack_memberships_contains_paid_content":false},"categories":[14,428],"tags":[],"acf":[],"yoast_head":"\nMassive SQL ERRORLOG \u2013 Regain Control - Brainlitter - Inside the mind of Sean Wallbridge<\/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:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Massive SQL ERRORLOG \u2013 Regain Control - Brainlitter - Inside the mind of Sean Wallbridge\" \/>\n<meta property=\"og:description\" content=\"If you have a massive, giant, huge, big (throwing the words in there, for folks on Google to find this post \ud83d\ude42 SQL ERRORLOG file, you will want to do TWO things (don’t just perform step #2, it will likely be back!) SQL ERRORLOG growth, when it starts, will grow quickly. I’ve seen it fill …\" \/>\n<meta property=\"og:url\" content=\"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/\" \/>\n<meta property=\"og:site_name\" content=\"Brainlitter - Inside the mind of Sean Wallbridge\" \/>\n<meta property=\"article:published_time\" content=\"2009-05-05T08:37:00+00:00\" \/>\n<meta name=\"author\" content=\"Sean Wallbridge\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sean Wallbridge\" \/>\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:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/\",\"url\":\"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/\",\"name\":\"Massive SQL ERRORLOG \u2013 Regain Control - Brainlitter - Inside the mind of Sean Wallbridge\",\"isPartOf\":{\"@id\":\"https:\/\/regroove.ca\/brainlitter\/#website\"},\"datePublished\":\"2009-05-05T08:37:00+00:00\",\"dateModified\":\"2009-05-05T08:37:00+00:00\",\"author\":{\"@id\":\"https:\/\/regroove.ca\/brainlitter\/#\/schema\/person\/74e1c0def190f181c1394c2b6d883e77\"},\"breadcrumb\":{\"@id\":\"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Brainlitter\",\"item\":\"https:\/\/regroove.ca\/brainlitter\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Massive SQL ERRORLOG \u2013 Regain Control\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/regroove.ca\/brainlitter\/#website\",\"url\":\"https:\/\/regroove.ca\/brainlitter\/\",\"name\":\"Brainlitter - Inside the mind of Sean Wallbridge\",\"description\":\"Dad. Husband. Drummer. Learner of Things.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/regroove.ca\/brainlitter\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/regroove.ca\/brainlitter\/#\/schema\/person\/74e1c0def190f181c1394c2b6d883e77\",\"name\":\"Sean Wallbridge\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/regroove.ca\/brainlitter\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/adf8cea6291c39d166616f2148d919a6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/adf8cea6291c39d166616f2148d919a6?s=96&d=mm&r=g\",\"caption\":\"Sean Wallbridge\"},\"url\":\"https:\/\/regroove.ca\/brainlitter\/author\/swallbridge\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Massive SQL ERRORLOG \u2013 Regain Control - Brainlitter - Inside the mind of Sean Wallbridge","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:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/","og_locale":"en_US","og_type":"article","og_title":"Massive SQL ERRORLOG \u2013 Regain Control - Brainlitter - Inside the mind of Sean Wallbridge","og_description":"If you have a massive, giant, huge, big (throwing the words in there, for folks on Google to find this post \ud83d\ude42 SQL ERRORLOG file, you will want to do TWO things (don’t just perform step #2, it will likely be back!) SQL ERRORLOG growth, when it starts, will grow quickly. I’ve seen it fill …","og_url":"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/","og_site_name":"Brainlitter - Inside the mind of Sean Wallbridge","article_published_time":"2009-05-05T08:37:00+00:00","author":"Sean Wallbridge","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Sean Wallbridge","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/","url":"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/","name":"Massive SQL ERRORLOG \u2013 Regain Control - Brainlitter - Inside the mind of Sean Wallbridge","isPartOf":{"@id":"https:\/\/regroove.ca\/brainlitter\/#website"},"datePublished":"2009-05-05T08:37:00+00:00","dateModified":"2009-05-05T08:37:00+00:00","author":{"@id":"https:\/\/regroove.ca\/brainlitter\/#\/schema\/person\/74e1c0def190f181c1394c2b6d883e77"},"breadcrumb":{"@id":"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/regroove.ca\/brainlitter\/2009\/05\/05\/massive-sql-errorlogregain-control\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Brainlitter","item":"https:\/\/regroove.ca\/brainlitter\/"},{"@type":"ListItem","position":2,"name":"Massive SQL ERRORLOG \u2013 Regain Control"}]},{"@type":"WebSite","@id":"https:\/\/regroove.ca\/brainlitter\/#website","url":"https:\/\/regroove.ca\/brainlitter\/","name":"Brainlitter - Inside the mind of Sean Wallbridge","description":"Dad. Husband. Drummer. Learner of Things.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/regroove.ca\/brainlitter\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/regroove.ca\/brainlitter\/#\/schema\/person\/74e1c0def190f181c1394c2b6d883e77","name":"Sean Wallbridge","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/regroove.ca\/brainlitter\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/adf8cea6291c39d166616f2148d919a6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/adf8cea6291c39d166616f2148d919a6?s=96&d=mm&r=g","caption":"Sean Wallbridge"},"url":"https:\/\/regroove.ca\/brainlitter\/author\/swallbridge\/"}]}},"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/posts\/498"}],"collection":[{"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/comments?post=498"}],"version-history":[{"count":0,"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/posts\/498\/revisions"}],"wp:attachment":[{"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/media?parent=498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/categories?post=498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regroove.ca\/brainlitter\/wp-json\/wp\/v2\/tags?post=498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}