SQL Server Performance Forum – Threads Archive
Log file randomly increses size.
I have noticed that sometimes our log file will grow in size, from 407k to 3610k to 17,177,944k then back to its normal size of roughly 3-4 megs. We back up at 15 minute intervals, and it does this even during non-peak times(IE: in the middle of the night when there is little to no activity). Unatended it will eat up all the space. The strange thing was this morning, the backup of the Tran log failed, upon close inspection I noticed that the log file was as follows.12:43 17,177,944k
1:00 10,198k
1:15 8,756k
1:30 9,140k
1:45 9,012k Has anyone seen this before..? And what can I do.. Its random, it will go backup small files, then all of a sudden peak into the gigabyte ranges then back down to small 500k files all within 30-1hour..
Compare the log file times to your scheduled database optimization plans. I suspect you will find that when the optimizations run the tran logs get big. We have tran logs the exceed our database size after the optimizations run. This will happen if you checked "Reorganize data and index pages" under the optimization tab and your database recorvery model is set to "Full". — Greg Haselmann
Hi, thanks for getting back with me. I found that option, and here is what is scheduled. The database (table portion of the database) does not have this option checked, however the log file backup does, and this is what it says it is to do. "Occurs every 1 day(s), at 1:05:00 AM." And the option noted was "Change free space percentage to 10%" However the problem happened today, during the day, at 12:43pm, no where near the 1:00 am start time I would have thought. Can you think of any other reasons why this would be doing it..? Thx
I’ve seen this as well when optimization is running at the same time as backups. Another option is that you have a query, stored procedure, batchjob or anything else running that does a large amount of work in one big transaction. For example a large import. Could be a query that is run infrequently. Run a Profiler trace during a day and see if you can catch what is happening. I think you can monitor log growths as well with Profiler. /Argyle
True, do you have any maint. jobs for optimization. If so the log file size will be varied when it runs reindexing on the tables. How about Tlog backup schedule? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I have only found one procedure that fires around that time frame, and am looking into that, however that does not do any optimization. There is only one procedure that optimizes and that is run at 1:00 am. Nowhere near the time frame that this happens. The thing that is strange is that it goes from the lower end of the megabyte scale to the high gig range with no warning. I will use sql profiler to watch the process and see where that leads. If anyone has any other ideas, please don’t hold back.. <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Thx,<br />David<br />
Just to add, I get similar behavior on some of the databases I look after. I haven’t got to the bottom of it yet. One reason for it the log file growing unreasonably can be long running transactions – SQL Svr won’t truncate any transaction before the oldest open transaction. However, in my case, I’ve checked and haven’t found long running transactions. The growth seems to be connected with relatively small amounts of data being bulk copied via an ADO app. It could be that SQL Server doesn’t handle the ADO sourced transactions as well as it would if they were native SQL such as a stored proc? Not sure. Clive
What is the Vesrion and Service pack of SQL Server that you are using? There is a reference of Transaction Log being filled in SQL Server 7.0 pre SP 2 http://support.microsoft.com/defaul…port/kb/articles/Q254/3/69.ASP&NoWebContent=1 http://support.microsoft.com/default.aspx?scid=kb;EN-US;243302 Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
We are running SQL2000 on a 2k Box. I did find that we were re-organizing the data every night, and when shut that off the problem went immediatly. There are still more to do, we are going to set up jobs that change the Recovery mode to simple during the night because those transactions are easily replaceable by running the jobs again. -David Roesch
San Diego, Ca
]]>