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
Like this:
Like Loading...