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?
- 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
gouse dbamaint
goif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[filespacestats]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[filespacestats]
GOCREATE 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]
GOALTER TABLE [dbo].[filespacestats] ADD
CONSTRAINT [DF__filespace__repor__1332DBDC] DEFAULT (getdate()) FOR [report_date]
GOThis would create the database “dbamaint” on the server and the table “filespacestats” in “dbamaint.”
- 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.
- 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.
- 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