cannot get transaction logs to commit / or shrink!!!

Question

Hi,

I know this is a common question but I can't get this to work.  I have a DB using the FULL recovery model.  I perfrom nightly Full backups.  I also perform transaction log backups.  However, the trans log file (LDF) grows all the time and the DB doesn't.  What do you need to do to get the logs to commit records to the mdf files and shrink the log.

Please instruct me as to what else I need to tell you so that you can help me!!

TIA,

Charles

Answer 1

Hello,

Please, could you provide the full version  ( 2000,2005,2008,2008 R2,2012 + last installed service pack ) of your SQL Server ( and the edition (Express , Web, Standard, Entreprise,...) ? The answer may differ accoding these informations.

In http://msdn.microsoft.com/en-us/library/89a4658a-62f1-4289-8982-f072229720a1#Tlog_Truncation , i found this

"To avoid filling up the transaction log of a database, routine backups are essential. Under the simple recovery model, log truncation occurs automatically after you back up the database, and under the full recovery model, after you back up the transaction log. However, sometimes the truncation process can be delayed. For information about factors that can delay log truncation, see The Transaction Log (SQL Server)." 

in  http://msdn.microsoft.com/en-us/library/ms190925  ( the transaction log (SQL Server) upper link , you will find a note which could be useful even if it seems that the documentation is not complete ( "Log truncation does not reduce the size if the physical log file. " i think that something is missingbetween "if the physical log file" and the dor marking the end of the sentence ).

I think that your question should be moved to the Transact-SQL or the Database Engine Forums  ( i am wandering which is the most appropriate one ). A move by a moderator avoids to recreate a new thread , to have lost posts . I prefer usually to ask the agreement of the original poster that's to say Charles__Toray before doing a move )

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

LDF (Transaction log) contains the details of your all commited & uncommited transactions of DB. Where as DB (MDF 7 NDF contain the actuall data thats why your LDF keep growing & DB file not.

To keep your LDF in normal size, you need to take regular T-log backups in frequent intervals.

Shrinking of LDF file is not recomended as it may effect DB performance.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

Hello,

Please, could you have a look at this thread ?

http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/dcedcb25-75df-4d6c-b0fa-f2f6de528ebc

Gail has modified the documentation which will be updated the next week.

If i have understood the documentation, a truncate of the log files frees spaces in the transaction logfile , but does not give back the free space to the file system. It could be a bad thing except if you know that one of the worst thing for the performances is that a space allocation ( to increase the size of a datafile  or a logfile ) is needing a call to the Windows file system. During all the time to allocate new blocks, the query is stopped and the whole time used for it is included in the query timeout.It is a usual reason to get a query timeout.

2 possible problems :

- the increase growth for data/log files is/are too small ( the default value is 1 MB  maybe ridiculous for a database big in records number or in mean records sizes )

- when a database is created , the default values for the sizes/growths are coming from the values of the Model database . If you change these values in the Model database, these values will be the default values for the new databases ( but be careful for small databases )

- if the physical drive is heavily fragmented, the size increase may be long, with the classical query timeout.

I hope you will excuse my poor english.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.