A while ago I wrote a post about the SQL Server error log. Today I will show how to load the SQL error log into a table for analysis via T-SQL.
Read on to see a couple of ways to traverse all the error logs and load into a table for analysis.
I encourage you to read this article first: How to Analyze the SQL Server Error Log
It talks about the SQL Server error log in general.
Why Not Just Use the File System?
When possible I prefer to go into the file system and open the SQL Server error logs in a text editor e.g. Notepad++ or UltraEdit. However, there are sometimes you may have access to a SQL Server instance but not be able to RDP or otherwise scan the file system.
That’s where this comes in – straight T-SQL.
The Key Players: xp_readerrorlog and sp_readerrorlog
The 2 key system procedures at play here are “xp_readerrorlog” and “sp_readerrorlog”. Basically, xp_readerrorlog is a wrapper for sp_readerrorlog – granting it more parameters to call.
|
1 2 3 4 5 |
--xp_readerror log is a wrapper for sp_readerrorlog, it has more parameters EXEC sp_help 'xp_readerrorlog' EXEC sp_helptext 'xp_readerrorlog' EXEC sp_help 'sp_readerrorlog' EXEC sp_helptext 'sp_readerrorlog' |
From calling exec sp_helptext ‘sp_readerrorlog’ you can see the underlying definition:
Stage and Load
The process is simple – just create a temp table (or physical table if you prefer) and load it from sp_readerrorlog
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
/* Stage a temp table and load results of SQL Error log */ /* Legacy way IF OBJECT_ID('tempdb.dbo.#SQLErrorLog') IS NOT NULL DROP TABLE #SQLErrorLog */ --SQL Server 2016+ DROP TABLE IF EXISTS #SQLErrorLog; --assuming SQL Server 2016+...otherwise use the typical if object exists drop CREATE TABLE #SQLErrorLog ( LogDate DATETIME ,ProcessInfo NVARCHAR(12) ,LogText NVARCHAR(3999) ) INSERT INTO #SQLErrorLog ( LogDate ,ProcessInfo ,LogText ) EXEC sp_readerrorlog; SELECT * FROM #SQLErrorLog; -- this is your output table |
Now you can query for things in the SQL Server error log from your table. Some quick examples of things you can check here (from Glenn Berry’s popular DMVs): <Mine below some examples querying from table>
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Get socket, physical core and logical core count from the SQL Server Error log. (Query 2) (Core Counts) -- This query might take a few seconds depending on the size of your error log EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket'; SELECT * FROM #SQLErrorLog WHERE LogText like '%detected%'; -- Returns status of instant file initialization (Query 6) (IFI Status) EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization'; SELECT * FROM #SQLErrorLog WHERE LogText like '%instant file initialization%'; -- Get System Manufacturer and model number from SQL Server Error log (Query 19) (System Manufacturer) EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer'; SELECT * FROM #SQLErrorLog WHERE LogText like '%manufacturer%'; -- Get number of data files in tempdb database (Query 26) (TempDB Data Files) EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has'; SELECT * FROM #SQLErrorLog WHERE LogText like '%tempdb%'; |
Iterate SQL Server Error Logs – Results in the Same Table
Part 1 is to get a list of the SQL Server error log files we want to peruse:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
/******************************************************** Loop through all log files as WHILE loop All results in the same table *********************************************************/ IF OBJECT_ID('tempdb.dbo.#ErrorLogFiles') IS NOT NULL DROP TABLE #ErrorLogFiles CREATE TABLE #ErrorLogFiles ( [Archive #] INT ,[Date] NVARCHAR(259) ,[Log File Size (Byte)] INT ) INSERT INTO #ErrorLogFiles ( [Archive #] ,[Date] ,[Log File Size (Byte)] ) EXEC master.sys.xp_enumerrorlogs; SELECT * FROM #ErrorLogFiles; --the error logs we will loop through |
Next we create a results table and then loop through each error log file loading it into the results table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
--Create results table IF OBJECT_ID('SQLErrorLog_AllResults') IS NOT NULL DROP TABLE SQLErrorLog_AllResults; CREATE TABLE SQLErrorLog_AllResults ( LogDate DATETIME ,ProcessInfo NVARCHAR(12) ,LogText NVARCHAR(3999) ) --Iterate through each log file and output to a table (all results) DECLARE @i INT = 0; DECLARE @tableName NVARCHAR(128); DECLARE @sql NVARCHAR(MAX); DECLARE @logCount INT; SELECT @logCount = COUNT(*) FROM #ErrorLogFiles; TRUNCATE TABLE SQLErrorLog_AllResults; WHILE (@i < @logCount) BEGIN SELECT @sql = ' INSERT INTO SQLErrorLog_AllResults ( LogDate ,ProcessInfo ,LogText ) EXEC sp_readerrorlog ' + CAST(@i AS NVARCHAR(8)) + ';' --PRINT @sql; EXEC sys.sp_executesql @sql; SET @sql = ''; SET @i += 1; END --View results SELECT * FROM SQLErrorLog_AllResults; |
Now we have all of the error logs available stored in a table for analysis!
Iterate SQL Server Error Logs – Results in Separate Tables
The above example shows how to load all of the SQL Server error logs into the same table. But what if we want to load them to individual tables? The technique is similar:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
/******************************************************** Loop through all log files as cursor All results as a separate table *********************************************************/ IF OBJECT_ID('tempdb.dbo.#ErrorLogFiles') IS NOT NULL DROP TABLE #ErrorLogFiles CREATE TABLE #ErrorLogFiles ( [Archive #] INT ,[Date] NVARCHAR(259) ,[Log File Size (Byte)] INT ) INSERT INTO #ErrorLogFiles ( [Archive #] ,[Date] ,[Log File Size (Byte)] ) EXEC master.sys.xp_enumerrorlogs; SELECT * FROM #ErrorLogFiles; --EXEC sys.sp_readerrorlog 0; --EXEC sys.sp_readerrorlog 1; --EXEC sys.sp_readerrorlog 2; --EXEC sys.sp_readerrorlog 3; --EXEC sys.sp_readerrorlog 4; --EXEC sys.sp_readerrorlog 5; --EXEC sys.sp_readerrorlog 6; --Iterate through each log file and output to a table (separate results) DECLARE @logCount INT; SELECT @logCount = COUNT(*) FROM #ErrorLogFiles; DECLARE @sql NVARCHAR(MAX); DECLARE @i INT = 0; DECLARE @tableName NVARCHAR(128); DECLARE curReadSQLErrorLogs CURSOR FAST_FORWARD READ_ONLY FOR SELECT [Archive #] FROM #ErrorLogFiles OPEN curReadSQLErrorLogs FETCH NEXT FROM curReadSQLErrorLogs INTO @i WHILE @@FETCH_STATUS = 0 BEGIN SET @tableName = 'SQLErrorLog_' + CAST(@i AS NVARCHAR(8)); SELECT @sql = ' IF OBJECT_ID(''' + @tableName + ''') IS NOT NULL DROP TABLE ' + @tableName + ' CREATE TABLE ' + @tableName + ' ( LogDate DATETIME ,ProcessInfo NVARCHAR(12) ,LogText NVARCHAR(3999) ) INSERT INTO ' + @tableName + ' ( LogDate ,ProcessInfo ,LogText ) EXEC sp_readerrorlog ' + CAST(@i AS NVARCHAR(8)) + ';' --PRINT @sql; EXEC sys.sp_executesql @sql; SET @sql = ''; SET @i += 1; FETCH NEXT FROM curReadSQLErrorLogs INTO @i END CLOSE curReadSQLErrorLogs DEALLOCATE curReadSQLErrorLogs |
Now we have a table populated for each individual SQL Server error log:
|
1 2 3 4 5 6 7 |
SELECT * FROM SQLErrorLog_0; SELECT * FROM SQLErrorLog_1; SELECT * FROM SQLErrorLog_2; SELECT * FROM SQLErrorLog_3; SELECT * FROM SQLErrorLog_4; SELECT * FROM SQLErrorLog_5; SELECT * FROM SQLErrorLog_6; |
So there you have it. Simple but sometimes very useful way to analyze the SQL Server error log files without access to the file system.
Thanks for reading!
If you liked this post then you might also like my post about How to Analyze the SQL Server Error Log
Do you care about InfoSec and Privacy? Then YOU need to use a VPN.
[…] Jeff Mlakar shows how you can load the SQL Server error log into a table: […]