Skip to main content
Skip table of contents

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:


Step 1

Run the following on SSMS on the database to shrink:

Right-click Database → New Query


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.

_SQL Server1

Approach 2

On the dialog below:

_SQL Server2

  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”.

_SQL Server

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.