SQL Overview SSIS Package - Retrieving SQL Error Log

SQL Overview SSIS Package - 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
•
o Name: Truncate ErrorLog
o Connection: to QASRV.SQL_Overview
o SQL Statement: TRUNCATE Table ErrorLog
o 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"
2.
1. Connect the "Collect Database Status" container to this object with the green line/arrow
2. Settings
3.
1. General
2.
1. Name: Collect ErrorLog
3. Collection
4.
1. Change Enumerator to Foreach ADO enumerator
2. Select ADO object source variable User::SQL_RS
5. Variable Mapping
6.
1. Add User::SRV_Conn
7. Click OK
4. Right Click on this Container
5. Select Properties
6. Set MaximumErrorCount to 999
3. 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.
4.
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 1. Add "Data Flow Task" to the "Collect Error Log" container 2. 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 3. 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. 4. Select the Data Flow Tab or double click on icon for the "Data Flow task" 5. Add "OLE DB Source" from toolbox 6. 1. Double Click Icon 2. OLE DB Connection manager: MultiServer 3. Change Data access mode to SQL Command 4. SQL Command Text: SELECT [Server] ,[dtMessage] ,[SPID] ,[vchMessage] ,[ID] FROM [tempdb].[dbo].[ErrorLog] 5. Click Preview to verify the SQL and then click close when done 6. Click OK 7. Add "OLE DB Destination" from toolbox 8. o Connect the "OLE DB Source" to this element with the green line/arrow o Double click on the Icon for OLE DB Destination and make the following changes o 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.