SQL Overview SSIS Package II - Retrieving SQL Error Log

,

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

Rate

4.88 (8)

Share

Share

Rate

4.88 (8)