Recently we moved some servers from being physically on local drives to virtual on our SAN. We started getting Error 833 messages every night during Integrity Checks. The more servers we added the more messages we got. I needed a quick way to retrieve these messages from our error logs and considering we cycle the error log every night at midnight and we are configured to have 99 logs that meant querying 99 logs. So I wrote this script to pull out each occurrence of the message and put the number of occurrences into a separate column. Now I can quickly pull this data and show how long it has been occurring.
CREATE TABLE #slowio
LogDate DATETIME ,
processinfo VARCHAR(MAX) ,
DECLARE @i AS TINYINT
SET @i = 0
WHILE @i < 99
INSERT INTO #slowio
EXEC sp_readerrorlog @i, 1,
'I/O requests taking longer than 15 seconds'
SET @i = @i + 1
SELECT @@servername ServerName ,
SUBSTRING(textdata, 28, CHARINDEX('occurrence', textdata) - 28) Occurences
DROP TABLE #slowio
She also tinkered with databases in middle school to keep her sports card collection organized.
Tracy has volunteered through the NC Guardian ad Litem program since 2003 advocating for abused and neglected foster children in court.This is her passion outside of SQL Server and favorite job.More information about this program in North Carolina can be found at http://volunteerforgal.org or the national organization CASA at http://www.casaforchildren.org.
Latest posts by Tracy Boggiano (see all)
- TSQL Tuesday #93: Interviewing Patterns & Anti-Patterns - August 9, 2017
- Setting Custom Fill Factors Based on Page Splits - July 25, 2017
- Monitoring Availability Groups Part 3 – Extended Events - July 11, 2017