Enterprise Software Development

Easy way to kill MS SQL server transaction logs

I manage many SQL 2000 databases. And many times while performing maintenance I have run into the ‘transaction log full’ error.

like this:
Error: 9002, Severity: 17, State: 2
The log file for database ‘%.*ls’ is full.

So like many people I resort to using the commands
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE

The hassle with this, is that you need to first find out the logical name from sysfiles, or from enterprise manager to be able to use the DBCC SHRINKFILE command

So I wrote a stored procedure that you can install into each of your databases, it will enumerate through the sysfiles table. And perform the above steps against all of the Log files found for the current database. The nice thing is that this works with databases with more than one log file defined.

create procedure proc_killLog

(

 @size int = 2

)

as

begin

 declare @logFile sysname, @db sysname

 select @db = db_name()

 declare curFiles cursor for

 select rtrim([name]) from sysfiles where groupid = 0

 open curFiles

 fetch next from curFiles

 into @logFile

 while @@FETCH_STATUS = 0

 begin

 BACKUP LOG @db WITH TRUNCATE_ONLY

 DBCC SHRINKFILE(@logFile, @size)

 fetch next from curFiles

 into @logFile

 end

 close curFiles

 deallocate curFiles

end

This procedure should work with SQL 7, as well as SQL 2005 databases