In the previous two articles in this series on monitoring a large number of servers from a centralized location using Data Transformation Services (DTS) (see Part 1 and Part 2), I explained how to set up a report to ensure that backups are happening as per the backup policy and how to monitor SQL Server error logs. In this final article, I will explain how to monitor space utilization of databases on all the servers. The articles in this series explain how to monitor three elements of SQL servers:
- Backup SLA (Service Level Agreement) Report — to find any database that has not been backed up in the last 24 hours.
- Errorlog check — to report errors in SQL Server errorlog for any of servers in an enterprise.
- Space monitoring — to report databases that have less than 10% free space
Space Monitoring of SQL Servers
Most people keep the data files set to auto grow. The auto grow option is a huge performance drag if the database starts growing in the middle of the business day. The space monitoring report helps alleviate the issue. You can schedule the space monitoring report to run once a day. The report lists the databases that have less than 10 percent free space. You can then schedule the space increase during off-peak hours. You can use the same data to track database growth rate. The growth statistics can be extrapolated to find out the space requirements for next quarter or next year. The DTS package can run the queries on each server and bring the space utilization data to the central repository.
The process uses DTS Package to consolidate the data from all the SQL Servers to a central server. It then runs the queries against the central repository to find any database that has less than 10 percent free space. According to your requirement, you can set the threshold percentage. Following are four parts of the reporting process:
- Run the “sp_filespacestats” on each server to get the space utilization per file and save this data in the table “filespacestats” in “dbamaint” on the local server. (View the create scripts for the stored procedure and the table.)
- Transfer the records from each table to the central repository.
- Run the query to find the databases that have less than the predefined free space percentage.
- Send an e-mail report.
For this article, the central repository server is “SQLSRVCNTREP” and the database with the central repository is “dbamaint.” For this setup, you need to create the “dbamaint” database on all the servers that need to be monitored. A table to hold space statistics is created on each server.
How Does It Work?
The main script is db_space_monitor.bat. (Click here to get the text of the script). This script has four parts as described below:
- The bef_space_mon.sql will be executed. The purpose of this SQL is to clear the data for the current date. This ensures that even if the space monitoring report is executed twice in one day, it will show the latest results. (Click here to see the text of the scripts.) isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60 -w 3000 -i%DBASQL%bef_space_mon.sql > %DBAOUT%bef_space_mon.out
- In this section, the DTS package is executed for gathering the information from different servers. This DTS Package will populate the respective table in the central repository by collecting the data from each of the servers. dtsrun /S SQLSRVCNTREP /N space_mon /E >>%DBAOUT%db_space_monitor.log
- This section involves reporting. The SQL that does this job is db_space_monitor.sql. The data collected in the previous step is queried to get space statistics per database. This SQL adds the used space and free space for all the data files of each database. (Click here to see the text of the scripts.) isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60 -w 3000 -i%DBASQL%db_space_monitor.sql > %DBAOUT%db_space_monitor.out
- Send the report in an e-mail to all the intended recipients. You can use any SMTP client to send the e-mail. “Sendmail” and “blat” are among a few. Blat is freeware and you can download it from http://www.blat.net/. blat %DBAOUT%db_space_monitor.out -t Neeraj.nagpal@neeraj.com -s “Space Monitoring Report” -server timeline -f Neeraj.nagpal@neeraj.com