SQL Server Performance

log file grows...and grows...

Discussion in 'General DBA Questions' started by griggsy21, Aug 14, 2006.

  1. griggsy21 New Member

    We have an issue with a DB server's log file growing in large proportions. Current settings are to automatically grow/10 MB/unrestricted file growth. However, I understand that the log file should be automatically shrunk to a smaller size after transactions are completed. This is not the case, and the log file can grow to GB's. There was a fix that I had tried sometime ago, where I detached the DB and reattached to allow a new log file to be created- thinking the log file may have been corrupt. I thought the problem had gone away after an incident free week, but it returned shortly after. Has anyone on this forum seen this problem before?
  2. Luis Martin Moderator

    What is your recovery model?

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  3. dineshasanka Moderator

    Log file contains all the transaction details. You tak backup so that log file truncate.
    also your log file may have Full recovery mode


  4. griggsy21 New Member

    We actually have always had the recovery model set to simple. Sorry I didn't include that sooner.
  5. Luis Martin Moderator

    What about maitenance plan?. If you have a scheduled job with reindex every day, that could cause big logs.

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  6. griggsy21 New Member

    We only have a maintenance plan to backup one db. I had actually added a job every hour to do a dbcc shrinkfile (db_log, n) to keep the log file to a somewhat manageable size- but it caused errors in our counter processes. It grew to 4 GB today!
  7. waqar Member

    Hi Griggsy,

    shrinkfile will only help if you will truncate log file before performing shrink operation.

    if you are not concern about your log data (which could be surprising for production enviornment) you can do

    alter database databasename set recovery simple <-- One time only
    backup log databasename with truncate_only
    dbcc shrinkfile (log,n)

    This will

    1- Set Recovery model to Simple (for minimal log).
    2- Truncate log.
    3- Shrink log file.

    ~* Opinions are like a$$holes, everyone got one. *~
  8. griggsy21 New Member

    Thanks for the response Waqar. I can try this...but isn't this just a correction to the job I have scheduled? I'd like to see if I can just eliminate the problem so as not to have to run the job and get back to normal. I am just trying to figure out why the log file is growing to such an extent.
  9. mmarovic Active Member

    Shrinking the log file is not the solution if it will grow again. Do you have a problem with disk space available? I would reserve in advance bigger amount of disk space and leave it as it is. Also, try to identify long running transactions. They may be cause by insert/update/delete of huge amount of data or by index rebuilding tasks. If index rebuilds cause the growth just find out what is the maximum log size produced and leave it as it is. If you rebuild all indexes at once, better apply solution from Tom Pulen's article from this site. If large inserts/deletes/updates cause long running transaction, split inserts into smaller batches using loop.
  10. griggsy21 New Member

    Thanks for your response. Quick question...I tried to run a trace in profiler to check out what commits/rollback/etc. if any were causing this. I got close to zero usable info from it though. Is there any other method I can use to find troublesome transactions?
  11. mmarovic Active Member

    As I said you should search for long running transactions. So check the query in descending order of duration. I mean set the filter on duration > 60000 ms or something like that.
  12. Henk New Member

    The best thing to do is make an maintenance plan, backup the transactionlogfile hourly and put these transactionlogs on tape,
    be aware that removing the backupped translogfiles should be in sink with the tapebackup, one would like to roleback one day.

    If you never have to rollback a transaction than use the schrinktip earlier in this topic.

    It works

    But speak to the guy that made the program, tell him the story about begin end end transactions. Mostly these growing cancerlike logfiles are due to transactions that stay open. When a normal db backup is initiated it's not shrinked.

  13. satya Moderator

    ...why one hour, you can perform the log backups every 15 minutes in order to control the virutal size of log.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  14. griggsy21 New Member

    Thanks for all your help everyone. I am going to take some time and examine the transactions. There has to be some long running transaction that is causing this. Profiler doesn't seem to much of a help in this case...but I think my filters are off, so I will keep trying.
  15. RockyLee New Member

    Try this way:

    full backup the database
    drop the database
    create a new database with the same name and RECOVERY Model as SIMPLE
    restore the database with the bak file

    Life is simple~~~
  16. sonnysingh Member

    Hi Folks

    I ma facing the same problem with my log files. I am truncating my log file as mentioned above and when check the size of log file physically it will show same as before.

    Steps I have followed....
    1.backup log travelwire with truncate_only
    2.dbcc shrinkfile (travelwire_Log,50)

    what would be wrong??? Database is in simple recovery mode...

  17. waqar Member


    Before truncating and shrinking what is log file size?
    As what i can see you want to shrink your log file to 50MB size, what is the size after executing both commands as u mentioned below?

    ~* Opinions are like a$$holes, everyone got one. *~
  18. mmarovic Active Member

    What happens if you issue checkpoint after backup log and one more backup log after checkpoint?
  19. Henk New Member

    Ok ok intervals between 2 backup transactionlogs depents on the load, the more you do the more transaction backups.

    Keep in mind that even in simple mode sql ALWAYS saves a running transaction in the transactionlog file. If the transactions fails it can rollback. After the "end transaction" statement is given it removes the data in the transaction logfile. But here;s mostely likely the problem. Is there realy a begin en and transaction. If not the sql server never sees the "end transaction so keping the data in the log file, and as all the ohter users are working leaving there actions in the log file "IT GROWS" The data is written to the database, that stay the same.
    Having the database log at simple you can only restor a database until it's last backup. You need the transaction logfiles to restore to the point just before the crash.

Share This Page