MSSQL Available Space alert: How to manually grow the database file with SSMS

Written by Claudio Kuenzler - 0 comments

Published on July 11th 2023 - Listed in Monitoring Databases Windows

---

When using MSSQL databases (hint: MSSQL also runs on Linux, you're welcome!), the Microsoft SQL server and its database(s) should be properly monitored. Consol Labs' check_mssql_health is a great and free monitoring plugin which does the job perfectly.

In this article a particular MSSQL database ran into a "available free space" warning. Here's how the database can be grown manually using Microsoft SQL Server Management Studio (SSMS).

Monitoring alert: Available free space is warning

When a database is created, a "fixed" database size needs to be defined. A database is (by default) split into two files: A database file (db) called PRIMARY and a LOG file (db_log).

Over time the tables within the database grow and the pre-defined database size is approaching. When properly monitoring the available space of databases (using the database-free check type), the monitoring kicks in and alerts:

WARNING - filegroup nlz::PRIMARY has 451.44MB free space left, database nlz has 451.44MB free space left, database nlz has 2097114.16MB free log space left

But I have AUTOGROWTH enabled!

Now you might raise your hand and vocalize a "but!". Yes, you most likely have AUTOGROWTH configured on that database. But why did it not kick in and increase the database already? 

According to this answer on ServerFault, the AUTOGROW feature only kicks in when the database uses 100% of its pre-defined limit. This threshold can not be adjusted - at least I have not found any documentation pointing to a AUTOGROWTH threshold setting.

Do we want to find out? Should we wait until the database has reached 100% space usage on a production database? Nah, I consider this too risky. Especially given my couple of minutes of experience with MSSQL.

Manually grow database in SSMS

Luckily the database files can also manually grown. The easiest way to do this is using Microsoft SQL Server Management Studio (SSMS). Right-click the affected database, select Properties. In the new window Database Properties, select "Files". Identify the database file (usually PRIMARY, look at the monitoring alert) and manually change/increase the "Size (MB)" column. Here I increased the size to 60GB:

This action takes place immediately, there's no further commit or apply to do.

Monitoring Recovery

Shortly after this, the monitoring check recovered and switched back to OK, now with over 10GB of available space for this database:

OK - database nlz has 10796.88MB free space left, database nlz has 2097090.30MB free log space left

Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.