SQL - shrink database log file

Question

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 error message:

Msg 155, Level 15, State 1, Line 3 'truncate_only' is not a recognized BACKUP option.

Am I missing something?

Why? As a general rule, shrinking your files is a **bad** idea. — RBarryYoung, Feb 03 '10 at 23:10
It's a poor practice because it negates the purpose of the transaction log in the first place. That's why SQL Server 2008 removed this ability. As I mentioned, if you don't want the transaction log to grow, if you don't have an RPO, use the Simple recovery model. Otherwise, you need the transaction log in order to be able to successfully restore recent transactions from the last database backup. — Aaronaught, Feb 04 '10 at 01:17
http://www.codeproject.com/Tips/625760/How-to-truncate-log-file-in-SQL-Server — Jitendra Pancholi, Feb 18 '15 at 10:45
Any commercial based database should NEVER be made into a SIMPLE model. Always, always use FULL and pay for more storage if need be. You've been warned. — Fandango68, Oct 27 '17 at 02:21

score 5 · Accepted Answer · answered Feb 03 '10 at 23:00

5

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.

answered Feb 03 '10 at 23:00

Aaronaught

120,909
25
266
342

That's a horrible, horrible thing to do. If you want to use the `SIMPLE` recovery model then that's fine, but don't lie by destroying the transaction log and then claiming you have `FULL` recovery. The person who wrote that article clearly does not understand anything about SQL Server recovery models and was just (like the OP I guess) looking for a quick fix to free up space. – Aaronaught Feb 19 '15 at 04:58
It's not horrible, if someone do not want to take backup and not bothered about the logs and only want to save space then it's good to go with that solution to only shrink the log file. And i guess OP has asked the same. – Jitendra Pancholi Feb 19 '15 at 06:05
And as you said, if someone is bothered about the log data, then they should take the backup first. – Jitendra Pancholi Feb 19 '15 at 06:06
@JitendraPancholi: If you want to be reckless then just use `SIMPLE` recovery model. Switching to `SIMPLE` to delete the logs and then pretending that you're really on `FULL` is reckless **and** dishonest. Tell me, do you understand what's in those logs and why they can't simply be shrunk or deleted under `FULL` recovery? – Aaronaught Feb 20 '15 at 06:49

score 0 · Answer 2 · edited Jun 15 '17 at 09:28

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 @dbname varchar(50) = 'AdventureWorks';
DECLARE @logFileName varchar(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 @dbname TO DISK = @path WITH 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

SQL - shrink database log file

2 Answers2

Linked

Related