Shrink log databases on SQL Server

Ran into a problem where my SP2013  standalone development machine was running out of disk space. The reason turned out to be 61GBs of database log files.

Here is small script for shrinking SQL Server databases that start with ‘SP_‘ or ‘WSS_‘. Very handy on a development machine. Make sure that the databases you want to shrink are in ‘Recovery Model: Simple’. You find this setting on the property page of the database.

Warning: Do not use this in production! For production machines contact your DBA and decide on a maintenance, backup and recovery plan that suits the needs of your company. Use script at your own risk.

The script

use [master]

set nocount on

declare @name sysname,
    @file_id int,
    @sqlcmd varchar(max)

DECLARE db_mps_simple_logs_cur CURSOR FOR
select, mf.file_id
from sys.databases d
join sys.master_files mf
    on d.database_id = mf.database_id
where (d.[name] like 'WSS_%' OR d.[name] like 'SP_%')
    and d.recovery_model = 3 --simple only
    and mf.type = 1 --0 is data, 1 is log

open db_mps_simple_logs_cur
fetch next from db_mps_simple_logs_cur into @name, @file_id

while @@fetch_status = 0 begin

    set @sqlcmd = 'use ' + QUOTENAME(@name) + '; checkpoint; dbcc shrinkfile ( ' + cast(@file_id as varchar) + ', 1024 );'
    exec ( @sqlcmd )

    fetch next from db_mps_simple_logs_cur into @name, @file_id

close db_mps_simple_logs_cur
deallocate db_mps_simple_logs_cur

One Comment

  1. judson High school

    July 28, 2014

    Hi, just wanted to mention, I loved thuis article.
    It was helpful. Keep on posting!

Comments are closed. ×

Share Your Thoughts