By Erin Stellato, 2013/01/23

Capturing Baselines on SQL Server: Where's My Space?

http://www.sqlservercentral.com/articles/baselines/96059/

Storage is an integral part of a SQL Server installation. We need to make sure that the capacity and performance of the storage subsystem match our requirements in terms of the volume of data we need to store, over time, and the speed with which we need to access it.

In this article, we'll tackle the topic of monitoring disk space usage. By tracking how much is in use and how much is still available, over time we'll have the data we need for better capacity planning, and can ensure that a database won't ever run out of disk space. It is easy to find out how much space is free on a drive right now, but trending that information requires foresight and a bit of data capture. Fortunately, both are easy to do in SQL Server, as we'll see here.

In order to work through this article, you'll need to create a database in which baseline information can be stored. My previous post, Back to Basics: Capturing Baselines on Production SQL Servers, created one called BaselineData, and all the scripts included in this post assume this database exists, and that you're using SQL Server 2005 or higher.

The Data to Capture

In order to understand how much space databases are using, and how much is free, we need to capture file information for data and log files from either the sys.database_files or sys.master_files catalog views. If you're using SQL Server 2012, then you can also get file size information for all databases from the sys.dm_db_file_space_usage DMV. However, for SQL Server 2005 through SQL Server 2008R2, it provides this information only for the tempdbdatabase.

Use of sys.master_files is preferable for capturing file information, as it is a system-wide view and it means we don't need to roll through each database, querying sys.database_files to retrieve information. However, neither of these views list how much of the file is in use, only the size of the file.

It is a good practice to size database files in advance, and grow them a manually, as necessary. This helps avoid the performance hit when files grow automatically, particularly during periods when the server is busy. As such, it is essential the DBA knows how much space is in use within a database file in order to determine when to grow a file manually, and therefore prevent auto-growth.

File growth and instant file initialization

Enabling instant file initialization reduces the cost of growth events for data files. However, log files cannot utilize this feature.

We can capture space usage within a file using the FILEPROPERTY function, but FILEPROPERTY is database-scoped, so when we call the function and pass in the logical file name, from sys.master_files, we get the usage information for only the local database. Ultimately, we end up querying sys.database_files for each database

....