When Will Your Database Run Out Of Space?

One of the more forgotten aspects of Pharos Systems Blueprint Enterprise and Uniprint management is, ironically, one of its most important: The Database. Without it, both solutions are broken and battered. This blog entry focuses on database space. All of those 1s and 0s that make up our Very Important Information have to be stored in files on a hard disk, and if we don't pay attention, pretty soon--and when we least want it to happen--there isn't any place to put those 1s and 0s any more. Either the database file, or the disk it sits on, gets full. While there are a host of really good monitoring systems out there, many of us do not have one of them because they can be expensive or difficult to configure...or if we have something, it's very tactical: restarting stopped services, for example.

Capacity Planning: Figuring Out How Long Until You Need More Space

The vast majority of databases out there are growing and not shrinking. Some databases are hosted by generous organizations that provide all of the storage that they could ever need. The rest of them have to be prepared to run out of space.

Why care? Well, if ignored, you deal with an emergency out-of-space situation, rather than managing a disk space change order after a six-month – or better, one year – space usage warning. The former could put you on the unemployment line; the latter could put you behind the wheel of that new Tesla (or, at the very least, encourage sustained employment).

In order to know when a given database may run out of space, you need to know a few facts. For each file group of the database, what are these values?

• The present size of its files.

• The amount of free space within its files.

• The growth increment of the file, and the maximum file size (if any).

• The free space on disk to grow those files.

• How rapidly each file group grows, best expressed in megabytes per day.

Capturing Usage Metrics

Major monitoring tool vendors capture the necessary data listed above. That makes it quite easy since the data are already captured, and a table can be created to capture daily size information. For those who don’t have a monitoring tool, it is still possible to capture the data in a SQL job. First, you need to have a database and table where you can collect the information you need. Here is a sample CREATE script:

CREATE TABLE [dbo].[DBGrowthHistory](
  [ID] [bigint] IDENTITY(1,1) NOT NULL,
  [TimeCollected] [datetime] NOT NULL,
  [DBServerName] [nvarchar](128) NOT NULL,
  [DBName] [nvarchar](128) NOT NULL,
  [FileGroup] [sysname] NOT NULL,
  [FileLogicalName] [sysname] NOT NULL,
  [OSFileName] [nvarchar](260) NOT NULL,
  [TotalMB] [decimal](15, 2) NOT NULL,
  [FreeMB] [decimal](15, 2) NOT NULL,
  [GrowthIncrementMB] [decimal](15, 2) NOT NULL,
  [GrowthIncrementsRemaining] [decimal](15, 2) NOT NULL,
  [MaxFileSizeMB] [decimal](15, 2) NOT NULL,
  [VolumeFreeSpaceMB] [int] NOT NULL,

CONSTRAINT [PK_DBGrowthHistory] PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]

Once the table is built, the following query can populate the table with useful data:

SELECT 
 CONVERT(datetime,CONVERT(date,GETDATE())) as [TimeCollected] 
,@@Servername as [DBServerName] 
,db_name() as [DBName] 
,b.groupname AS [FileGroup] 
, a.Name as [FileLogicalName] 
,[Filename] as [OSFileName] 
,CONVERT (Decimal(15,2),(a.Size/128.000)) as [TotalMB] 
,CONVERT (Decimal(15,2),((a.Size-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)) AS [FreeMB] 
,CASE 
WHEN a.growth < 128 THEN 
CONVERT (Decimal(15,2),((a.size/128)*(a.growth*.01))) 
ELSE 
CONVERT (Decimal(15,2),(a.growth/128.00)) 
END [GrowthIncrementMB] 
,CASE 
WHEN a.growth < 128 AND a.maxsize > 0 THEN 
CONVERT (Decimal(15,2),((a.maxsize-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)/((a.size/128)*(a.growth*.01))) 
WHEN a.growth < 128 AND a.maxsize < 0 THEN 
CONVERT (Decimal(15,2),(((dovs.available_bytes/1048576.00)-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)/((a.size/128)*(a.growth*.01))) 
WHEN a.growth > 101 AND a.maxsize > 0 THEN 
CONVERT (Decimal(15,2),((a.maxsize-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)/(a.growth/128)) 
WHEN a.growth > 101 AND a.maxsize <0 THEN 
CONVERT (Decimal(15,2),(((dovs.available_bytes/1048576.00)-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)/(a.growth/128)) 
END [GrowthIncrementsRemaining] 
,CASE 
WHEN a.maxsize < 0 THEN 
CONVERT (Decimal(15,2),(dovs.available_bytes/1048576.00)) 
WHEN a.maxsize > 0 THEN 
CONVERT (Decimal(15,2),(a.maxsize/128.00)) 
END [MaxFileSizeMB] 
, CONVERT(INT,dovs.available_bytes/1048576.0) AS [VolumeFreeSpaceMB] 
FROM dbo.sysfiles a (NOLOCK) 
JOIN sysfilegroups b (NOLOCK) 
ON a.groupid = b.groupid 
JOIN msdb.sys.master_files c 
ON a.filename = c.physical_name 
CROSS APPLY sys.dm_os_volume_stats(c.database_id,c.file_id) dovs 
ORDER BY b.groupname; 

The above query only runs in the database for which you desire to collect statistics, which isn't very helpful. You can easily prepend this query with an “INSERT” and “sp_MSforeachdb” in order to sweep all database stats for a SQL Server instance into the table:

sp_MSforeachdb 'Use [?]; 
INSERT INTO psbprint.dbo.DBGrowthHistory 
SELECT 
 CONVERT(datetime,CONVERT(date,GETDATE())) as [TimeCollected] 
,@@Servername as [DBServerName] 
,db_name() as [DBName] 
,b.groupname AS [FileGroup] 
, a.Name as [FileLogicalName] 
,[Filename] as [OSFileName] 
,CONVERT (Decimal(15,2),(a.Size/128.000)) as [TotalMB] 
,CONVERT (Decimal(15,2),((a.Size-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)) AS [FreeMB] 
,CASE 
WHEN a.growth < 128 THEN 
CONVERT (Decimal(15,2),((a.size/128)*(a.growth*.01))) 
ELSE 
CONVERT (Decimal(15,2),(a.growth/128.00)) 
END [GrowthIncrementMB] 
,CASE 
WHEN a.growth < 128 AND a.maxsize > 0 THEN 
CONVERT (Decimal(15,2),((a.maxsize-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)/((a.size/128)*(a.growth*.01))) 
WHEN a.growth < 128 AND a.maxsize < 0 THEN 
CONVERT (Decimal(15,2),(((dovs.available_bytes/1048576.00)-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)/((a.size/128)*(a.growth*.01))) 
WHEN a.growth > 101 AND a.maxsize > 0 THEN 
CONVERT (Decimal(15,2),((a.maxsize-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)/(a.growth/128)) 
WHEN a.growth > 101 AND a.maxsize <0 THEN 
CONVERT (Decimal(15,2),(((dovs.available_bytes/1048576.00)-FILEPROPERTY( a.Name,''SpaceUsed''))/128.000)/(a.growth/128)) 
END [GrowthIncrementsRemaining] 
,CASE 
WHEN a.maxsize < 0 THEN 
CONVERT (Decimal(15,2),(dovs.available_bytes/1048576.00)) 
WHEN a.maxsize > 0 THEN 
CONVERT (Decimal(15,2),(a.maxsize/128.00)) 
END [MaxFileSizeMB] 
, CONVERT(INT,dovs.available_bytes/1048576.0) AS [VolumeFreeSpaceMB] 
FROM dbo.sysfiles a (NOLOCK) 
JOIN sysfilegroups b (NOLOCK) 
ON a.groupid = b.groupid 
JOIN msdb.sys.master_files c 
ON a.filename = c.physical_name 
CROSS APPLY sys.dm_os_volume_stats(c.database_id,c.file_id) dovs 
ORDER BY b.groupname; 
 ' 

Some notes about this query:

• It handles both “In Percent” and “In Megabytes” growth options.

• It handles both “Restricted” and “Unrestricted” file growth; however, unrestricted will just show the remaining disk/volume free space as [MaxFileSizeMB], so it should match [VolumeFreeSpaceMB].

• If the file growth is unrestricted (a bad practice), the [GrowthIncrementsRemaining] value will be very high if there is considerable free volume space available. It will also be nearly impossible to predict how soon it will be before space on the volume runs out.

• The sys.dm_os_volume_stats function is used instead of xp_fixeddrives because many SQL Server configurations utilize volume mount points to access SAN storage, rather than direct-mapping LUNs (Logical Unit Number; how the “size” of a disk is determined) as disks in the operating system. xp_fixeddrives will not show free space included volume mount points. This function is only present in SQL Server 2008R2 Service Pack 1 and newer SQL Server versions.

Creating the Report

Armed with the data, it is now time to calculate! The equation is pretty straightforward:

Days Remaining = (Growth Increment * Number of Growths remaining) / (Growth per Day [averaged])

The lowest number of days remaining from all file groups is how long you have until you either:

1. Increase your Maximum database size, or

2. Get more storage, or

3. Do both 1 & 2

The only value that the DBGrowthHistory table does not carry internally is the Average Growth Per Day. If the additional data storage rate is consistent, you can look at the average of the [GrowthIncrementsRemeaining] field over just a few days and get a suitable average. Otherwise, you may need to wait a month or so before the Average function returns anything useful. A query based on the current data and the equation is:

SELECT DBName AS [ Database ]
       , CONVERT ( INT ,(((GrowthIncrementMB* MIN (GrowthIncrementsRemaining)))/ AVG (GrowthIncrementsRemaining))) AS [Days Remaining]
       , MIN (VolumeFreeSpace) AS [ Free Disk (MB)]
FROM DBGrowthHistory
GROUP BY DBName,GrowthIncrementMB
ORDER BY [Days Remaining]

This serves as the basis for the reporting and alerting mechanism to ensure that the database keeps doing its job. The results of the query can be pushed into some other tool, or wrap it around other logic such that if [Days Remaining] is less than 365 (or 180, 90, etc.) the alarms start going off.

Things That Will Destroy Growing Room

Several things can get in the way of the Perfect Plan:

Multiple files on a drive: The chances that you have a 500GB volume with just one 30,000MB (29.29GB) file are, in a normal production environment, remote. It is more likely that a single volume will have multiple database files. In that event, you have to look at two options:

• Using a calculation to split the space between the files; or

• Setting a maximum file size for each database file.

Setting a maximum file size is a best practice. Assigning maximum file size means growth can be easily monitored and an accurate prediction of “days-till-full” can be made. This means an impending-doom scenario can be confidently avoided - if the reported metric is used in day-to-day management.

Mixed files on a drive: This is the real problem scenario. If the drive is used for non-SQL items (e.g., backups, or [ugh] file shares), the availability of space is unpredictable. What solution then? Wild guess? I think not; we are professionals here. Assuming that continued database operation trumps somebody’s files, the best recommendation is to set the “Initial” file sizes of the database’s files to something that already fills the disk, and then set the maximum file size so that it cannot grow (in other words: the maximum file size is equal to current file size). That way, other disk space hogs cannot grab the disk from you, and your database growth predictions will be all the more accurate.

Database Configuration Best Practices

Avoid the following database configuration options for continued success in database availability:

Growth by Percentage: No two growths in a file are likely to be the same. Calculating how many growths are left would leave Stephen Hawking in tears. Moreover, in a growing scenario, each growth is larger than the previous, raising the chance of a growth that is too large for the remaining disk. Always use a fixed number of megabytes as a growth factor. Most vendors that supply database build scripts will specify percentage growth simply because the product doesn’t know how much (or how little) the site will end up using the application. Monitor database growth over several days and then determine how much, in megabytes, to set database growth.

Unrestricted Maximum File Size: A 1TB volume on a SQL Server seems to be a lot of space. But if there are 10 databases’ files that are housed there, with each one having unlimited growth potential, it can only take one messed up stored procedure to bring down 10 databases. Again, like Percentage Growth, most vendors that supply database build scripts will also specify unrestricted growth for the database file for the same reasons Percent Growth was used. Monitor database growth and determine what the maximum size for the database file should be, based on the site’s (and application’s) data retention requirements.

Mixed files on a drive: This is being repeated intentionally. If there is any way to avoid using the same drive for database data files and any other type of file (including transaction log files, database backup files, and SSIS package files), do it! With both DAS (Direct-Attached Storage) and SAN (Storage Area Network) storage systems, there are performance benefits to separating file types across drives. It does no good to assign a growth maximum that can never be reached because other files are in the way. Note that doing this may make it difficult to refresh a lower (test or acceptance) environment with production data, because the restored file sizes would be too large.

Not Checking Frequently: Not only should you calculate the days-left-to-full of every major production database, you should look at it regularly. By that I mean every single workday. If some extraordinary event occurs that sucks up a whole pile of space, you need to know. What if someone puts a file onto your SQL drive without your knowledge? "Hey! I found 570GB to hold the movies I downloaded from BitTorrent in violation of Company AUP! The DBA won't know because "No worries, there is plenty of space!" can quickly turn into "Could not allocate space for object 'whatever' in database 'PorkChopSandwiches' because the 'PRIMARY' filegroup is full..." Properly calculating days left would show you any kind of impact like that and give you an opportunity to act before the error arises.