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