Database File Free Space

If a data file runs out of free space, it attempts to grow (assuming autogrowth is enabled). Even with an effective autogrowth strategy, it is prudent to manually grow files since this can be done in a more controlled manner (e.g., at a suitable time).

Conversely, if a data file has excessive free space, this is potentially a waste of storage, which could be reclaimed by shrinking the data file.

Suggested Action

šŸ’” Select 'Suggested Script' for an automatically generated script.

Aim to maintain 5-10% free space in all data files and grow them manually.

How to Increase the Size of SQL Server Data and Log Files

USE [master]
ALTER DATABASE [WhippetWorks] MODIFY FILE ( NAME = N'WhippetWorks', SIZE = 128MB )
ALTER DATABASE [WhippetWorks] MODIFY FILE ( NAME = N'WhippetWorks_log', SIZE = 128MB )

Further Reading

Increase the Size of a Database | Microsoft Docs

ALTER DATABASE (Transact-SQL) File and Filegroup Options | Microsoft Docs