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.
SQLRook 