Years ago while I was with Parity, we were always having to truncate the log file on our development servers as we were always running out to disk space.
We wrote a small little script which would truncate and shrink the log file and for some reason we called this script 'Fred'. Fred became a little star of a script being sent off to clients to get them out of problems.
I was trying to clean my machine down in order to clean some space for Vista the other night and realised that my log files were hitting ~ 1GB on my Dev machine (don't ask).
DECLARE @ssql nvarchar(4000)
SET @ssql= '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'')
BEGIN
PRINT ''SHRINK LOG FILES FOR ?''
USE [?]
DECLARE @tsql nvarchar(4000) SET @tsql = ''''
DECLARE @iLogFile int
--Loop Though all the LogFiles for the Database
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
--Try to Shrink Log File to 1MB
SET @tsql = @tsql
+ ''DBCC SHRINKFILE(''
+cast(@iLogFile AS varchar(5))
+'', 1)'' + CHAR(13) + CHAR(10)
FETCH next FROM LogFiles INTO @iLogFile
END
CLOSE LogFiles
DEALLOCATE LogFiles
--TRUNCATE LOG FILE and Try to Shrik the Log file again
SET @tsql = @tsql
+ ''BACKUP LOG [?] WITH TRUNCATE_ONLY''
+ CHAR(13) + CHAR(10) + @tsql
--PRINT @tsql
EXEC(@tsql)
END'
EXEC sp_msforeachdb @ssql
exec @RETURN_VALUE = sp_MSforeachdb @command1, @replacechar, @command2, @command3, @precommand, @postcommand
There was a lot of talk at the SQL talk the other week about undocumented functions and stored procs about these being a thing of the past. However this is still not documented, which probably means that it might not appear in the next verison of SQL (be warned!!!).