truncate_only' is not a recognized BACKUP option

Question

Hello All,

I am able to run the below command in sql 2005 but in 2008 getting error : truncate_only' is not a recognized BACKUP option.


Back Up Log [DBName] with Truncate_Only 
dbcc shrinkfile([logFileName])

Thanks in Advance

Ashwin

Answer 1

Hi,

Check this link might be helpful

http://sqlserverpedia.com/blog/sql-server-management/backup-log-with-truncate_only/

---

Rajesh Jonnalagadda http://www.ggktech.com

Answer 2

it became deprecated because of 'misuse'  in 2005 & removed in 2008.  You can achieve the same result by sending the file to the NUL blackhole 

BACKUP LOG [DBNAME] TO DISK='NUL'

Make sure that if you are taking transaction log backups this will break your chain of transaction logs & you will need to take a full backup otherwise any log backups taken after the TRUNCATE_ONLY (or the 2008 equivalent) are useless

Answer 3

Hello,

Go throught the link.
http://experiencing-sql-server-2008.blogspot.com/2008/02/sql-server-2008-truncating-transaction.html


Hope this might be help ful.

Thanks,
Shanthi

Answer 4

No, it's not supported any more in sql 2008, if you want to remove log chain, try to switch to simple recovery mode.


Regards,

Andrian

Answer 5

Hey,

Thanks for your reply.

I have done this.
It worked by using

dbcc shrinkfile ([DBName])

Answer 6

For shrinking log file in sql server 2008, first u have to change the recovery model of database to simple,shrink the log file and then change back to the previous recovery model. following is the code

USE dbname;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE dbname

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)

GO

-- Reset the database recovery model.

ALTER DATABASE dbname

SET RECOVERY FULL;

GO

Answer 7

Amit, Thank You!  I just recovered over 100GB!!!

Answer 8

Awesome, exactly what I was looking for.  100 gig here too.

Answer 9

it became deprecated because of 'misuse'  in 2005 & removed in 2008

Now that's typical Microsoft..."We understand your needs better than you do"

Answer 10

Thanks for sharing right information. It helped us alot.

Answer 11

Thanks, very useful information!

Answer 12

I'm using SQL 2008 to build a BIDS package to migrate database objects from one database to another.  The SSIS package is throwing this error that this TRUNCATE_ONLY option no longer exists.  It's nice that Microsoft took away the function, but then they still call it in the Integration Services  Looking for a workaround.  The two databases I'm trying to transfer are both set to simple recovery.

Answer 13

When you take log backup with Truncate_only.

The inactive portion becomes reuse and breaks the LSN chain. And it will not generate any backup file on the disk.

After you perform Truncate_only immedietly you should perform full backup.

Answer 14

When you take log backup with Truncate_only.

The inactive portion becomes reuse and breaks the LSN chain. And it will not generate any backup file on the disk.

After you perform Truncate_only immedietly you should perform full backup.

The thing is Microsoft has removed the commands in SQL server 2008 onwards.

Read this post you may get it http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/

---

Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum Please click the Mark as Answer button if a post solves your problem!

Answer 15

Just switch to simple then switch back to full, then you can shrink the log file already

http://myitsolution.info/?p=77

Answer 16

Absoltely helpful....

I was looking for the same and it really helped me ..

Thanks very much........

Answer 17

I struggled with this doing it manually using the shrink Files option. Some DBs would, some wouldn't. I found the problem seemed to be the intial size.

Right click on the DB > Properties > files and I was able to change the inital size which in my case for some reason was 79. I changed it to 1.

---

Kim