Skip to content

Instantly share code, notes, and snippets.

@NJohnson9402
Last active July 20, 2018 04:11
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save NJohnson9402/cb123a23c16a63f3125c6a474a0241b1 to your computer and use it in GitHub Desktop.
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