SQL Server Disk Usage Statistics

• Download disk-status-experiment.zip - 11.5 KB
• Download DBDiskStatusExperiment.sql.zip - 1.4 KB

Introduction

This is an example to check the SQL server disk and data file usage statistics.

Background

For a truly large and fast growing database, disk space is an issue that concerns many DBAs. At the same time, it is a difficult problem.

• In SQL servers, there are both data files and log files;
• The data files can be in multiple file groups;
• The data files can be placed on different disks;
• Each data file can be configured differently for initial size, growth rate, and max size;
• Each data file may have its physical size, but mostly likely its physical size is not fully used, so it can take more data before it needs to grow.

Due to the complexities, an easy to use monitoring tool is needed to check the disk and file usages. In this example, I will introduce a couple of simple SQL scripts to check the statistics. I will also include a JAVA Spring MVC program to display the information on the web browser. Since we are talking about SQL server, it is ideal to use a .NET program to do the UI work, but I noticed that the Visual Studio 2015 community created a large package folder with the solution, and a lot of the dependencies are actually not used. In order not to flush the Code-Project's hard drive, I decided to create a JAVA Spring MVC project. The JAVA project is much smaller. In any case, the important part of the example is the SQL scripts, if you are not interested in the web project, you can simply skip it.

The Example Database

The SQL server used in this example is the SQL server Express 2014 on my local computer. If you want to try this example, I think you can also use earlier versions of SQL server, but I do not have a guarantee. To create the example database, you can run the following script.

USE [master]
GO

-- Create a login user
IF EXISTS
    (SELECT * FROM sys.server_principals WHERE name = N'TestUser')
DROP LOGIN [TestUser]
GO
    
EXEC sp_addlogin @loginame = 'TestUser', @passwd  = 'Password123';
GO
    
-- Create the database
IF EXISTS 
    (SELECT name FROM sys.databases 
        WHERE name = N'DBDiskStatusExperiment')
BEGIN
    ALTER DATABASE [DBDiskStatusExperiment] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE
    DROP DATABASE [DBDiskStatusExperiment]
END
GO

DECLARE @A VARCHAR(100)
CREATE DATABASE [DBDiskStatusExperiment]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = 'DBDiskStatusExperiment', 
    FILENAME =
        'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBDiskStatusExperiment.mdf',
    SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DBDiskStatusExperiment_log',
    FILENAME =
        'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\DBDiskStatusExperiment_log.ldf', 
    SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

-- Add additional database files for test purpose
ALTER DATABASE DBDiskStatusExperiment ADD FILEGROUP TestFileGroup;
GO

ALTER DATABASE DBDiskStatusExperiment 
ADD FILE 
( NAME = 'TestDataFile-1',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDataFile-1.ndf',
    SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB
),
(NAME = 'TestDataFile-2',
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDataFile-2.ndf',
    SIZE = 5MB, MAXSIZE = UNLIMITED, FILEGROWTH = 5MB
)
TO FILEGROUP TestFileGroup
GO

USE DBDiskStatusExperiment
GO

EXEC sp_grantdbaccess 'TestUser'
GO

CREATE PROCEDURE dbo.GetDatabaseDiskStatistics AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DBName VARCHAR(255) = DB_NAME()
    DECLARE @DiskStatistics TABLE(drive VARCHAR(10), size INT, free_space INT)

    INSERT INTO @DiskStatistics
    SELECT volume_mount_point, 
        total_bytes/1048576 as Size_in_MB,
        available_bytes/1048576 as Free_in_MB
    FROM sys.master_files AS f CROSS APPLY 
        sys.dm_os_volume_stats(f.database_id, f.file_id)
    WHERE DB_NAME(F.database_id) = @DBName
    GROUP BY volume_mount_point, total_bytes, available_bytes

    SELECT * FROM @DiskStatistics

    SELECT ISNULL(FG.name, 'N/A') FileGroup, D.drive, F.name, physical_name,
        F.size/128 file_size, fileproperty(FILE_NAME(F.file_id), 'SpaceUsed')/128 used_size,
        CASE WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(3)) + '%'
            ELSE CAST(growth/128 AS VARCHAR(100)) END growth,
        CASE WHEN max_size < 0 THEN 'UNLIMITED'
            ELSE CAST(max_size/128 AS VARCHAR(100)) END max_size
    FROM sys.master_files F
        LEFT JOIN sys.filegroups FG ON F.data_space_id = FG.data_space_id
        LEFT JOIN @DiskStatistics D ON F.physical_name LIKE D.drive + '%'
    WHERE DB_NAME(database_id) = @DBName
    ORDER BY FG.name

END
GO

GRANT EXECUTE ON OBJECT::[dbo].[GetDatabaseDiskStatistics] TO TestUser
GO

USE [master]
GRANT VIEW SERVER STATE TO TestUser
GRANT VIEW ANY DEFINITION TO TestUser
GO

• You will need the administrative permission to run the script;
• The script added the data files and the log file for the database. Please check that the folders to put the files do exist. If the folders do not exist, you will fail to run the script;
• My computer has only one drive. To make things more interesting, if you have more drives to use, you can try to add more data files to different drives.

Upon the successful run of the script, we created the [DBDiskStatusExperiment] database.

• For the purpose of this example, I did not add any tables to the database for simplicity;
• The [GetDatabaseDiskStatistics] stored procedure will be used by the example UI application to display the disk and file usage statistics;
• The [TestUser] will be used by the UI application to access the SQL server. It has been granted the permission to run the [GetDatabaseDiskStatistics] stored procedure and the permission to read the disk and file usage information.

If you right click the database in the management studio -> Properties -> Files, you can see the files for this database.

Check the Disk and Database File Information

The [xp_fixeddrives] Stored Procedure

To check the free disk space available for the database, we can use the [xp_fixeddrives] stored procedure.

While it is a simple handy stored procedure, it does not provide us the total disk space. To get the total disk space, we can use the [dm_os_volume_stats] table valued function.

The [dm_os_volume_stats] Function

The [dm_os_volume_stats] function requires the database id and the file id to get the information. It gives us both the total disk space and the available free space. If you check out the disk property from your operating system, you can see that the numbers are correct.

Because your disk space is constantly changing, so the time difference between your running of the [dm_os_volume_stats] and your opening of the disk property window can give your some difference on the numbers. In my experiment, they match exactly. To get the information for each database file, you can use the [master_files] view.

The [master_files] View

• The [data_space_id] column is the id of the file group that the file belongs to;
• The [type_desc] column is the type of the file. If it is "ROWS", it is a data file. If it is "LOG", it is a log file;
• The [physical_name] column is the operating system file name and location;
• The [size] column is the current size of the file;
• The [max_size] column is the max size of the file. When it is "-1", it means that the file can grow unlimited;
• The [growth] column is the growth rate of the file;
• The [is_percent_growth] column tells us if the growth rate is in percentage. If it is 1, the growth rate is in percentage;
• The unit of the sizes is page. In SQL server, 1 MB is equivalent to 128 pages.

A database file may not use all of its occupied space, which means that the data file can take more data before it has to grow. To find the actual space used, we can use the [fileproperty] function.

The [fileproperty] Function

Again, the unit of the "used_space" returned by the [fileproperty] function is page.

Summary

In summary, I created the [GetDatabaseDiskStatistics] stored procedure.

CREATE PROCEDURE dbo.GetDatabaseDiskStatistics AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DBName VARCHAR(255) = DB_NAME()
    DECLARE @DiskStatistics TABLE(drive VARCHAR(10), size INT, free_space INT)

    INSERT INTO @DiskStatistics
    SELECT volume_mount_point, 
        total_bytes/1048576 as Size_in_MB,
        available_bytes/1048576 as Free_in_MB
    FROM sys.master_files AS f CROSS APPLY 
        sys.dm_os_volume_stats(f.database_id, f.file_id)
    WHERE DB_NAME(F.database_id) = @DBName
    GROUP BY volume_mount_point, total_bytes, available_bytes

    SELECT * FROM @DiskStatistics

    SELECT ISNULL(FG.name, 'N/A') FileGroup, D.drive, F.name, physical_name,
        F.size/128 file_size, fileproperty(FILE_NAME(F.file_id), 'SpaceUsed')/128 used_size,
        CASE WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(3)) + '%'
            ELSE CAST(growth/128 AS VARCHAR(100)) END growth,
        CASE WHEN max_size < 0 THEN 'UNLIMITED'
            ELSE CAST(max_size/128 AS VARCHAR(100)) END max_size
    FROM sys.master_files F
        LEFT JOIN sys.filegroups FG ON F.data_space_id = FG.data_space_id
        LEFT JOIN @DiskStatistics D ON F.physical_name LIKE D.drive + '%'
    WHERE DB_NAME(database_id) = @DBName
    ORDER BY FG.name

END
GO

Running the [GetDatabaseDiskStatistics], we have the following result, which should have the important information about the disk space and the file space for the database. Of course, you can always make changes to it to get more information of your interest. If you have more than one drives, you can put more files on different drives to see how the stored procedure works.

Display the Information on the UI

In order to display the information on the UI, I created a JAVA Spring MVC application. If you are not interested in this subject, you can simply skip it.

It is a Maven project. If you are not familiar with Maven, you can take a look at this link. To create this web application, the following are the minimum Maven dependencies.

<dependencies>         
        <!-- Sevlet jars for compilation -->
        <dependency>
                <groupId>org.apache.tomcat</groupId>
                <artifactId>tomcat-servlet-api</artifactId>
                <version>${tomcat.version}</version>
                <scope>provided</scope>
        </dependency>
        
        <!-- Minimal dependencies for Spring MVC -->
        <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-core</artifactId>
                <version>${spring.version}</version>
        </dependency>
        <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-web</artifactId>
                <version>${spring.version}</version>
        </dependency>
        <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-webmvc</artifactId>
                <version>${spring.version}</version>
        </dependency>
        
        <dependency>
        <groupId>net.sourceforge.jtds</groupId>
        <artifactId>jtds</artifactId>
        <version>${jtds.version}</version>
        <scope>runtime</scope>
    </dependency>
</dependencies>

The MVC view models are implemented in the "DatabaseFile.java" and "DatabaseDrive.java" files.

package com.song.model;
    
public class DatabaseFile {
    
    private String fileGroup;
    private String drive;
    private String name;
    private String physicalName;
    private Long size;
    private Long usedSize;
    private String growth;
    private String maxSize;
    
    public String getFileGroup() { return fileGroup; }
    public void setFileGroup(String v) { this.fileGroup = v; }
    public String getDrive() { return drive; }
    public void setDrive(String v) { this.drive = v; }
    public String getName() { return name; }
    public void setName(String v) { this.name = v; }
    public String getPhysicalName() { return physicalName; }
    public void setPhysicalName(String v) { this.physicalName = v; }
    public Long getSize() { return size; }
    public void setSize(Long v) { this.size = v; }
    public Long getUsedSize() { return usedSize; }
    public void setUsedSize(Long v) { this.usedSize = v; }
    public String getGrowth() { return growth; }
    public void setGrowth(String v) { this.growth = v; }
    public String getMaxSize() { return maxSize; }
    public void setMaxSize(String v) { this.maxSize = v; }
}

package com.song.model;
    
import java.util.ArrayList;
import java.util.List;
    
public class DatabaseDrive {
    public DatabaseDrive()
    {
        files = new ArrayList<DatabaseFile>();
    }
    
    private String drive;
    private Long size;
    private Long freeSpace;
    private List<DatabaseFile> files;
    
    public String getDrive() { return drive; }
    public void setDrive(String v) { this.drive = v; }
    public Long getSize() { return size; }
    public void setSize(Long v) { this.size = v; }
    public Long getFreeSpace() { return freeSpace; }
    public void setFreeSpace(Long v) { this.freeSpace = v; }
    public List<DatabaseFile> getFiles() { return files; }
}

To load the information to the view models, I created the "DBConnector" and "DAO" classes.

package com.song.database;
    
import java.sql.Connection;
import java.sql.DriverManager;
    
import javax.naming.InitialContext;
    
public class DBConnector {
    private final static class ConnInformation {
        public String DBUrl;
        public String UserName;
        public String Password;
        
        private ConnInformation(String dBUrl,
                String userName, String password) {
            DBUrl = dBUrl;
            UserName = userName;
            Password = password;
        }
        
        private static ConnInformation instance = null;
        private static synchronized ConnInformation getInstance() throws Throwable{
            if (instance == null) {
                try {
                    InitialContext initialContext = new javax.naming.InitialContext();
                    String contextString = (String) initialContext
                            .lookup("java:comp/env/DBConnectionInformation");
                    
                    String[] info = contextString.split("\\|");
                    instance = new ConnInformation(info[0], info[1], info[2]);
                } catch (Throwable e) {
                    instance = null;
                    throw e;
                }
            }
            
            return instance;
        }
    }
    
    public static Connection getAConnection(boolean autocommit) throws Throwable {
        ConnInformation connInfo = ConnInformation.getInstance();
        
        Connection connection = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            connection = DriverManager
                    .getConnection(connInfo.DBUrl, connInfo.UserName, connInfo.Password);
            
            connection.setAutoCommit(autocommit);
        } catch (Throwable e) {throw e;}
        
        return connection;
    }
    
    public static Connection getAConnection() throws Throwable {        
        return getAConnection(true);
    }
}

package com.song.database;
    
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.LinkedHashMap;
    
import com.song.model.DatabaseDrive;
import com.song.model.DatabaseFile;
    
public class DAO {
    
    public HashMap<String, DatabaseDrive> getDiskStatistics() throws Throwable {
        HashMap<String, DatabaseDrive> drives = new LinkedHashMap<String, DatabaseDrive>();
    
        String sql = "{call GetDatabaseDiskStatistics}";
        try (Connection con = DBConnector.getAConnection()) {
            try (CallableStatement stmt = con.prepareCall(sql)) {
    
                stmt.execute();
                ResultSet rs = stmt.getResultSet();
                while (rs.next()) {
                    DatabaseDrive drive = new DatabaseDrive();
                    String driveLetter = rs.getString("drive");
                    drive.setDrive(driveLetter);
                    drive.setSize(rs.getLong("size"));
                    drive.setFreeSpace(rs.getLong("free_space"));
    
                    drives.put(driveLetter, drive);
                }
    
                stmt.getMoreResults();
                rs = stmt.getResultSet();
                while (rs.next()) {
                    DatabaseFile file = new DatabaseFile();
                    String driveLetter = rs.getString("drive");
                    file.setDrive(driveLetter);
                    file.setFileGroup(rs.getString("FileGroup"));
                    file.setName(rs.getString("name"));
                    file.setPhysicalName(rs.getString("physical_name"));
                    file.setSize(rs.getLong("file_size"));
                    file.setUsedSize(rs.getLong("used_size"));
                    file.setMaxSize(rs.getString("max_size"));
                    file.setGrowth(rs.getString("growth"));
    
                    drives.get(driveLetter).getFiles().add(file);
                }
    
            }
        }
    
        return drives;
    }
}

The information to connect to the database is configured in the "web.xml" file.

<env-entry>  
        <env-entry-name>DBConnectionInformation</env-entry-name>
        <env-entry-type>java.lang.String</env-entry-type>
        <env-entry-value>
            jdbc:jtds:sqlserver://localhost/DBDiskStatusExperiment|TestUser|Password123
        </env-entry-value>    
</env-entry>

The MVC controller and the MVC view are implemented in the "HomeController.java" and "example.jsp" files.

package com.song.web.controller;
    
import java.util.HashMap;
    
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;
    
import com.song.database.DAO;
import com.song.model.DatabaseDrive;
    
@Controller
@RequestMapping("/home")
public class HomeController {
    
    @RequestMapping(value = "/example", method = RequestMethod.GET)
    public ModelAndView mainpage() throws Throwable {
    
        DAO dao = new DAO();
        HashMap<String, DatabaseDrive> drives = dao.getDiskStatistics();
    
        ModelAndView modelView = new ModelAndView();
        modelView.addObject("data", drives);
        modelView.setViewName("home/example.jsp");
    
        return modelView;
    }
}

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Database disk status</title>
<link rel="stylesheet" type="text/css"
    href="<%=baseUrl%>styles/app.css" />
<body>
<%for(DatabaseDrive drive : drives.values()) { %>
<div>
<span><%=drive.getDrive() %> - 
    total size: <%=drive.getSize() %> - 
    free space: <%=drive.getFreeSpace() %> -
    ALL unit: MB</span>
<table>
    <thead>
        <tr>
            <td>File Group</td>
            <td>Name</td>
            <td>Size</td>
            <td>Used Size</td>
            <td>Growth</td>
            <td>Max Size</td>
        </tr>
    </thead>
    <tbody>
<%for(DatabaseFile file: drive.getFiles()) { %>
    <tr>
        <td><%=file.getFileGroup() %></td>
        <td><%=file.getName() %></td>
        <td><%=file.getSize() %></td>
        <td><%=file.getUsedSize() %></td>
        <td><%=file.getGrowth() %></td>
        <td><%=file.getMaxSize() %></td>
    </tr>
<%} %>
    </tbody>
</table>
</div>
<%} %>
</body>
</html>

Run the MVC Application

You can run the example MVC application by creating a "war" file by issuing the "mvn clean install" command and deploying it to a servlet container. You can also import the example project into Eclipse and run it from there. If you are not familiar with importing Maven projects, you can take a look at this link.

When you load the web page, you will see that both the disk and database file information is displayed according to the stored procedure [GetDatabaseDiskStatistics]. It would be nice if you can add database files to multiple disks to see how it works. Since I have only 1 drive, I am OK with what I see now. I have tested this application with JAVA 8, Eclipse Luna, and Tomcat 7.

Points of Interest

• This is an example to check the SQL server disk and data file usage statistics;
• This example comes with a SQL script and a Spring MVC application. If you are not interested in the MVC application, you can simply ignore it;
• I hope you like my postings and I hope this article can be of some help one way or the other.

History

First Revision - 9/24/2015.