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:

[sourcecode language="sql"]–TRUNCATE_LOG for all databases at once on SQL SERVER 2008 R2
–http://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
[/sourcecode]

For me it works like expected.

4 Responses to “Truncate (Shrink) LOG Files of Databases in SQL Server 2008 R2”

  1. KErickson

    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)

    Reply
  2. Lava

    Hello -

    great post, this is very helpful. The one thing the code does not do is when the database is offline, you might want to add additional like to exclude the databases that you have offline (that is only if you have any database offline).

    Many thanks to you.

    Reply
  3. Zafar

    The solution you mention is not working. my log file size is 792223MB and it is not reducing.
    below is more information, Please see CurrentSize and UsedPages has the same size.

    DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
    12 2 101404648 63 101404648 56

    Reply

Leave a Reply