SQL Server maintains Error log file which can store information messages, and errors with numbers similar to windows event viewer. When we encounter any issue in the SQL Server this error log is the first place we need to check whats wrong with the server. These error log files are stored in the LOG folder of each instance example "c:\program files\microsoft sql server\mssql10.50\mssql\log",
If required we can change the location of error log files by modifying -e parameter in the SQL Server Engine Configuration.
We can see these error logs in SSMS - Management - SQL Server Logs - Current, Archive#1, Archive#2, Archive#3, Archive#4, Archive#5, Archive#6
These will be maintained by SQL Server, Each time if you restart SQL Server these files get recycle, what it means is the Archive#6 file gets deleted and Current will become Archive#1 and so on and new file will be created and named as Current.
There are couple of disadvantages using the default behavior of SQL Server that there wont be any clue for size of the error log files, that means you cannot except the size of all error logs should be same; one file can contain 10 days of data and other file contains only 2 days of data. And recycling happend thru SQL Server so users may not control the default behaviour, so that we need to set seperate process to make sure one file has only 1 day of information.
Also we need to modify the default error log files from 7 to some large number, otherwise we may lost the history.
As a first step we need to increase the error log files from 7 to 50, To do so
SSMS - Management - SQL Server Logs - RightClick - Configure - Check Limit the number of error files before they are recycled We can also create one error log file for every one day by calling the sys.sp_cycle_errorlog on daily basis, It will be very helpful while managing error log files.
One day data will be stored for one log file so that we can get go back and verify those files at any time easily. The same is applicable for SQL Server Agent also, but there 10 files will be maintained instead of 7 files.
Like SQL Server Error logs we cannot change increase the number of error logs for SQL Server Agent, sp_cycle_agent_errorlog helps to recycle Agent error logs without restarting Agent.
No comments: