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.
💡 Aireforge Advisor can automatically generate a script for this.
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] GO ALTER DATABASE [WhippetWorks] MODIFY FILE ( NAME = N'WhippetWorks', SIZE = 128MB ) GO ALTER DATABASE [WhippetWorks] MODIFY FILE ( NAME = N'WhippetWorks_log', SIZE = 128MB ) GO
Increase the Size of a Database | Microsoft Docs
ALTER DATABASE (Transact-SQL) File and Filegroup Options | Microsoft Docs