forge

Recovering Space on Development Servers through Shrinking Logs and Setting Recovery to Simple

One of the problems that happens when you put together a new virtual machine for development is you have to make a decision about how big you make the hard drive. Too big and you chew up space and too small and you’re constantly fighting for space. Invariably if I use a development machine for too long it starts to run out of space – no matter how big I made the drive in the beginning. However, a lot of that space is just log files for the database. Every change in the database gets logged and since it’s a development machine I’m not backing it up. In fact, I never backup the data in a development machine. So what I really need to do is set the recovery mode for the databases to SIMPLE so SQL won’t make log files and then shrink the log files I already have. This isn’t hard it’s just tedious for the 20 or so databases that make up a SharePoint installation. So I started making a script and ultimately found one at http://codesnippets.joyent.com/posts/show/665 that I could tweak quickly to get what I wanted. Basically it was resetting the recovery mode to whatever it was when it started – and I didn’t want that. I wanted to stay in SIMPLE recovery to prevent the logs from chewing up space again. Here’s the script (with modifications):

— From http://codesnippets.joyent.com/posts/show/665 w/ modifications
declare @ssql nvarchar(4000)
set @ssql= ‘
if ”?” not in (”tempdb”,”master”,”model”,”msdb”) begin
use [?]
declare @tsql nvarchar(4000) set @tsql = ””
declare @iLogFile int
declare @sLogFileName varchar(55)
declare @RecoveryModel nvarchar(10)
declare LogFiles cursor for
select fileid from sysfiles where status & 0x40 = 0x40
open LogFiles
fetch next from LogFiles into @iLogFile
while @@fetch_status = 0
begin
set @tsql = @tsql + ”DBCC SHRINKFILE(”+cast(@iLogFile as varchar(5))+”, 10) ”
fetch next from LogFiles into @iLogFile
end
set @tsql = ”USE [?]; ” + @tsql + ” ALTER DATABASE [?] SET RECOVERY SIMPLE ” + @tsql
set @tsql = @tsql + ”; ”
–print @tsql –for debugging
exec(@tsql)
close LogFiles
DEALLOCATE LogFiles
end’
exec sp_msforeachdb @ssql