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

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);

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

** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software