SQL Server Performance

Transaction log grows

Discussion in 'General DBA Questions' started by NewDBA, Jul 9, 2003.

  1. NewDBA New Member

    I have 6 G database (full recovery model)
    Transaction log is growing like 4-6 G per night
    I just scheduled truncate/shrink job after transaction log backup.
    But still wondering what would cause such a big grows .
    I was watching activity at server there was no users at all during 2 days!!!
    Why transactio log would grow?


  2. gaurav_bindlish New Member

    Please refrain from duplicating a post at two or more places. I have deleted the other post.


    Gaurav
    Moderator - SQL-Server-Performance.com
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. gaurav_bindlish New Member

    Are there any jobs run in the night that do a mass insert / delete? If there are try deleting / insering in batches. This requires less log space.

    Gaurav
    Moderator - SQL-Server-Performance.com
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. NewDBA New Member

    there is jobs but such a huge grows?
    id database option transaction log is set to automatically grow file by percentage .
    Is that okey or i would better change it to grow by MG will that help?
  5. gaurav_bindlish New Member

    Changing auto grow options won't help. How many rows are affected when the job is run? Do you take backup before / after the job is run?

    Gaurav
    Moderator - SQL-Server-Performance.com
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  6. nilayt New Member

    Check whether you have any schedule task which does reinddxing on database. If you have reindexing then that can be cause for such a big log everyday.

    Nilay
  7. Selcuk New Member

    Do you use replication? If so, in your replication may be something is going wrong and because of that, your transaction log grows everyday.
  8. NewDBA New Member

    no replication is not happening
  9. itbhushan New Member

    Why do not you put in a sql profiler trace based on the jobs executed if you can afford to have that amount of overhead on your server to trace out the exact series of operation, or else if u can save performance monitor logs during the execution of jobs, u would have a very clear idea about the exact nature of jobs getting exected, which could eventually help u in getting to the root cause of the problem.

    Bhushan
  10. satya Moderator

    What kind of maint.plans are on the database?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. NewDBA New Member

    Actually maintenance plan is the problem here.
    This server has 815 databases(!)Half of them are simple recovery model and half full.
    There was one recovery plan for all user databases which would do everything :
    optimization,integrity chechk,backing up transaction log and the database by itself
    All this staff is done every night.
    But because transaction log cannot be backed up for simple recovery model dbs,
    sql could not clean old files:because transaction log backup was reporting errors.
    So first thing i did separated that maintanence plan in two peices
    1.for simple recovery model(optimization,integrity chechk,backing up )
    2.for full recovery model (optimization,integrity chechk,backing up and transaction log backup)

    Optimization every night!
  12. gaurav_bindlish New Member

    What kind f optimization queries are run on the server?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  13. satya Moderator

    Optimization job will have impact on Tlog to increase the space.
    For which database you have the problem of Tlog space bulge & its recovery model/Tlog backup schedule?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  14. NewDBA New Member

    1.DBB4C3D_Audit , Updateability=READ_WRITE
    UserAccess=MULTI_USER
    Recovery=FULL
    Version=539Collation=SQL_Latin1_General_CP1_CI_ASSQLSortOrder=52IsTornPageDetectionEnabledIsAutoCreateStatisticsIsAutoUpdateStatistics 80DBB4C3D_Audit 4880.38 MBsa432Sep 12 200280
  15. NewDBA New Member

    Sorry i forgot :
    Tlog is being backed up every night
  16. satya Moderator

    So why not schedule Tlog backup every hour or so and see the difference use WITH INIT clause while backup of Tlog.

    Also run DBCC SHOWFILESTATS and produce the results.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  17. NewDBA New Member

    I will try it :
    schedule Tlog backup every hour
    I have to wait until tomorrow thought:have to ask manager
    Should i truncate transaction log every hour also after backing log?.
    Last time when i backed up transaction log at the middle of the day it
    didn't truncate transaction log emediately
  18. gaurav_bindlish New Member

    Backing up the transaction log automatically reduces the %used transaction log. So there is no need to truncate the transaction Log.

    Did you check the %transaction log used at that instance? If it didn't get reduces, there could be an open transaction in the database towards the end of the transcation log. Also the transaction log backup will not reduce the size of log. It will simply free up the space. For reducing / shrinking the transaction log, see other posts on this site.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  19. satya Moderator

    True, also refer to books online also for DBCC SHRINKFILE about shrinking Tlog file.
    If the optimization jobs take on Tlog, then there will be no use of running DBCC SHRINKFILE every time. Better to define file size values for Tlog after assessing the jobs usage.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page