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.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |