Release free space of database

Created by QNE Software Sdn bhd, Modified on Tue, 28 Sep, 2021 at 5:09 PM by QNE Software Sdn bhd

Scenario 

How to release free space in database?

SQL Database file are like a box container that constantly grow bigger to store your data but it does not auto shrink after data removed from database.

By default, SQL database are set not to auto shrink to reduce the time and resource required during grow and shrink process take place because it will affect the database performance but there is time that database shrink is required like after data purge or transactions cleared then you will need to release free space of the database.

(Shrinking database will

Solution 

1. First, check available free space in database

2. Launch SSMS and connect to your database

https://support.qne.com.my/a/solutions/articles/81000385373 

3. Execute below query to find out Database’s “Current Size” and “Free Space”

SELECT DB_NAME() AS DbName, 

     name AS FileName, 

     type_desc,

     size/128.0 AS CurrentSizeMB,  

     size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files

WHERE type IN (0,1);

4. You will get a result like below.

 

5. In this example DB, my .mdf file size is 8,600MB and I have 2,798.31MB of free space after purging audit trail

6. Click New Query

7. Copy & paste below query

8. Replace [CLONED_DB] with your database name and execute query

USE [CLONED_DB]

GO

DBCC SHRINKDATABASE(N'CLONED_DB' )

GO

9. Wait patiently while SSMS executing the query, duration depend on the database’s size

10. Once below message are shown mean release free space process has completed

11. You can use Step No.3 query to check Database’s “Current Size” and “Free Space”

12. Now, you have successfully release free space in your database.

 

** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software


Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article