SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SIZE Of LDF FILE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mp5555
Starting Member

9 Posts

Posted - 04/29/2013 :  16:10:39  Show Profile  Reply with Quote
Size of my DB is 2G,and size of my ldf file used to be 50M.
I have full backup nightly and transactional backup every 1 hour.
Here is my problem:
I have One maintenace plan to reorganize, and rebuild Indexes once a week at night,
I noticed that the size of my LDF file became big exactly after reorganizing and rebuliding indexes (about 2G). Also, size of my transactonal backup which is run every 1 hour is about 30k
How can I prevent of getting bigger of my ldf file?
ANy help would be appreciated.

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/29/2013 :  16:59:46  Show Profile  Visit chadmat's Homepage  Reply with Quote
You can try switching to bulk logged before the Index maintenance, then back to full after.

-Chad
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/29/2013 :  18:07:58  Show Profile  Reply with Quote
Unless you are very short on space, it is probably easier just to leave the log file at the size it grows to, 2 GB, since that is not very big.

You might prevent some log file growth by running transaction log backups at shorter intervals, like every 15 minutes. I prefer the shorter interval of 15 minutes anyway, for recovery purposes.







CODO ERGO SUM

Edited by - Michael Valentine Jones on 04/29/2013 18:11:15
Go to Top of Page

mp5555
Starting Member

9 Posts

Posted - 04/29/2013 :  19:24:13  Show Profile  Reply with Quote
Thanks guys. Here we donot have more transactions. That is why I have chosen transactional backup at 1 hour interval. Is not it a good idea I shrink log db once a month? Thanks again for your help
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/30/2013 :  11:56:47  Show Profile  Reply with Quote
quote:
Originally posted by mp5555

Thanks guys. Here we donot have more transactions. That is why I have chosen transactional backup at 1 hour interval. Is not it a good idea I shrink log db once a month? Thanks again for your help



It's a very bad practice to shrink the log file on a regular basis. That can lead to tremendous VLF fragmentation, and cause poor performance.

Let the transaction log grow to the size needed, and then leave it alone.


Performance impact: a large number of virtual log files
http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx

Slow recovery times and slow performance due to large numbers of Virtual Log Files
http://blogs.msdn.com/b/grahamk/archive/2008/05/16/slow-recovery-times-and-slow-performance-due-to-large-numbers-of-virtual-log-files.aspx


This is a very good article on transaction log management:
Why is my transaction log full?
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/




CODO ERGO SUM

Edited by - Michael Valentine Jones on 04/30/2013 12:01:26
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000