Script to Shrink Database

Script to Shrink Database.

You need to use Database Console Commands of SQL Server to get back the free space.

DBCC ShrinkDatabase : Shrink all files from database.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, 10)
Go

It will shrink 90% free space and remains 10% free for database files.


Use Master
Go

DBCC ShrinkDatabase(AdventureWorks2012, TruncateOnly)
Go

It will shrink data and log files to the last allocated extent, while you use TruncateOnly the percentage parameter will be ignored.

DBCC ShrinkFile : Shrink only single file.

You have a choice to which data file or log file you want to shrink.

–> “AdventureWorks_MDF” and “AdventureWorks_LDF” is a logical name of data files and log files.


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_MDF,10)
Go


Use AdventureWorks2012
Go

DBCC ShrinkFile(AdventureWorks_LDF,10)
Go

Regards,
Nirav Gajjar