How to Search SQL Server Error Logs Using T-SQL and Temp Tables

Summary: Learn how to efficiently query the SQL Server Error Log using sp_readerrorlog and temporary tables to filter out noise and identify critical system errors.

Read Specific SQL Server Errors Using a Temp Table

Ever need to quickly check recent errors in your SQL Server logs without scrolling through thousands of lines in the Log File Viewer? This handy T-SQL snippet allows you to query the error log and gather information about various processes and messages. It's a great way to get a snapshot of what's been happening in your environment.

Querying SQL Server Error Logs

The following SQL code creates a temporary table and populates it by reading the last seven SQL Server error logs (from 0 to 6). It then selects all entries where the process is not 'logon', helping you filter out routine login messages and focus on actual errors or system events.

-- Create a temp table to hold log data
CREATE TABLE #t (dt DATETIME, process VARCHAR(200), msg VARCHAR(1000));

-- Populate the table by reading current and archived logs
INSERT INTO #t EXEC sp_readerrorlog 0, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 1, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 2, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 3, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 4, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 5, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 6, 1, 'error';

-- Filter results to ignore routine logon messages
SELECT * 
FROM #t 
WHERE process <> 'logon'
ORDER BY dt DESC;

-- Cleanup
DROP TABLE #t;
        

---

Found this T-SQL tip helpful? Share it with your fellow DBAs or subscribe for more SQL Server troubleshooting guides!