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


advertisement
 

Tip: Read All SQL Server Log Files

Learn a script that allows you to read through all SQL Server Log files.


advertisement

WEBINAR:

On-Demand

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


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'

 

Visit the DevX Tip Bank

 





   
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Thanks for your registration, follow us on our social networks to keep up-to-date