Skip to main content

Drives used for SQL databases are running out of free space – Health Analyzer rule

Introduction

Health Analyzer does a great job warning you about possible problems on your platform, however it is extremely hard to stay on the good side (meaning it does not reward you with a red bar in Central Administration) without turning off a couple of rules.
I met with the Drives used for SQL databases are running out of free spacemessage a few times and the other day reading the official documentation and feeling a bit confused and disappointed I decided to check out what this is about under the hood.
 On technet:
“The databases have one or more files that exceed the available free disk drive space. If this happens, operations will fail. A disk drive should have enough free space to allow the largest database file to automatically grow to twice its size.”
Errr… so let’s say a 100 GB database mdf file is the biggest on my drive and because of this I need 100GB free disk space? Wrong!

Behind the scenes

So what does this rule checks on an hourly basis?
It checks all databases. It checks all files in these databases (mdf, ndf, ldf).
It compares the current size and “some” autogrowth value with the free space on that drive. It does not care about your free space in your database; if you have a 1 GB db and have 990 MB free space in it, it is still counted as 1 GB as current size.
Let’s break these down.
Free space of the drive: uses the [sys].[xp_fixeddrives] extended stored procedure.
[sys].[xp_fixeddrives] extended stored procedure
[sys].[xp_fixeddrives] extended stored procedure
Database file size: uses the [sys].[sp_helpfile] stored procedure.
[sys].[sp_helpfile] stored procedure result
[sys].[sp_helpfile] stored procedure result
Growth rate: comes from the same [sys].[sp_helpfile] stored procedure execution with the following twist:
  • if it is in percentage, the rule uses percentage/100 as a value
  • if it is fixed value (eg. 50 MB), the rules uses 20/100 as a value here
Putting it all together a database will trigger this rule if the free space is less than this value:
Autogrowth specified as percentage
Autogrowth specified as percentage
 and if the growth is specified as a fixed value rather than percentage:
With fixed growth
With fixed growth
So you are safe (at least according to this rule:) if all your databases files can grow with two times its growth percentage, if specified as percentage OR can grow 40% if autogrowth is specified as a fixed value; compared to current free space.
This is the same in SharePoint 2010 and SharePoint 2013

Examples

Example 1

I have the content database files on a 2GB partition. The files look like this:
Content database files properties
Content database files properties
My MDF’s size is 1GB and its autogrowth percentage is 100%. This leaves less than 1GB free on my 2GB partition, so let’s calculate:
1 GB * 100 / 100 * 2 = 2GB, I need at least 2GB free space, so it should trigger the rule and it actually does. No surprise here.
Let’s change the autogrowth value to 2 GB instead of the percentage:
Content database files properties
Content database files properties
Changing the value to 2 GB from 100% makes the alert disappear.
1 GB * 20 / 100 *2 = 0,4 GB, I need at least 400 MB free space and because I have (and no other file in this database violates these rules) I have no alert in SP.

You can run re-check with this rule if you go to Central Administration > Monitoring > Review Rule Definitions, selecting the rule and clicking on the Run Now button.
Run a health analyzer rule
Run a health analyzer rule
You always find the rule here even if it is not active.
Other option is to run with Reanalyze Now button on the error’s display form page if the error or warning is active.

Example 2

Your logfile size can trigger too!
Increased LDF size and 100% autogrowth value
Increased LDF size and 100% autogrowth value
In this case the increased LDF size and 100% growth rate cause that this database appeared as problematic according to the Drives used for SQL databases are running out of free space  rule.

Conclusion

The Drives used for SQL databases are running out of free space rule can be really useful for you, don’t turn it off, learn to tame it by knowing what cause this alert exactly in case of a database. Add more storage, set your autogrowth rates and most importantly don’t leave your database file settings on SQL Server default values.
In short, you need enough storage for your database files to be able to grow twice based on the percentage autogrowth, or by 40% if you specified autogrowth as a fixed value in MB or GB.

Comments

Post a Comment