How to backup and truncate all log files in a database

  • Article

Some things to consider ... 

  1. Make sure you can recover your server and database prior to issuing these commands.
  2. Since I am generically calling a rountine you will receive errors on the system databases.  
  3. Your database and log files must follow the convention of name=database name_log=log file name.
  4. This will force the reduction of your log files and you may not be able to recover unless you have done the appropriate database backup and log backup operations (I know this is repeating item 1 ... smile )
  5. If you have any better ways or improvements feel free to post a comment.

Enough with the disclaimers, here is the code:

declare @sqlstring nvarchar(1024)
SET @sqlstring='use ?;DBCC SHRINKFILE (?, TRUNCATEONLY);DBCC SHRINKFILE (?_log, TRUNCATEONLY);BACKUP LOG ? WITH TRUNCATE_ONLY;DBCC SHRINKFILE (?_log, TRUNCATEONLY);';
SELECT @sqlstring;
exec master.dbo.sp_MSforeachDB @command1=@sqlstring