Ever have this happen?
You (on vacation): I’ll have another piña colada please. Yes, in the coconut. Thanks! Hmm, my phone is ringing…..Hello? Work: There are problems! The database is messing up and being really slow! : Well, email me the latest log file from SQL Server and I’ll take a look. : We sent the latest one to you. : Yeah, I just saw it. It’s empty. Did you restart the server? : Of course. When you aren’t here we always restart the server. : *Heavy sigh* OK. Send me all the log files from the SQL Server then. : Sure thing! They are on their way. : *Heavy sigh* There are only 6 files and they are all the same? Are you sending me everything? : Sure we are! That’s everything in the directory you told us to send. All 6 files. : How many times have you restarted the server? : The server has been really bad today and we have had to restart it constantly to get anything done. : OK. I’ll cut things short here and try to get in remotely and try to see what’s going on. Don’t restart the server again until I say it is OK to do so.What happened to the SQL Log files?
Hopefully this never will, or has, happened to you, but if it did do you know why you only received 6 files?
That is the minimum number of log files SQL Server will keep. Oh, and just so you know, that’s the default number of files too.
Every time the SQL service is restarted, not only is a new tempdb created, but so is a new log file. You can also cycle the log files manually using sp_cycle_errorlog .
In this case a far too common event occurred. The office was desperate and wanted to let you have your time off, so they just kept restarting the server whenever there was an issue; and unknown to them, before the log could really capture anything useful for you.
Now you have the arduous task of figuring out what happened with out a lot of supporting documentation.
Vacation ruined.
If you had more logs to be able to possibly capture the information you needed! If only you had increased that default limit!
Speaking of that, here’s how you do that:
Using the GUI:
Using SSMS, Connect to and instance of SQL Server. Expand Management and right click on SQL Server Logs to select Configure
- Right click and select configure
[divider]
Mark the check-box Limit the number of error logs before they are recycled then set the Maximum number of error log files to the value you want. In this example we are setting it to 50. Click OK and that’s it. No restart of services or anything else is required.
Set the number of log files to keep
Using a script:
You can also change the value using an undocumented script in SQL Server to change the value in the registry. Just change the patch and the value at the end (50 in this example) to the value you want.
USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 50 GO
Because this method is undocumented as it changes the value in the registry via T-SQL and you have to know the path to place the updated value in it is strongly suggested to use using the GUI to make sure it is done correctly.