Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SIZE Of LDF FILE

Author  Topic 

mp5555
Starting Member

9 Posts

Posted - 2013-04-29 : 16:10:39
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

1974 Posts

Posted - 2013-04-29 : 16:59:46
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)

7020 Posts

Posted - 2013-04-29 : 18:07:58
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
Go to Top of Page

mp5555
Starting Member

9 Posts

Posted - 2013-04-29 : 19:24:13
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)

7020 Posts

Posted - 2013-04-30 : 11:56:47
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
Go to Top of Page
   

- Advertisement -