How to shrink MS SQL Server Files

When a server with SQL Server runs very low in free disk space, SQL and related apps will likely fail to operate properly. Freeing disk space is the obvious solution including deleting unused files, emptying Recycle Bin and shrinking SQL databases.

However, SQL database shrinking in a environment with low disk space might not work at all or take a very long time to process.

Here are 2 approaches to Shrink MS SQL Server Files on a Server with Very Limited Disk Space:

SHRINKING SQL SERVER DATABASES WITH LARGE LOG FILES

Step 1

Run the following on SSMS on the database to shrink:

Right-click Database → New Query

DBCC FREESYSTEMCACHE ('ALL')
GO

Approach 1

If the log file is too big and is not shrinking with the SQL regular procedure, then the procedure below is easy and super fast:

1. Detach the database
2. Rename the database’s ldf file to something different. Eventually this file will be deleted.
3. Reattach the database, but first, remove the line highlighted below which refers to the ldf file. Upon attachment, SQL will create a new and small ldf file.
4. Click Ok.
5. After successfully re-attaching the file with a new small log file, go ahead and delete the old large ldf file.

Approach 2

On the dialog below:

1. Set “File Type” = Log.
2. If “Available free space” is high, click Ok and see if this will free plenty of disk space. If you manage to free plenty of HD space, try the same procedure from “File Type” = Data.
3. If “Available free space” is low, for each “File Type”, do the following: a) Select “Reorganize pages before releasing”. b) IMPORTANT: “Shrink file to” must be set to a size bigger than (Currently allocated space) – (available free HD space). In other words, the file’s size to be shrunk must be smaller than the free HD space. For example, if the Data file is 100000MB and the free disk space is 500MB (100000-500 = 99500MB), thus set this parameter to something like 997000MB.
4. Repeat the procedure above until “Available free space” is mostly gone.
5. Repeat procedure for both “File Types”.