Last active
July 20, 2018 04:11
Star
You must be signed in to star a gist
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 .
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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