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

[sourcecode language=”sql”]

create table #err_log_tmp
(
ArchiveNo int,
CreateDate nvarchar(24),
Size int
)
insert #err_log_tmp
exec master.dbo.sp_enumerrorlogs

[/sourcecode]

If you look at the details of the error log file after restart, you will see something like this:

Sql Server Error Log

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.

[sourcecode language=”sql”]
USE [master]
GO

EXEC sp_cycle_errorlog
GO
[/sourcecode]

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)

SqlServer ErrorLog After Recycle

Hope this helps,
_SqlTimes