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.
- Pick the server the package will be running from.
- 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.
- Create the database SQL_Overview if it does not exist.
- Save the attached SQL_Overview_Part3.zip and extract it.
- Extract SQL_Overview_Part3_package.zip to a directory on the server. For example D:\DBA_Scripts\SQL_Overview
- 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.
- 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.
- Execute SQL_Overview_Part3_Tempdb.sql. This script creates all the TEMPDB tables that will be used by this package.
- Enable OLE Automation on each SQL Server 2005 instance. It is used to collect disk space.
- 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_OverviewGO
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
- Open the solution on the server using SQL Server Business Intelligence Development Studio.
- 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.
- Run the package by pressing F5
- Now just watch it go.
- 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
USE [SQL_Overview]
GO
CREATE SCHEMA [rep] AUTHORIZATION [dbo]
Create table SQL_Overview_Last_Run_Date
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
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)
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
Type: SQL Server Integration Services Package
General Tab
Package Source: File System
Package: D:\DBA_Scripts\SqlOverview\SQL_Overview\SQL_Overview_Package.dtsx
Type: Transact-SQL script (T-SQL)
Database: Master
Command:
EXEC msdb.dbo.sp_start_job N'DBA-SQL Overview - Report Table Refresh Errors'
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
The SQL command will need to be customized. Change the email account '??@domain.com' to a valid e-mail account.
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
??@domain.com to a valid e-mail account.Job Name: DBA-SQL Overview - Report Instances Recently Started
[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.
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
- Get the code below.
- SQL Server Integration Services by Rodney Landrum
