SQL Server Error Log is the first place to look for issues/errors arising in SQL Server. Error Log is a gift if used properly. you can Turn on several Trace Flags to write several other critical information into the SQL Server Error Log.
If the Log size is small enough it is easier to read the log by xp_readerrorlog OR sp_readerrorlog OR using SSMS (SQL Server Management Studio).
But what if the SQL Server Error Log has grown to 200+MB. it will be disastrous on your system resources to load that kind of file, and also if you are looking at certain text inside the Log file or a Specific date, you can use this method.
IMP : SQL Server Error Log is a OUT BOUND File, i.e. it does not have any effect on the SQL Server Performance as does the Database transaction Logs and its Growth does. but it is a good idea to trim it down at intervals to keep things simple and available. If the Error Log File is BOG (500+MB or 1GB), you will not be able to open it actively and your system resources will be hurt. Even writing information into such kind of file, will of course be hideous to SQL Server.
One thing we can do is, dump the file into a SQL Server Table to read the information with a select statement.
| if exists(select name from sysobjects where name like N’ReadSqlLog’)
drop Table ReadSqlLog; create Table ReadSqlLog insert into ReadSqlLog select * from ReadSqlLog where ????? |
we can re-cycle the SQL Server Error Log with sp_cycle_errorlog system stored procedure, without restarting the SQL Server Service.
By default SQL Server will retain the backup copies of 6 Previous Logs Files.
we can read the previous Logs Files as Follows:
EXEC sp_readerrorlog 1
EXEC sp_readerrorlog 2
Also we can search for the inline text occurrence like this:
EXEC sp_readerrorlog 2,1, ‘tempdb’
Loading ...