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.

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
3. 1. General
  1. 1. Name: Collect
      ErrorLog
  2. Collection
  3. 1. Change Enumerator to Foreach ADO enumerator
    2. Select ADO object source variable User::SQL_RS
  4. Variable Mapping
  5. 1. Add User::SRV_Conn
  6. Click OK
4. Right Click on this Container
5. Select Properties
6. Set MaximumErrorCount to 999
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
2. - 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
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
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.
Select the Data Flow Tab or double click on icon for the
"Data Flow task"
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
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

SQL_Overview_Part2.zip
SQL Server Integration Services by
Rodney Landrum

SQL Overview SSIS Package II - Retrieving SQL Error Log