Share via

Basics SQL Server Error Log Management

  • Article

SQL server maintains its own informational and Error events in Error log. The SQL Server error log is a great place to find the information what is happening in your database. SQL Server error log maintain information about failures/ Success and Errors that occurred in the database since SQL Server was last restarted or since the last time you have recycled the error logs. SQL Server error log contains user define events and certain system events.

By default there are 6 Archived error logs and 1 Current error log available in SQL Server instance.

http://sqltalkdotcom1.files.wordpress.com/2013/07/1.png?w=230&h=300
By default error Log available in the location C**:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log**

** **In the above path the filename called ERRORLOG is the currently using error log and 6 archived error logs are like ErrorLog.1, ErrorLog.2 to ErrorLog.6.

How to configure number of Error log to be maintained by SQL Server.

SQL Server management studio -> Management -> SQL Server Logs

http://sqltalkdotcom1.files.wordpress.com/2013/07/2.png?w=276&h=300

http://sqltalkdotcom1.files.wordpress.com/2013/07/3.png?w=300&h=94

Here can increase SQL Server error log up to 99 files.

How to start new SQL Server error log file.

Every time SQL server restart will start with new SQL server error log with name ERRORLOG and old file are renamed to ErrorLog.1, ErrorLog.1 and soon.

Here method to create new error log without server restart.

Exec sp_cycle_errorlog

Go

Before Sp_cycle_errorlog

http://sqltalkdotcom1.files.wordpress.com/2013/07/4.png?w=300&h=210

After Sp_cycle_errorlog

http://sqltalkdotcom1.files.wordpress.com/2013/07/5.png?w=300&h=235

Get Size of 6 default log files

EXEC master..sp_enumerrorlogs

Go

Log file data in Table

xp_readerrorlog

Have a nice day!!