Share via

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

Comments

  • Anonymous
    January 26, 2008
    Buenas hace unos días escribí un pequeño post donde comentaba como "limpiar un poco" las bases

  • Anonymous
    January 26, 2008
    Buenas hace unos días escribí un pequeño post donde comentaba como "limpiar un poco" las bases

  • Anonymous
    January 26, 2008
    Buenas hace unos días escribí un pequeño post donde comentaba como "limpiar un poco" las bases de datos

  • Anonymous
    October 15, 2008
    I already posted a way to truncate log, so that they do not grow indefinitly. Here a little routine found