Truncate the Log File

Question

Truncate the Log File

vamshi.sql

SSCommitted

Points: 1770
More actions
August 23, 2010 at 3:00 am

#387634

Hi Everyone,
Can any one clarify what will happen when we execute the below query..
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Is there any possibilities to loss some data when we execute the above query? One of my database log file is growing regularly but not hugely.Database in on Full Recovery model.Can I put that in to Simple recovery model?? If i do changes for my database,will I lose any data....Please let me know
Thanks in advance,
Vamshi.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

I will suggest, instead of shrinking the log you should go for changing the recovery option to simple because if you search further, you will come to know that with "truncate only" option will impact your restoration process when you be doing log backup restoration as it break the chain sequence.

Because of this the option "truncate only" is no more provided from 2008 onwards and Stongly suggested to put the database back to Simple mode.

----------
Ashish

crazy4sql SSCoach Points: 19590 More actions · Answer 2

put the database back to Simple mode.

And as well once resolved put it back to Full recovery.

I dont like blame game so ...... :w00t: :w00t: 😀

----------
Ashish

BrainDonor SSCoach Points: 19254 More actions · Answer 3

vamshi.sql (8/23/2010)
Hi Everyone,
Can any one clarify what will happen when we execute the below query..
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
Is there any possibilities to loss some data when we execute the above query? One of my database log file is growing regularly but not hugely.Database in on Full Recovery model.Can I put that in to Simple recovery model?? If i do changes for my database,will I lose any data....Please let me know
Thanks in advance,
Vamshi.

You won't lose any data with the code above, but you are going to mess the log file about more than it would ever like.

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files.aspx

If you're considering moving to Simple Recovery just how much data can you afford to lose? You'll only be able to recover a Full backup using that model, and if you switch model back and forth you have to take a Full backup once the model has changed.

If you need the Log backups and the size is a problem could you increase the space available for them or even better, increase the frequency of the backup?

http://www.sqlservercentral.com/articles/64582/ - managing transaction logs.

Shrinking a log or DB should be avoided. At best it's a one-off thing for an unexpected growth. If it's a regular problem then you need to cater for the growth or resolve the cause.

BrainDonor.

Steve Hall
Linkedin
Blog Site

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please read through this - Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

ashish.kuriyal (8/23/2010)
I will suggest, instead of shrinking the log you should go for changing the recovery option to simple because if you search further, you will come to know that with "truncate only" option will impact your restoration process when you be doing log backup restoration as it break the chain sequence.

So will a switch to simple recovery, and in exactly the same way as the log truncation.

No log backups allowed, no chance of point-in-time recovery.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

crazy4sql SSCoach Points: 19590 More actions · Answer 6

Thanks Gail.

But I read the question as :-

Can any one clarify what will happen when we execute the below query..

And I consider that they are well aware of backup and restoration sequence.

The reply was just to answer on "with truncate" or "recovery mode"

----------
Ashish