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_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
- 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
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
