Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Overview SSIS Package II - Retrieving SQL Error Log

By David Bird, (first published: 2008/01/14)

SQL Overview SSIS Package II - Retrieving SQL Error Log

In part I, I presented how to create a SSIS package to collect the database statuses for all instances. This approach works fine when a single select statement is executed on the remote instance. When using multiple statements or a stored procedure, a modified approach is needed to return the results.

This approach creates a table on the remote instance in the TEMPDB database. The output from multiple queries or stored procedures is collected in this table. After the data has been collected, a single select statement can be used to retrieve the data from the remote instance. This table does not have to be in TEMPDB. It's just a convenient place to put it because every instance has a TEMPDB database and the table does not need to be recoverable.

I want to capture the errors that are in the SQL Server Error Log for all servers. To accomplish this, I will be using the stored procedure xp_readerrorlog to read the SQL Server Error Log files from each server\instance. Only the last two days of records will be retrieved. This will be sufficient because this package is expected to be executed daily. SQL Server 2000 and 2005 have a different format for the ErrorLog file. Therefore the SQL script will need to handle each format. When the data is finally collected from each server\instance, a query can be used to report all errors for every server\instance.

Items used:
  • SQL Server Business Intelligence Development Studio for SQL Server 2005 x64 SP2 with hot fix KB934459
  • Package from Part I
As it was in Part I, some of these instructions are very detailed and will bore those very familiar with SSIS. I am sorry for that but I wanted a level of detail to allow those still somewhat new to SSIS to be able to follow along.

Create the ErrorLog Table

USE [SQL_Overview]
GO
CREATE TABLE [dbo].[ErrorLog](
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
)
ON [PRIMARY]

This table will contain all the ErrorLog records for all the entries in the SSIS_ServerList table.

Create ErrorLog TEMPDB Table

This TEMPDB table will be used on each instance to collect the SQL Server error log information. This table must be created on the instance that the package will be executed from before the package can be updated. The package will then create the table on all of the other server\instance listed in the SSIS_ServerList table.

IF OBJECT_ID('tempdb.dbo.ErrorLog') IS NOT NULL
DROP TABLE tempdb.dbo.ErrorLog
GO
CREATE TABLE tempdb.dbo.ErrorLog(
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
) ON [PRIMARY]

Updating the SSIS Package

Open the SQL Overview package created in Part I.

Create Tasks

Truncate ErrorLog Table Task
This task will truncate the table ErrorLog in the SQL_Overview database.
  • Using the Toolbox, add "Execute SQL Task" object to the Truncate Tables "Sequence Container"
  • Settings - Double Click on Icon
    • Name: Truncate ErrorLog
    • Connection: to QASRV.SQL_Overview
    • SQL Statement: TRUNCATE Table ErrorLog
    • BypassPrepare: False
Load ErrorLog Container
This container will loop through the server names passed in the SRV_Conn variable, connect to each server, and execute three SQL tasks.
  1. Add "Foreach Loop Container" to the right of the "Collect Database Status"
    1. Connect the "Collect Database Status" container to this object with the green line/arrow
    2. Settings
      1. General
        1. Name: Collect ErrorLog
      1. Collection
        1. Change Enumerator to Foreach ADO enumerator
        2. Select ADO object source variable User::SQL_RS
      2. Variable Mapping
        1. Add User::SRV_Conn
      3. Click OK
    3. Right Click on this Container
    4. Select Properties
    5. Set MaximumErrorCount to 999
  2. Add "Execute SQL Task" to the "Collect Error Log" container.
    This Task will be used to create a TEMPDB table and populate it with the last 2 days of error logs on the remote instance. The SQL used is long and complex. Testing it is recommended.
    1. Settings - Double Click on Icon
      • Name: Get ErrorLog
      • Connection: to MultiServer
      • SQL Statement:
        -- Drop Temporary Tables
        IF OBJECT_ID('tempdb..#Errors8') IS NOT NULL
        DROP TABLE #Errors8
        IF OBJECT_ID('tempdb..#Errors9') IS NOT NULL
        DROP TABLE #Errors9
        IF OBJECT_ID('tempdb..#ErrorLogs') IS NOT NULL
        DROP TABLE #ErrorLogs
        IF OBJECT_ID('tempdb.dbo.ErrorLog') IS NOT NULL
        DROP TABLE tempdb.dbo.ErrorLog
        GO
        CREATE TABLE tempdb.dbo.ErrorLog(
        [Server] [nvarchar](128) NOT NULL,
        [dtMessage] [datetime] NULL,
        [SPID] [varchar](50) NULL,
        [vchMessage] [nvarchar](4000) NULL,
        [ID] [int] NULL
        )
        ON [PRIMARY]
        -- Set extract date for 2 days
        DECLARE @ExtractDate datetime
        SET @ExtractDate = DATEADD(dd,-2,CURRENT_TIMESTAMP)
        SELECT 'Extract Date = ' + CONVERT(CHAR(26),@ExtractDate)
        -- SQL Server 2000 and 2005
        each has different formats for reading the Error Log file
        DECLARE @VersionId AS CHAR(1)
        SELECT @VersionId = LEFT(CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')),1)

        CREATE TABLE #ErrorLogs (intFileId INT
        , dtLastChangeDate DateTime NOT NULL
        ,
        biLogFileSize bigint)
        INSERT INTO #ErrorLogs
        EXEC master.dbo.xp_enumerrorlogs
        DECLARE @SQL AS VARCHAR(256)
        -- Define Temp Table to contain error log messages
        IF @VersionId = 8
        CREATE TABLE #Errors8 (vchMessage VARCHAR(255), ID INT)
        ELSE
        CREATE TABLE
        #Errors9 (LogDate datetime, Processinfo VARCHAR (10), vchMessage NVARCHAR(4000))
        -- Processes Error Logs files modified since last Run Date
        DECLARE ErrorLog_cursor CURSOR FOR
        SELECT
        intFileId
        FROM #ErrorLogs
        WHERE dtLastChangeDate > @ExtractDate
        OPEN ErrorLog_cursor
        DECLARE @intFileId INT
        FETCH
        NEXT
        FROM ErrorLog_cursor INTO @intFileId
        WHILE (@@FETCH_STATUS <> -1)
        BEGIN
        IF
        (@@FETCH_STATUS <> -2)
        BEGIN
        -- Load Error Log into temporary Table
        IF @intFileId = 0
        IF @VersionId = 8
        INSERT #Errors8
        EXEC master.dbo.xp_readerrorlog
        ELSE
        INSERT
        #Errors9
        EXEC master.dbo.xp_readerrorlog
        ELSE
        IF
        @VersionId = 8
        INSERT #Errors8
        EXEC master.dbo.xp_readerrorlog @intFileId
        ELSE
        INSERT
        #Errors9
        EXEC master.dbo.xp_readerrorlog @intFileId
        END
        FETCH
        NEXT
        FROM ErrorLog_cursor INTO @intFileId
        END
        CLOSE
        ErrorLog_cursor
        DEALLOCATE ErrorLog_cursor
        -- Extract all error log record for last two days
        IF @VersionId = 8
        BEGIN
        INSERT INTO
        tempdb.dbo.ErrorLog
        ([Server]
        ,[dtMessage]
        ,[SPID]
        ,[vchMessage]
        ,[ID])
        SELECT @@SERVERNAME
        ,CASE ISDATE( LEFT(vchMessage,22))
        WHEN 1 THEN LEFT(vchMessage,22)
        ELSE '1900-01-01'
        END
        ,NULL
        ,
        vchMessage, ID
        FROM #Errors8
        END
        ELSE
        BEGIN
        INSERT INTO
        tempdb.dbo.ErrorLog
        ([Server]
        ,[dtMessage]
        ,[SPID]
        ,[vchMessage]
        ,[ID])
        SELECT @@SERVERNAME
        ,LogDate
        ,Processinfo
        ,vchMessage, NULL
        FROM #Errors9
        WHERE LogDate >= @ExtractDate
        END
        DELETE
        FROM
        tempdb.dbo.ErrorLog
        WHERE [dtMessage] < @ExtractDate
        SELECT *
        FROM tempdb.dbo.ErrorLog
        -- Drop Temporary Tables
        IF OBJECT_ID('tempdb..#Errors8') IS NOT NULL
        DROP TABLE #Errors8
        IF OBJECT_ID('tempdb..#Errors9') IS NOT NULL
        DROP TABLE #Errors9
        IF OBJECT_ID('tempdb..#ErrorLogs') IS NOT NULL
        DROP TABLE #ErrorLogs
      • BypassPrepare: False
      • Click OK
      • Right Click on Get ErrorLog
      • Select Properties
      • Properties to set MaximumErrorCount to 999
  3. Add "Data Flow Task" to the "Collect Error Log" container
    1. Connect the "Get ErrorLog" Task to this object with the green line/arrow
    2. Rename to Load ErrorLog
    3. Right Click on this Task
    4. Select Properties
    5. Set MaximumErrorCount to 999
  4. Next, two data flow elements will be added to the "Data Flow Task". The first will read the ErrorLog table on the remote instance and the other will save it in the local database.
  5. Select the Data Flow Tab or double click on icon for the "Data Flow task"
  6. Add "OLE DB Source" from toolbox
    1. Double Click Icon
    1. OLE DB Connection manager: MultiServer
    2. Change Data access mode to SQL Command
    3. SQL Command Text:
      SELECT [Server]
      ,[dtMessage]
      ,[SPID]
      ,[vchMessage]
      ,[ID]
      FROM [tempdb].[dbo].[ErrorLog]
    4. Click Preview to verify the SQL and then click close when done
    5. Click OK
  7. Add "OLE DB Destination" from toolbox
    • Connect the "OLE DB Source" to this element with the green line/arrow
    • Double click on the Icon for OLE DB Destination and make the following changes
      1. OLE connection manager: QASRV.SQL_Overview
      2. Name of the table or the view: [dbo].[ErrorLog]
      3. Click Mappings and confirm the column mappings are correct
      4. Click OK

Ready to be Tested

  • Click Control Flow tab
  • Save all by pressing Ctrl+Shift +S
  • Press F5 to run
  • To review any errors by checking the Progress tab.
  • When done, click the blue line to get back to edit mode

Query the Error Logs

SQL Server Error Logs contain a variety of information. Now by using this SSIS package the error logs for all the instances can be queried from a single table. I've created a SQL statement that returns any error or warning messages I believe warrant review for possible problems.

SELECT [Server]
,[dtMessage]
,[SPID]
,[vchMessage]
,[ID]
FROM [SQL_Overview].[dbo].[ErrorLog]
WHERE ([vchMessage] LIKE '%error%'
OR [vchMessage] LIKE '%fail%'
OR [vchMessage] LIKE '%Warning%'
OR [vchMessage] LIKE '%The SQL Server cannot obtain a LOCK resource at this time%'
OR [vchMessage] LIKE '%Autogrow of file%in database%cancelled or timed out after%'
OR [vchMessage] LIKE '% is full%'
OR [vchMessage] LIKE '% blocking processes%'
)
AND [vchMessage] NOT LIKE '%\ERRORLOG%'
AND [vchMessage] NOT LIKE '%Attempting to cycle errorlog%'
AND [vchMessage] NOT LIKE '%Errorlog has been reinitialized.%'
AND [vchMessage] NOT LIKE '%found 0 errors and repaired 0 errors.%'
AND [vchMessage] NOT LIKE '%without errors%'
AND [vchMessage] NOT LIKE '%This is an informational message%'
AND [vchMessage] NOT LIKE '%WARNING:%Failed to reserve contiguous memory%'
AND [vchMessage] NOT LIKE '%The error log has been reinitialized%'
AND [vchMessage] NOT LIKE '%Setting database option ANSI_WARNINGS%'
AND [vchMessage] NOT LIKE '%Error: 15457, Severity: 0, State: 1%'
AND [vchMessage] <> 'Error: 18456, Severity: 14, State: 16.'

Conclusion

The package now collects from each instance the current status of each database and last two days of error log messages. This is still just start the of what this type of package can do. In an upcoming article I will be providing the full version of this package along with some sample reports.

Resources

Resources:

SQL_Overview_Part2.zip
Total article views: 17567 | Views in the last 30 days: 49
 
Related Articles
BLOG

SQL Server – How to change SQL Server ERRORLOG location

By default SQL Server ERRORLOG is stored in "C:\Program Files\Microsoft SQL Server\InstanceFolder\MS...

FORUM

error when creating the link server

error when creating the link server

FORUM

Error creating a Linked server

Error creating a Linked server from SQL Server 2005 to SQL Server 2000

FORUM

Errorlog

reading sp_readerrorlog

FORUM

Create server side trace.

Cannot create server side trace - error 12

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones