SQLServerCentral Article

SQL Overview SSIS Package III - Full Package

,

This article will cover both the setting up of the full SQL Overview package and creating some reports. Part I & Part II provided instructions on how to create several tasks in this package. These same techniques were used in creating the seven collection tasks contained in this package. In this article, I provide the full package along with instructions on setting it up to run each morning. The basis for this package comes from the article SQL Server Integration Services by Rodney Landrum.

Reports in this article are simple but provide the framework for adding additional reports. For now, these reports consist of reporting any error messages that were encountered while running the package and listing any SQL Server instances that have been restarted since the last execution of the job.

Believe it or not I find this second report interesting. Starting work on Monday and seeing all the instances restarted over the weekend was shocking at first. I eventually learned these were planned outages that applied security patches from Microsoft. It was nice of them not to tell me about it so I wouldn't worry about them over the weekend. But when Monday comes and the new business week is starting, I need to know this in case of problems. So now, I know even when I am not told.

Setting up the Package

This package was created to run locally on a SQL Server Default Instance. If you wish to execute the package from a remote client, the variable SRV_Conn and connections need to be updated (see readme.txt in attached file). The instructions below for setting up this package will be simple and to the point.

  1. Pick the server the package will be running from.
  2. SQL Agent service on the host must be running with a windows id that has been granted access to all the instances that will be accessed.
  3. Create the database SQL_Overview if it does not exist.
  4. Save the attached SQL_Overview_Part3.zip and extract it.
  5. Extract SQL_Overview_Part3_package.zip to a directory on the server. For example D:\DBA_Scripts\SQL_Overview
  6. Execute SQL_Overview_Part3_Tables.sql. This script creates tables in the database SQL_Overview. No tables are dropped; they are only created if they don't exist.
  7. You will get warning about 'IX_Database_Info' having a maximum length of 1288 bytes. You can ignore it. If it causes errors during the load just drop the index. Its only purpose was for performance, which is still very good without it.
  8. Execute SQL_Overview_Part3_Tempdb.sql. This script creates all the TEMPDB tables that will be used by this package.
  9. Enable OLE Automation on each SQL Server 2005 instance. It is used to collect disk space.
  10. Review the contents of the table SSIS_ServerList. On the first run, try only a few servers. To skip a server set the column Skip_SQL_Overview to true. If this table is empty, you can load it using this script:

    USE SQL_Overview

    GO

    CREATE TABLE #Server ( [Server] [varchar](128) )

    INSERT INTO #Server

    EXEC xp_cmdshell 'sqlcmd /Lc'

    INSERT INTO SSIS_ServerList ([Server])

    SELECT [Server] FROM #Server WHERE [Server] IS NOT NULL

    DROP TABLE #Server

  11. Open the solution on the server using SQL Server Business Intelligence Development Studio.
  12. Any Yellow or Red icons need to be fixed. Just place the cursor over the mark. A message will popup explaining the problem. Take the appropriate action to fix it. You might just need to confirm the column mappings. TEMPDB tables are deleted each time the instance is restarted and need to be recreated on the host instance. After fixing any problems save it.
  13. Run the package by pressing F5
  14. Now just watch it go.
  15. Any errors encountered will be capture in the table SSIS_Errors. Please review its contents when any errors are encountered. Also, look at the messages on the Progress Tab in the Studio.

Outline of Tasks

This package includes many tasks. Here is a description of each of them.

  • Truncate Tables - cleans out tables before they are loaded by this package
  • Populate ADO Variable- reads the SSIS_ServerList table and passes the server\instance names to each container using the variable SQL_RS
  • Collect Backup History - gathers each database's backup history for the last 31 days
  • Collect Database Info - gathers information about the databases and the files allocated to each of them.
  • Collect Database Status - gathers the status of each Database
  • Collect Disk Space - gathers the space usage on each drive. For this task to work on SQL Server 2005 instances, OLE Automation needs to be enabled on each instance.
  • Collect ErrorLog - gathers the last 2 days of SQL Server Error Logs
  • Collect Job Info - gathers setup information about the SQL Agent jobs from each instance
  • Collect Job History - gathers the SQL Agent job history for the last 7 days
  • Collect Server Info - gathers SQL Server instance information

SQL Overview Tables

  • Backup_History - database backup history for last 7 days
  • Database_Info - information about the databases and their files
  • Database_Status - database status
  • Disk_Space - space usage for each drive on each server
  • ErrorLog - last 2 days of SQL Server Error Logs
  • Job_History - SQL Agent Job history for the last 7 days
  • Jobs - information about the SQL Agent jobs from each instance
  • SSIS_ServerList - contains a list of server\instance to be processed by this package
  • SSIS_Errors - any errors this package encountered during its last execution
  • SQL_Servers - SQL Server instance information

Reports

It's time to make some reports after the data has been collected from all the instances. The reports in this article are simple but provide the framework for adding additional reports. The SQL Overview package is expected to be executed each morning with the reports being created immediately afterwards. Tables and views used exclusively by reports will be assigned to the rep schema to make them easily identifiable.

The sample reports included in this article consist of error messages captured during the execution of the SQL Overview package. The other lists SQL Server instances restarted since that last execution of this job. These reports are created using three jobs executed through SQL Agent. The first job will execute the SQL Overview package. The other two jobs are the reporting jobs. Remember SQL Agent service must be running with a windows id that has been granted access to all the instances that will be accessed.

Database Mail is used to send the reports. If Database Mail is not already setup, please do so. For instructions on setting this up refer to the article Intro to Database Mail in SQL 2005 by Corey Bunch.

My instructions for setting up these jobs in SQL Agent will be brief. It will mostly consist of listing the parameter values for the job and the job steps. These values will be blue except for the SQL that has been "prettified" using the Simple-talk Prettifier website.

Create Reporting Schema and Table

Create schema rep

This schema will be used to identify tables and views only used to create reports. In this article, there is only one table. There can easily be more, when additional reports are created.

USE [SQL_Overview]

GO

CREATE SCHEMA [rep] AUTHORIZATION [dbo]

Create table SQL_Overview_Last_Run_Date

This table is used to capture the datetime of the previous successful execution of the job "DBA-SQL Overview". The LastJobRun_date column is used by reports to identify changes that occurred since the last job execution.

CREATE TABLE [rep].[SQL_Overview_Last_Run_Date](

[LastJobRun_date] [datetime] NOT NULL

) ON [PRIMARY]

Create job DBA-SQL Overview

This job's primary purpose is to execute the SSIS SQL Overview package and start the report jobs. Since the package uses tables in TEMPDB they are created before the package is executed. SSIS requires them to exist on the local instance before the package is executed. These tables are dropped every time the instance is restarted. So it's just safer to have the job create them every time.

Job Name: DBA-SQL Overview

Job Owner: Windows SERVICE Login ID for the SQL Server Instance

Step1

Save the datetime of the last successful execution of this job.
Step name: s01-Job Last Run Datetime

Type: Transact-SQL script (T-SQL)

Database: SQL_Overview

Command:

-- Get Job Name

SET nocount ON

DECLARE @JobName sysname

SELECT @JobName = [name] FROM msdb.dbo.sysjobs

WHERE Job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))

PRINT '>'+@JobName+'<'

-- Get the date of the last successful execution of this job

DECLARE @dtLastRun datetime

DECLARE @chLastRun CHAR(26)

SELECT TOP 1 @dtLastRun =

CAST(CAST(run_date AS CHAR(8)) + ' ' + -- Convert run_date to DateTime data type

STUFF(STUFF( -- Insert : into Time

RIGHT('000000' + -- Add leading Zeros

CAST(run_time AS VARCHAR(6)) ,6) , 3, 0, ':'), 6, 0, ':') AS datetime)

FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobhistory B

WHERE A.job_id = B.job_id AND B.run_status = 1

AND A.name = @JobName

ORDER BY 1 DESC

SELECT 'Job Last Run Date', @dtLastRun

IF @dtLastRun IS NULL SET @dtLastRun = '1900-01-01'

SET @chLastRun = CONVERT(CHAR(26),@dtLastRun)

PRINT 'Job Last Run Date = ' + @chLastRun

DELETE FROM [rep].[SQL_Overview_Last_Run_Date]

INSERT INTO [rep].[SQL_Overview_Last_Run_Date]

([LastJobRun_date])

VALUES

(@chLastRun)

Step2

Recreate the TEMPDB tables on the Local Instance
Step name: s02-Create TempDB Tables

Type: Transact-SQL script (T-SQL)

Database: Master

Command:

IF EXISTS

(SELECT *

FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID(N'[tempdb].[dbo].[HoldforEachDB]')

)

DROP TABLE [tempdb].[dbo].[HoldforEachDB]

GO

CREATE TABLE [tempdb].[dbo].[HoldforEachDB]

(

[Server] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[InstanceName] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DatabaseName] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[FileId] smallint NOT NULL,

[GroupId] smallint NOT NULL,

[Size] real NOT NULL,

[MaxSize] real NOT NULL,

[Growth] real NOT NULL,

[FileStatus] [int] NOT NULL,

[LogicalName] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Filename] NVARCHAR(260) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[DatabaseStatus] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Updateability] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[User_Access] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Recovery] NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SpaceUsed(MB)] real ,

[UsageType] VARCHAR (6),

[Owner] NVARCHAR(128) NULL,

[CreateDate] datetime NULL,

[CmptLevel] tinyint NULL,

[DateChecked] datetime

)

ON [PRIMARY]

GO

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](1024) NULL,

[ID] [int] NULL

) ON [PRIMARY]

GO

IF EXISTS

(SELECT *

FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID(N'[tempdb].[dbo].[HoldDiskSpace]')

)

DROP TABLE [tempdb].[dbo].[HoldDiskSpace]

GO

CREATE TABLE [tempdb].[dbo].[HoldDiskSpace] (

[Server] NVARCHAR(128),

Drive CHAR(1) PRIMARY KEY,

[Free(MB)] INT NULL,

[Total(MB)] INT NULL,

[Free(%)] tinyint NULL,

[DateChecked] [datetime] NOT NULL CONSTRAINT [DF_HoldDiskSpace_Date Checked] DEFAULT (GETDATE()),

)

GO

IF EXISTS (SELECT *

FROM tempdb.dbo.sysobjects

WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Hold_XP_MSVER]')

)

DROP TABLE [tempdb].[dbo].[Hold_XP_MSVER]

GO

CREATE TABLE [tempdb].[dbo].[Hold_XP_MSVER]

(

IDX INT NULL,

C_NAME VARCHAR(100) NULL,

INT_VALUE FLOAT NULL,

C_VALUE VARCHAR(128) NULL)

Step3

Execute the SQL Overview Package
Step name: s03-Collect SQL Server Information

Type: SQL Server Integration Services Package

General Tab

Package Source: File System

Package: D:\DBA_Scripts\SqlOverview\SQL_Overview\SQL_Overview_Package.dtsx

Step4

Start First Reporting Job
Step name: s04-Start Job - Report Errors from Package Execution

Type: Transact-SQL script (T-SQL)

Database: Master

Command:

EXEC msdb.dbo.sp_start_job N'DBA-SQL Overview - Report Table Refresh Errors'

Step5

Start Second Reporting Job
Step name: s05-Start Job - Report Instances Recently Started
Type: Transact-SQL script (T-SQL)
Database: Master
Command:

EXEC msdb.dbo.sp_start_job N'DBA-SQL Overview - Report Instances Recently Started'

Reporting Jobs

Job Name: DBA-SQL Overview - Report Table Refresh Errors

This job reports any errors that occurred during the latest execution of the SQL Overview package.

The SQL command will need to be customized. Change the email account '??@domain.com' to a valid e-mail account.

Step name: Step1
Type: Transact-SQL script (T-SQL)
Database: SQL_Overview
Command:

SET nocount ON

--

DECLARE @Subject VARCHAR (100)

SET @Subject='SQL Server - SQL Overview Table Refresh Errors'

DECLARE @Count AS INT

SELECT @Count=COUNT(*) FROM SSIS_Errors

PRINT @Count

IF @Count > 0

BEGIN

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =

N'<table border="1">' +

N'<tr>' +

N'<th>Server</th>' +

N'<th>TaskName</th>' +

N'<th>ErrorCode</th>' +

N'<th>ErrorDescription</th>' +

N'</tr>' +

CAST ( ( SELECT td=[server],''

,td=[TaskName],''

,td=[ErrorCode],''

,td=[ErrorDescription],''

FROM [SSIS_Errors]

ORDER BY 1,2,3,4

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

@recipients = '??@domain.com',

@subject = @Subject,

@body = @tableHTML,

@body_format = 'HTML' ;

END

Remember: Change ??@domain.com to a valid e-mail account.

Job Name: DBA-SQL Overview - Report Instances Recently Started

Reports any instances that have restarted since the previous successful execution of the job "DBA-SQL Overview". This report will always be empty during its first execution because the table [rep].[SQL_Overview_Last_Run_Date] is empty. This table is not populated until the second run of "DBA-SQL Overview".

The SQL command will need to be customized. Change the email account '??@domain.com' to a valid e-mail account.

Step name: Step1
Type: Transact-SQL script (T-SQL)
Database: SQL_Overview
Command:

SET nocount ON

--

DECLARE @dtLastRun datetime

SELECT @dtLastRun = LastJobRun_date FROM [rep].SQL_Overview_Last_Run_Date

SELECT @dtLastRun = ISNULL (@dtLastRun ,DATEADD(dd,-2,CURRENT_TIMESTAMP))

PRINT 'Last Run Date = ' + CONVERT(CHAR(26),@dtLastRun)

--

DECLARE @Subject VARCHAR (100)

SET @Subject='SQL Server - Instances Recently Started'

DECLARE @Count AS INT

SELECT @Count=COUNT(*) FROM [dbo].[SQL_Servers]

WHERE [InstanceLastStartDate] > @dtLastRun

PRINT @Count

IF @Count > 0

BEGIN

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =

N'<table border="1">' +

N'<tr>' +

N'<th>Server</th>' +

N'<th>InstanceLastStartDate</th>' +

N'<th>ProductVersion</th>' +

N'<th>WindowsVersion</th>' +

N'<th>ProcessorCount</th>' +

N'<th>PhysicalMemory</th>' +

N'</tr>' +

CAST ( (

SELECT td=[Server],''

,td=[InstanceLastStartDate],''

,td=[ProductVersion],''

,td=[WindowsVersion],''

,td=[ProcessorCount],''

,td=[PhysicalMemory],''

FROM [dbo].[SQL_Servers]

WHERE [InstanceLastStartDate] > @dtLastRun

ORDER BY 1,2

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default',

@recipients = '??@domain.com',

@subject = @Subject,

@body = @tableHTML,

@body_format = 'HTML' ;

END

Remember: Change ??@domain.com to a valid e-mail account.

Test Jobs

To test the jobs submit "DBA-SQL Overview". It will in turn submit the report jobs.

The Report Last Instances Report maybe empty the first time. To test this report, add a row to the table [rep].[SQL_Overview_Last_Run_Date]. Make the datetime column at least a year old. Then just submit this job.

Conclusion

That is it. You now have the full package along with some reports. This SQL Overview package collects a large assortment of information that can be used in a variety of ways. The reports I presented in this article are simple. Their purpose was to provide the framework for setting up reports that can be generated from the collected data. I have other reports that I may include in a future article. For now, you have all the basic tools to create your own set of reports.

This package will not work on SQL Server 7 because not all the components it uses are there. You can create a scaled down version just for SQL Server 7 instances.

Resources

Resources

Rate

4.94 (17)

You rated this post out of 5. Change rating

Share

Share

Rate

4.94 (17)

You rated this post out of 5. Change rating