Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Fetch and read SQL Error Logs on AWS RDS (or any SQL Server, but it's meant for RDS where permissions are limited!). See natethedba.wordpress.com/quickie-read-sql-error-log-in-aws-rds .
USE master;
DECLARE @CONST_P2 int = 1; --Error log, not Agent log.
DECLARE @NumLogFiles int = 30; --SET THIS APPROPRIATELY!
--^ (there's no way to get it programatically outside of registry-reads/other-xp's, none of which is doable in RDS, AFAIK.
--Example: show events from 7/19/2018 between 2am and 2:30am PDT
DECLARE @StartDate datetime = '2018-07-19T02:00:00'
, @EndDate datetime = '2018-07-19T02:30:00';
CREATE TABLE #ErrorLogs ([LogDate] datetime, [ProcessInfo] nvarchar(50), [Text] nvarchar(max));
DECLARE @LogNum int = 0;
WHILE (@LogNum <= @NumLogFiles)
BEGIN
INSERT #ErrorLogs (LogDate, ProcessInfo, [Text])
EXEC sys.sp_readerrorlog @p1 = @LogNum, @p2 = @CONST_P2
SET @LogNum += 1;
END;
ALTER TABLE #ErrorLogs
ADD LocalDate datetime;
UPDATE #ErrorLogs
SET LocalDate = CONVERT(datetime, LogDate AT TIME ZONE 'UTC'
AT TIME ZONE 'Pacific Standard Time');
SELECT el.LocalDate, el.ProcessInfo, el.[Text]
FROM #ErrorLogs el
WHERE el.LocalDate >= @StartDate
AND el.LocalDate < @EndDate
ORDER BY LogDate
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.