When Sql Server is restarted, along with a lot of other things, a new Sql Server Error Log file is created in its default location (…\MSSQL10.MSSQLSERVER\MSSQL\Log\). When you open that location, you’ll see abunch of ERRORLOG files with names like:
- ERRORLOG
- ERRORLOG.1
- ERRORLOG.2
- …
A new file is created after every restart with the name ERRORLOG. The old ERRORLOG file will now be renamed as ERRORLOG.1 and (ERRORLOG.1 is renamed to ERRORLOG.2…so on and so forth). These ERRORLOGs are just plain text files in this location …\MSSQL10.MSSQLSERVER\MSSQL\Log\ where new line items are appended at the end of the file. But when you open it through SSMS, you see it upside down where you see the most recent entry at the top. Sql Server reads the entire file using EXEC master..sp_enumerrorlogs and loads into a temporary table then
create table #err_log_tmp ( ArchiveNo int, CreateDate nvarchar(24), Size int ) insert #err_log_tmp exec master.dbo.sp_enumerrorlogs
If you look at the details of the error log file after restart, you will see something like this:
Once in a while this error log gets too huge and could cause delays in loading the entire file into temp table and then open it for viewing. In such cases, you can enable Sql Server to open a new log file without restarting Sql Server.
USE [master] GO EXEC sp_cycle_errorlog GO
A new file is created with ERRORLOG as the name and the old file is renamed as ERRORLOG.1.
Interestingly, the details in the first part of the error log are slightly different from a new error log when Sql Server is restarted. Since we are just recycling the log file and not restarting the entire instance, it does not have all the steps that Sql Server goes though when a restart happens. It makes a note early on that this is not a restat, but a reinitializing of the ERRORLOG file. See the image below (after recycling or reinitalizing)
Hope this helps,
_SqlTimes
[…] « Recycle Sql Server Error Logs (or create new sql server error logs) […]
[…] Sql Server goes through the start up process, ERRORLOG file is updated with its progress. Open ERRORLOG and you get an insight into the start up […]