Truncate (Shrink) LOG Files of Databases in SQL Server 2008 R2


Today I got this error while trying to shrink my SharePoint 2010 databases on my dev machine:

‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

I searched for the error and found different sites, e.g.:

But there was no complete script for “truncating” all of my databases  at once.

Here is my script:

--TRUNCATE_LOG for all databases at once on SQL SERVER 2008 R2
--https://ikarstein.wordpress.com/2011/08/28/truncate-shrink-log-files-of-databases-in-sql-server-2008-r2/

DECLARE c CURSOR FOR SELECT database_id, name, recovery_model_desc FROM sys.databases -- WHERE name='sharepoint_config'; 
DECLARE @dbname VARCHAR(1024); 
DECLARE @rmod VARCHAR(1024); 
DECLARE @id INT; 
DECLARE @lfile VARCHAR(1024);  

OPEN c;  

FETCH NEXT FROM c INTO @id, @dbname, @rmod; 

WHILE @@FETCH_STATUS = 0 
BEGIN
     IF @rmod = 'FULL'
     BEGIN
        SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
        PRINT @lfile
        EXEC('ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE')
        EXEC('USE ['+@dbname+']; DBCC SHRINKFILE(['+@lfile+'], 1)')
        EXEC('ALTER DATABASE [' + @dbname + '] SET RECOVERY FULL	')
     END ELSE
     IF @rmod = 'SIMPLE'
     BEGIN
        SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)        
        PRINT @lfile
        EXEC('USE ['+@dbname+']; DBCC SHRINKFILE(['+@lfile+'], 1)')
     END
     FETCH NEXT FROM c INTO @id, @dbname,@rmod; 
END;  

CLOSE c 
DEALLOCATE c  

For me it works like expected.

2 thoughts on “Truncate (Shrink) LOG Files of Databases in SQL Server 2008 R2

  1. Hello, thank you for this script! It worked perfectly for my machine. I used it on a ‘reporting’ box that I am using to take data from another machine, transform, augment, and score that data, and then save only a subset of that recent data on the reporting box. I was running job automatically this every hour and soon I discovered my reporting box was getting low on hard drive space. Needless to say, running it every hour generated a large log file. (I have now changed the backup properties but this was nice to have ex-post facto)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s