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

1. Select the Destination tab.
   
   
   
   Select the table name as "dbamaint.dbo.db_all_spacestats."
2. Select the Transformations tab.
   
   
   
   Click OK to proceed.
3. Now we need to create an "Execute SQL Task."
   
   
   
   On the General tab, give the Description as <servername>_sql. In the Existing connection drop down box, select the newly created server name. In the SQL Statement box, copy and paste the set of statements given with the scripts. (Click here to see the text of the scripts.)
   
   
   
   Once these statements have been copied to the SQL Statement box, click on OK to proceed.
4. Now, select the newly created connection and the "Execute SQL Task."
   
   
   
   In the Workflow option, click "On Success" after selecting both the newly created connection and "Execute SQL Task." This will create a link between the Execute SQL Task and the newly created connection as shown above.
   
   The server has been successfully added to the space monitoring report. Next time the report runs, it will monitor the databases for this server.
   
   The space statistics collected every day for this monitoring report are stored in one table. This information is invaluable for reporting the growth rate of the databases. The growth rate can be used to estimate your disk space requirements for next quarter or next year. (View the query for generating the growth rate statistics.) If you like, you can setup a Web Assistant Wizard to generate monthly growth statistics information.