Query “SQL Server has encountered ? occurrence(s) of I/O requests taking longer than 15 seconds to complete on file” from the Logs

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) ,
TextData VARCHAR(MAX)
)

DECLARE    @i AS TINYINT
SET @i = 0

WHILE @i < 99
BEGIN
INSERT    INTO #slowio
EXEC sp_readerrorlog @i, 1,
'I/O requests taking longer than 15 seconds'
SET @i = @i + 1
END

SELECT    @@servername ServerName ,
LogDate ,
SUBSTRING(textdata, 28, CHARINDEX('occurrence', textdata) - 28) Occurences
FROM    #slowio

DROP TABLE #slowio

Related Posts

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.