How to use SHRINKFILE

I was bouncing around a few of my SQL servers today, and found one of my system drives was below 300mb. Hmmmm can’t be good for business, so I dug a bit deeper. Turns out I had an .ldf file that was nearly 14gbs, ouch! All because the database recovery model was set to FULL, and we were only performing full backups nightly. To remedy the problem is actually very easy.

Backup up the log first:
BACKUP LOG MODEL WITH TRUNCATE_ONLY

Then run this shrink command:
ALTER DATABASE databasename SET RECOVERY SIMPLE
GO
USE databasename
GO
DBCC SHRINKFILE(‘databasename’, 2)

GO

The number 2 represents the target size you want the database in megabytes.

In my case I went from nearly 14GB to only 6.62MB, it gets as close to your target number as possible.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s