SQL Server has automatic error tracking system . it tracks system and user related events messages into error log and sql server logs. This makes DBA’s job litle bit easier while troubleshooting the system related issues generated by sql server.
Configure sql server error log
We can find the error log under SQL Server agent/ error log folder, by default sql server can generate up to 6 log files. This limit can be increased up to 99 log files.
Log files gets stored on disk with default file location : “C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\LOG\ERRORLOG and ERRORLOG.1 ”
In case of sql server 2oo8 file location is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log”
In case of named Instance file location is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.[SQL2008]\MSSQL\Log\”
· [SQL2008] –>Named instance
To view the error log connect to SQL Server Instance using Microsoft SQL Server Management Studio
–> Click on Management Folder
–>Click on SQL Server log folder
–>Double click on folder to view log files or right click on SQL Server log and select View/SQL Server log
View the error log using T-SQL command : sp_readerrorlog
Note: Whenever SQL server is started, it rename current error log to errorlog.1 and errorlog.1 becomes errorlog.2 like that errorlog.2 becomes errorlog.3 it keeps on going till last file.
If you want to avoid server restart then you can use “sp_cycle_errorlog” which will create new error log and rename other error logs with the respective numbers.
SQL SERVER AGENT
For SQL Server agent and jobs related errors, we have to see error log which generates under SQL Server Agent/ Error log folder. It works on same phenomena as of SQL Server log.
Default file location for the error log is: “C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT”
In case of SQL Server 2008, default file location is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT”
If sql server fail to start due to any reason then check out the errors in the SQLAGENT.OUT as well as ERRORLOG file.