Main menu

SQL server: shrink database files

I have just notice today that i am getting low disk space and after some digging i found that one of my database log consuming 20GB of disk space. MSSQL Database having default recovery model of full. What does it mean, that every transaction is logged into log files, older your log is more it will consume your disk space. It’s really good for production environment where you want point in recovery but in development environment  it’s just cause issues with disk space.

Let’s have a quick view how to change recovery model and shrink database files. With this simple TSQL query we will change recovery model to simple and shrink database file.

USE [master]
GO
ALTER DATABASE [your_db] SET RECOVERY SIMPLE WITH NO_WAIT
GO

USE [your_db]
GO
DBCC SHRINKFILE (N'your_db_log' , 0, TRUNCATEONLY)
GO

You can also shrink database files using MSSQL management studio. Navigate to database which you want to shrink, select files.

Shrink DB Log File

Select file type logs and press ok.

Shrink DB Log File 02

FacebookTwitterGoogle+RSS