Log File Full

My production database is very big in size and due to contious transactions, my log file becomes full frequently. I manually shrink log file when it becomes large. But i believe shrinking many times is not a good practise in terms of performance. So what should i do? Can anyone suggest a better approach.

How much frequency are you taking backup of transaction log ?

For example, if you are taking transaction log backup every 30 minutes, change it to every 15 minutes.

Thanks

Naras.

|||

Yes, you rightly said that shrinking is not a good practice. You should control the growth of Transaction log and if space is not there in the current drive, you should shift TL to a better place. You can control the growth by taking backup of transaction log more frequently. When you shrink the transaciton log the backup chain breaks. so the first thing you do should be full backup. If you dont want point in time restore you can change the recovery model to simple which will restirct the gwoth of TL to a certain limit.

How to stop the transaction log of a SQL Server database from growing unexpectedly

http://support.microsoft.com/kb/873235.

Madhu

|||I take trn log backup every 2 hours. As i want point in time restore, i need to take tl back up atleast 2 hr.|||But if i don't shrink it grows beyond 2 GB in week. I need a poin in time restore so i had to keep full recovery model.|||

what is the frequency of TL backup of this database.

Madhu

|||Every 2 hr TL Backup, daily Diff backup and weekly Full backup.|||

You can still achieve a point in time restore, even if you take transaction log backup every 30/60/90/120 minutes.

Thanks

Naras.

|||

schedule TL backup more frequently... say every 15 min... this is the better approach

Madhu

|||

http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=263

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Addition to Madhu's references.