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
3. Execute below query to find out Database’s “Current Size” and “Free Space”
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
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
DBCC SHRINKDATABASE(N'CLONED_DB' )
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