Sql - Shrink Database Log File

I am trying to shrink my database log file. I have tried to run: USE databasename BACKUP log databasename WITH truncate_only DBCC shrinkfile (databasename_log, 1) I get the erro

Solution 1:

SQL Server 2008 no longer allows the NO_LOG / TRUNCATE_ONLY options.

To truncate your transaction log, you either have to back it up (for real) or switch the database's Recovery Model to Simple. The latter is probably what you really want here. You don't need Full recovery unless you are making regular transaction log backups to be able to restore to some point mid-day.

Solution 2:

I think the best way is to use a script like this:

USE AdventureWorks
GO

-- Use some dynamic SQL just only not to re-write several times the name -- of your db, or to insert this snippet into a loop for all your databases...DECLARE@dbnamevarchar(50) ='AdventureWorks';
DECLARE@logFileNamevarchar(50) =@dbname+'_log';
DECLARE@SQL nvarchar(max);
SET@SQL= REPLACE('ALTER DATABASE {dbname} SET RECOVERY FULL;', '{dbname}', @dbname);
EXECUTE(@SQL);

DECLARE@path nvarchar(255) = N'F:\BCK_DB\logBCK'+CONVERT(CHAR(8), GETDATE(), 112) +'_'+ REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','') +'.trn';

BACKUP LOG @dbnameTO DISK =@pathWITH INIT, COMPRESSION;

DBCC SHRINKFILE(@logFileName);

-- determine here the new file size and growth rate:SET@SQL= REPLACE('ALTER DATABASE {dbname} MODIFY FILE (NAME = '+@logFileName+', SIZE = 32000MB, FILEGROWTH = 10%);', 
    '{dbname}', @dbname);
EXECUTE(@SQL);
GO

http://www.snip2code.com/Snippet/12913/How-to-correctly-Shrink-Log-File-for-SQL