Reduce SQL Server Datafile Size

---

Disclaimer

Click the images in the article below to toggle between thumbnail and full-size. Enlarge all | Reduce all

---

Introduction

This article shows how to view space utilization statistics for a SQL Server database and reduce the size of its data file and log file to reclaim unused space if it has been over-allocated.

Check for Unused Space

Here's a script you can run in a query window in Management Studio. It will show the space utilization for a single database, both the data file (.mdf) and the log file (.ldf). You will need to change the database name in the script below from "HypEPMRepository" to the database you want to check.

Create Table ##temp
(
    DatabaseName sysname,
    NameOfFile sysname,
    FileNameAndPath nvarchar(500),
    Allocated_Size_MB decimal (18,2),
    FreeSpace_MB decimal (18,2)
)   
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName,  NameOfFile, FileNameAndPath, Allocated_Size_MB, FreeSpace_MB)
    Select DB_NAME() As [DatabaseName], Name, physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) 
		As Allocated_Size_MB,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) 
        As FreeSpace_MB
    From sys.database_files
    Where DB_NAME() = "HypEPMRepository"
'
Select * From ##temp
drop table ##temp                	

Resize the Datafile

If you Google “SQL Server shrink files”, you will find articles entitled “Why You Should Not Shrink Your Data Files”, “Don’t Touch That Shrink Button”, and “Stop Shrinking Your Database Files. Seriously. Now.”.

Clearly there is some controversy around the topic of reducing the size of your database by shrinking its files, and there are good reasons for not shrinking your files. However, I find it convenient to shrink my files when I’m building a personal sandbox for the Oracle Hyperion EPM Suite. I don't always know how much space to allocate for databases that will be used as repositories in the Suite. Knowing that I will be able to recover unused space later, I allocate liberally when I create a database. Then, after the EPM Suite has been installed and configured I take a look at how much space has actually been used, add a generous amount of space for growth, and shrink the file accordingly.

Here are the commands to reduce the size of the both the data file and the log file. The file names (HypEPMRepository in the 1st command and HypEPMRepository_log in the 2nd command) are taken from the output of the query above, in the “NameOfFile” column.

USE HypEPMRepository;
GO
DBCC SHRINKFILE (HypEPMRepository, 300);
GO
DBCC SHRINKFILE (HypEPMRepository_log, 40);
GO
				

Final Remarks

This allows us to make generous initial allocations for our database files and then recover unused space once the database has been populated with tables and other objects.

-Tom Cockerline, February 2017