Automatic Data Collection: Database Files and Logical Drives in MS SQL Server

Introduction

It is important for a database administrator to know when there is no space on a disk. Thus, it is better to automate the process in order for them not to do it manually on each server.

In this article, I am going to describe how to implement automatic daily data collection about logical drives and database files.

Solution

Algorithm:

1. Create tables of data storage:
1.1. for database files:

1.2. for logical drives:

In addition, you need to fill in a table with logical drives in advance in the following manner:
Server name – volume label

2. create a necessary view for data collection about database files:

Here the sys.master_files system view is used.

3. Create a stored procedure that returns information on a logical drive:

To get a detailed information on this procedure, refer to the following article: Disk Space in T-SQL.

4. Create a stored procedure for data collection:

4.1. for database files:

4.2. for logical drives:

5. Create views for data output:

5.1. for database files:

5.2. for logical disks:

6. Create a task in the SQL Server Agent and run it once a day:

7. Collect all the data output from servers. You may do it using the SQL Server Agent, for example.

8. Create a stored procedure for generating a report and sending it to administrators. Since it is possible to implement it in different ways, I will consider it on this particular example:

This stored procedure generates an HTML-report about logical drives that either have free disk space less than 15%, or free space decreases over 5% a day. The latter shows a strange activity of records which means that someone stores too much information on this disk very often. It might happen for the following reasons:

  1. It is a time to extend a disk;
  2. It is necessary to delete unused files on a logical drive;
  3. Erase and reduce log files, as well as files of information and other tables.

Solution

In this article, I have analyzed an example of implementing a system of daily automatic data collection about local drives and database files. This information allows finding out in advance which disk has less free space, as well as what database files grow drastically. It allows avoiding a case when there is no space on a disk and finding out a reason why a process takes much space on a disk.

Also read:

Automatic Data Collection of Database Schema Changes in MS SQL Server

Automatic Data Collection about Completed Tasks in MS SQL Server 

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.