603
Ever needed to quickly check your SQL Server logs for troubleshooting or monitoring purposes? With the help of a simple and efficient T-SQL script, you can easily capture and store all your SQL Server logs in a temporary table, allowing for convenient analysis. This approach can save you valuable time, particularly when dealing with large log volumes, and provide you with the flexibility to filter and sort the logs according to your specific requirements. Get ready to optimize your SQL Server log analysis by exploring the T-SQL script below and streamline your logging process effortlessly.
T-Sql
-- Set which log file to read (0 = current log file)
DECLARE @LogFileID INT = 0 -- to specify which log file to read.
/*
Note: The DECLARE statement above is optional. You can execute the script without explicitly declaring the @LogFileID.
The optional @LogFileID variable can be used to limit the logs retrieved, especially if the server has a large number of logs, to avoid overloading the system.
0 (default): This value indicates the current log file. It reads the most recent SQL Server error log file.
1: It reads the previous SQL Server error log file.
2: It reads the second previous SQL Server error log file.
n: It reads the nth previous SQL Server error log file. For example, if you specify 3, it will read the third previous log file.
*/
-- Create a temporary table to store the log data
CREATE TABLE #SQLLogTemp
(
LogDate DATETIME,
SourceInfo NVARCHAR(64),
MsgText NVARCHAR(MAX)
)
-- Insert log data into the temporary table
INSERT INTO #SQLLogTemp
(
LogDate,
SourceInfo,
MsgText
)
EXEC sp_readerrorlog @LogFileID;
-- Query to retrieve all log entries
SELECT
LogDate,
SourceInfo,
MsgText
FROM
#SQLLogTemp
--WHERE /*Note: You might want to unfilter the WHERE clause if you're going to filter the data*/
-- MsgText LIKE '%failed login%'
ORDER BY 1 DESC
/* You can use either the column name or the column's ordinal number (the position of the column in the SELECT list) to specify which column to sort by. */
/*
Drop the temporary table
Note: You might want to comment this line out if you're going to work with the data
*/
DROP TABLE #SQLLogTemp
