Maintaining a database is an important job of the DBA role. One of the many maintenance tasks is ensuring that the disk does not fill up and your files are able to grow over time.

Additionally, you don’t want the files to grow in massive amounts. 1024 MB (1 GB) is usually a good growth for spinning media. Anything more and you may be waiting a while for the allocation, which means your queries are stuck until in is finished. Keep in mind this is general guidance and you’ll want to figure out what works best for the hardware you have.

How can I see the free space of my disks?

Validating your volume free space should be automated with alerting from applications like SCOM. If you want to check the drives manually, there are several ways to accomplish the task.

1. Use PERFMON to review % Free Space. Be sure to check the Logical Disk object if you have mount points.
2. Use WMI
3. Use Powershell
4. Query the SYS.DM_OS_VOLUME_STATS DMF.

For this blog we’ll demonstrate using the query method.

Finding information about my volumes

SELECT DISTINCT vs.volume_mount_point,

vs.volume_id,

(vs.total_bytes/1024)/1024 [Total MB],

(vs.available_bytes/1024)/1024 as [Total Available MB],
cast(cast((1.0 *((vs.available_bytes/1024)/1024)) / ((vs.total_bytes/1024)/1024) as decimal(9,4))*100 as decimal (9,2)) as [Percent Available]
FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) vs

Results:

How can we find potential growth problems?

Let’s say we want to search for any data or file that will exceed the available free space after two growths. We’d want to calculate the next growth for each file and then compare against the free space divided by 2.

SELECT mf.database_id, name, mf.file_id,
cast(case when is_percent_growth = 1 then ((size*8) * (growth/100.0))/1024 else (growth*8)/1024 end as decimal(18,4)) as [Next growth in MB],

(vs.available_bytes/1024)/1024 as [Total Available MB]
FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) vs
WHERE cast(case when is_percent_growth = 1 then ((size*8) * (growth/100.0))/1024 else (growth*8)/1024 end as decimal(18,4)) > ((vs.available_bytes/1024)/1024)/2

Results:

“Hello, Looks like we may have a problem.”

Summary

By using the DMV’s we can configure alerts around growth and many other scenarios to help us better manage our database instances.

As always, I hope that you found this post useful and thank you for taking the time to read my ramblings.

Be sure to check out my other posts at:

SQL Server Central & SQLTechBlog.com

Also be sure to follow me on LinkedIn and Twitter!