{"id":818,"date":"2017-03-09T06:00:37","date_gmt":"2017-03-09T14:00:37","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=818"},"modified":"2017-04-13T09:19:55","modified_gmt":"2017-04-13T16:19:55","slug":"sqlskills-101-the-sql-server-errorlog","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/sqlskills-101-the-sql-server-errorlog\/","title":{"rendered":"SQLskills SQL101: The SQL Server ERRORLOG"},"content":{"rendered":"

One of the most useful logs you can review when there\u2019s a problem in SQL Server is the ERRORLOG.\u00a0 It may not always be the answer to your problem, but it\u2019s a good place to start.<\/p>\n

When you initially install SQL Server it only keeps the most recent six (6) ERRORLOG files, in addition to the current, active one.\u00a0 A new ERRORLOG file is generated when the instance restarts, or when you run sp_cycle_errorlog<\/a>.\u00a0 There are drawbacks to this default configuration.\u00a0 If you do not regularly restart your instance (which is perfectly fine), then one ERRORLOG file could contain months, maybe even a year or more, of information.\u00a0 That\u2019s a lot of entries to read through if you\u2019re looking for patterns or unusual errors.\u00a0 In addition, if you happen to run into a scenario where you restart the instance multiple times in succession \u2013 three or four times for example \u2013 you could potentially lose months of history.<\/p>\n

The solution is to recycle the ERRORLOG on a regular basis (I like to do this weekly), and increase the number of files retained.\u00a0 To recycle the ERRORLOG every week, set up an Agent job that calls sp_cycle_errorlog.\u00a0 I\u2019ve included code at the end of this post to create the Agent job and weekly schedule.<\/p>\n

Next, increase the number of ERRORLOG files you keep.\u00a0 You can do this through Management Studio.\u00a0 Expand the instance, then Management<\/strong>, right-click on SQL Server Logs<\/strong> and select Configure<\/strong>.\u00a0 Enable the option Limit the number of error log files before they are recycled<\/strong> and then enter a number for Maximum number of error log files:<\/strong>\u00a0 I like to keep 30 around.\u00a0 That usually equates to about six months of time, including a few unplanned restarts.<\/p>\n

\"Configure<\/a>
Configure SQL Server to keep 30 ERRORLOG files<\/figcaption><\/figure>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

You can also make this change with T-SQL:<\/p>\n

\r\nUSE [master];\r\nGO\r\nEXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\\Microsoft\\MSSQLServer\\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30;\r\nGO\r\n<\/pre>\n
Checking the ERRORLOG configuration is always something we do as part of a health audit, and I\u2019m always happy when I find systems with at least a few months\u2019 worth of files that are less than a few MB in size (I think the largest ERRORLOG I\u2019ve seen is 4GB…that one took a long time to open).\u00a0 If this isn\u2019t something you\u2019ve configured on your SQL Server instances yet, take a few minutes and knock it out.\u00a0 You won\u2019t regret having this information when a problem comes up, or when you\u2019re looking to see if a problem occurred a few months ago but maybe no one realized it.<\/p>\n
If you\u2019re interested in other posts in our SQLskills SQL101<\/strong> series, check out SQLskills.com\/help\/SQL101.<\/a><\/p>\n
Additional reading:<\/p>\n