Monitor Your Database Servers with DTS, Part 3: Space Monitoring of SQL Servers

A sample space monitoring report is shown below.

Report Generation Date
----------------------
Dec 13 2005 4:53PM

The following SQL Server databases have less than 10% free data space
------------------------------------------------------------------------

Server Name   Database   Total space(MB)   Free space(MB)   % Free space
-----------   --------   ---------------   --------------   ------------
Server1       dbamaint   2.0               0.1              5.000000
Server5       dbamaint   2.0               0.1              5.000000
Server10      gpdb       3000.0            145.1            4.836666
Server20      BWQ        42694.8           517.4            9.211857

How Do You Add a New Server to the Script?

1. Execute the following script on the server that has to be monitored.
   
   if not exists(select * from master..sysdatabases where name='dbamaint')
   create database dbamaint
   go
   alter database dbamaint
   set RECOVERY SIMPLE
   go
   
   use dbamaint
   go
   
   if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[filespacestats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[filespacestats]
   GO
   
   CREATE TABLE [dbo].[filespacestats] (
   [server_name] [sysname] NOT NULL ,
   [dbname] [sysname] NOT NULL ,
   [flag] [bit] NULL ,
   [Fileid] [tinyint] NULL ,
   [FileGroup] [tinyint] NULL ,
   [total_space] [decimal](7, 1) NULL ,
   [usedspace] [decimal](7, 1) NULL ,
   [freespace] [decimal](7, 1) NULL ,
   [freepct] [decimal](7, 1) NULL ,
   [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
   [FileName] [sysname] NULL ,
   [report_date] [datetime] NULL
   ) ON [PRIMARY]
   GO
   
   ALTER TABLE [dbo].[filespacestats] ADD
   CONSTRAINT [DF__filespace__repor__1332DBDC] DEFAULT (getdate()) FOR [report_date]
   GO
   
   This would create the database "dbamaint" on the server and the table "filespacestats" in "dbamaint."
2. Login to the SQLSRVCNTREP server and open SQL Server Enterprise Manager Console. Navigate to the Data Transformation Services (DTS) > Local Packages tab. Double click the SPACE_MON package to edit it. After opening the DTS package, create a connection to the target server.
   
   
3. Enter the details for the new server as shown below. Select the authentication method to connect to the database. Select the "dbamaint" database. Click on OK to proceed.
   
   
4. Now we need to create a "Transform Data task" as shown below.
   
   
   
   Once the icon is clicked, it prompts you to select the source server. Please select the newly created connection as the source server. Select the destination server as SQLSRVCNTREP. A link will be established between the two servers. Right click on the link and select Properties to see the Transform Data Task Properties as shown below.
   
   
   
   Select the SQL Query option and paste the following query to the SQL space provided there.
   
   select * from dbamaint.dbo.filespacestats order by flag ,report_date desc