Link to home
Create AccountLog in
Avatar of MezzutOzil
MezzutOzil

asked on

How to truncate log file in SQL 2008 R2 server?

This is using MS SQL 2008 R2 Server 64-bit. There are few databases that configured to have a restricted growth on transaction log file. Basically, each database is configured to have 1 primary data file (.mdf) and 1 transaction log (*.ldf). There is this large database with data file of 35 gb, and log file of 16 gb. This db is set in full recovery mode. I am thinking of truncate the log file, what is the proper way of doing so? Shall I backup the database, then backup the log file, and then truncate? or else?

Please show clearly in steps. Thanks.
Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

The *ONLY* reason your database should be in FULL recovery mode is if you are taking transaction log backups so you could do point in time recovery.  That is, let's say you took a full backup at midnight, transaction log backups every 15 minutes, and then you wanted to restore your database to 3:35:12pm, you could.  If you are NOT taking regular transaction log backups then put the DB in SIMPLE recovery mode.  

Read the articles stated above, and then shrink your log file to something more managable.  Also do a DBCC LOGINFO.  That shows you your internal transaction log fragmentation.  I suspect it's in the hundreds.  It will make backups and restores much slower.  Reduce your log file fragmentation to something around 50
Avatar of MezzutOzil
MezzutOzil

ASKER

Hi Saurv/MrAli,

So treats Log shrinking a serious matter.

So, since no backups were scheduled for both data and log files, that means I can do the (ad-hoc) backups in these sequence:

  1. Full backup on db
  2. Log backup with truncate-only
 
How to I schedule the above scheduled backups in SSMS or by using Transact-SQL commands? Using Maintenance tasks? For example, the db name is - proddb1, and schedules for db and log are at 7 pm and 9 pm per day, respectively.


thanks,
ASKER CERTIFIED SOLUTION
Avatar of TempDBA
TempDBA
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi TempDBA and Jimpen,

Thanks for your suggestions.

I found this:

  Coming back to your question, yes the sequence you mentioned is correct. YOu need to take a full backup and then logs. Once you have taken a log backup or the first full backup, you can shrink your log file.
        You can either write your own job to do so, or can do it with a maintainance task via GUI.
http://support.microsoft.com/kb/2019698
http://forums.asp.net/t/1438498.aspx/1

Is what I want.

Appreciate your helps.
Excellent
Nice to know that it helped :) . Good luck with your further tasks.