SQL Server Standard Reports – Disk Usage: [Database Name]

We’re in the first of the database-level reports in this series of the Standard Reports that ship with SQL Server Management Studio (SSMS). To get to these reports, navigate from the server object in Object Explorer, move down to the Databases object, and then right-click any database. From the menu that appears, select Reports, then Standard Reports, and then this one.

One of the most important factors in a database is the I/O subsystem. Whether you’re using a Storage Area Network (SAN), Just a bunch of disks (JBOD) or any other storage medium, you’re interested in two things: information about the storage system, and information about the storage transfer rates. You can find some of the transfer information in a few of the “Performance” reports I explained earlier, and we’ll see more in later reports.

This report, and the next few that follow, deal with the first question. You’ll get valuable information about how the data is laid out on the drive a some information about how much space is left, at least in some cases.

It’s important to understand how SQL Server uses files on the drive, especially the terminology. I won’t cover all that here, so if you’re not familiar with the file system in SQL Server, I suggest you check here and then refer to this report.

There are three main sections to this report: two tables and a couple of charts. Let’s take a quick look at each:

Column	Description
Total Space Usage	The total space on disk that this database is using. This does not include files from other databases, the SQL binaries and so on.
Data Files Space Usage	How much space the database files are using, not counting the transaction logs.
Transaction Log Space Usage	How much space the transaction logs are using.

The first section is followed by two pie charts, which show more detail on the allocation within the data and transaction logs. For the data, the space is split between data, indexes, space that is in a contiguous block but not allocated to the SQL Server database, and the space allocated to the SQL Server database but not yet filled with anything.

Following those graphs is another table, showing the same space information, this time by the filegroups assigned to the database.

Filegroups might be new to you, but they are an integral part of the SQL Server storage story. Filegroups are simply a logical name for a physical location on the storage system. Every database has at least the PRIMARY filegroup, which is where all objects you create are stored by default. You can create more filegroups, placing them on any locally visible drive. No, you can’t store SQL Server files on a share, but you can store them on any device physically attached to SQL Server, whether that’s a SAN, NAS, JBOD and so on.

By creating new filegroups, you can spread out the data, indexes and so on. You do this for performance, security, maintenance and so on. You can read more about filegroups here. Here are the columns for this section of the report:

Column	Description
Filegroup Name	The name of the filegroup where the files are stored
Logical File Name	The name of the file that is stored in the master database
Physical File Name	The name that the operating system uses for this file
Space Reserved	The space allocated to the filegroup. You can set this to automatically grow, but for better performance it’s best to pick a large, fixed size for the file.
Space Used	The amount of space taken by the data and logs in this filegroup