dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Feb 19, 2019

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Read All SQL Server Log Files

You can use a script similar to the following to read through all SQL Server Log files:

CREATE PROCEDURE SearchLogFiles (@LogType INT = 1, Filter NVARCHAR(MAX) = '')
AS
BEGIN

   DECLARE @LogsTable TABLE (LogIndex INT, LogDate DATETIME, LogSize INT)
   
   DECLARE @LogRows TABLE (LogDate DATETIME, 
                              ProcessInfo NVARCHAR (4000), 
                              Test NVARCHAR (4000))
 
   INSERT INTO @LogsTable
   EXEC xp_enumerrorlogs @LogType
 
   DECLARE @index INT = 0
   WHILE @index <= (SELECT MAX (LogIndex) FROM @LogsTable)
   BEGIN
      INSERT INTO @LogRows 
      EXEC xp_readerrorlog @index 
         , @LogType -- 1=SQL Server log, 2=SQL Agent log
         , @filterstr -- filter string
         , @filterstr
 
      SET @index += 1
   END

   SELECT *
   FROM @LogRows
 
   ORDER BY LogDate DESC
END

Use it as follows:

Exec SearchLogFiles 1,'ERROR'
Hannes du Preez
 
Thanks for your registration, follow us on our social networks to keep up-to-date