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.
use [master] set nocount on declare @name sysname, @file_id int, @sqlcmd varchar(max) DECLARE db_mps_simple_logs_cur CURSOR FOR select d.name, 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 end close db_mps_simple_logs_cur deallocate db_mps_simple_logs_cur go