Many times, the size of the error log of SQL Server becomes huge if the Server is ONLINE for a long time and we have not recycled the SQL logs manually. In these cases, to read SQL Server Logs from GUI is a pain. It takes a lot of time for the SQL Server GUI to load the log and during emergency we are always running against time..
There is a smarter way to read the SQL Server ErrorLog using TSQL Command which is faster than GUI. The name of the stored Procedure is sp_readerrorlog and it allows us to read the contents of the SQL Server Error log from the SSMS and we can also filter the result set as per our requirement.
sp_readerrorlog takes four parameters:
- Error log file we want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
- Log file type: 1 or NULL = error log, 2 = SQL Agent log
- Search string 1: String one we want to search for
- Search string 2: String two we want to search to further refine the result set
The above command all the contents from current SQL Server errorlog.
Exec sp_readerrorlog 1,1,‘memory’
The above command reads the first SQL Server Archived Error log and searches for returns the result wherever “memory” appears
Let me know if anyone of you need more information regarding this topic.