Most developers write code to go against databases and often when doing performance testing and analysis, you can find yourself with a error something like “The transaction log for database ‘blahblah’ is full.”.   And many times this can come at the worst times (for example at the 23rd hour of a 24 hour test run).  Sometime back I found a handy script that I use as a scheduled job in these situations that will clear out the transaction log.  I forget where I saw this so I apologize and take no credit for this one, but it does work so thank you anonymous author. 

Happy Coding

SET NOCOUNT ON 
CREATE TABLE #TransactionLogFiles (
	DBName VARCHAR(150), 
	LogFileName VARCHAR(150) 
)
DECLARE DBList CURSOR 
	FOR SELECT name FROM master..sysdatabases WHERE 
	NAME NOT IN ('master','tempdb','model','msdb','distribution')
DECLARE @DB VARCHAR(100)
DECLARE @SQL VARCHAR(8000) 
OPEN DBList
FETCH NEXT FROM DBList INTO @DB
WHILE @@FETCH_STATUS <> -1 
BEGIN
	SET @SQL = 'USE ' + @DB 
		+ ' INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT '''
		+ @DB + ''', Name FROM sysfiles WHERE FileID=2'
	EXEC(@SQL) 
	FETCH NEXT FROM DBList INTO @DB 
END

DEALLOCATE DBList 
DECLARE TranLogList CURSOR 
	FOR SELECT DBName, LogFileName FROM #TransactionLogFiles
DECLARE @LogFile VARCHAR(100) 

OPEN TranLogList 
FETCH NEXT FROM TranLogList INTO @DB, @LogFile
WHILE @@FETCH_STATUS <> -1 
BEGIN 
	-- If you want to watch what it's processing.
	--PRINT @DB +',' + @LogFile
	SELECT @SQL = 'EXEC sp_dbOption ' + @DB 
		+ ', ''trunc. log on chkpt.'', ''True'''
	EXEC (@SQL)
	SELECT @SQL = 'USE ' + @DB + ' DBCC SHRINKFILE(''' 
		+ @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'
	EXEC (@SQL) 
	SELECT @SQL = 'EXEC sp_dbOption ' + @DB 
		+ ', ''trunc. log on chkpt.'', ''False'''
	EXEC(@SQL)
	FETCH NEXT FROM TranLogList INTO @DB, @LogFile 
END

DEALLOCATE TranLogList
DROP TABLE #TransactionLogFiles