Have you ever had a SQL Server error log file grow larger than 2 GB? How have you read the log to find out why it has grown? Notepad & Notepad++ have 2 GB limits, MS Word has a 500 MB limit, reading the errorlog through SSMS does not work ….. where to from here?
First thing I did was recycle the SQL Server errorlog file “exec sp_cycle_errorlog”. This creates a new error log file that is small enough to read. Preferably you should recycle the errorlog on a frequent basis. Now that you have recycled the error log file you can check it for any errors. If a process has gone rogue and is filling your log file it will not take long to find. If it has already stopped then you need to find out in the .1 log file when the issue finished &or what it was.
exec xp_readerrorlog 1,1 (http://www.mssqltips.com/tip.asp?tip=1476 gives a really good example of creating a stored procedure that allows you to choose the type of log you wish to read)
Sometimes a large log file can take a while to go through. You could create a temporary table to load the results from the xp_readerrorlog execution and perform some analysis on what is filling the log file.
Feel free to use this piece of code and analyse your log files as you please.
Enjoy
Leave a Reply