SQL Server Performance

Looking Solution - Log file is not clearing data.

Discussion in 'SQL Server 2005 General DBA Questions' started by amitadmin, Oct 20, 2008.

  1. amitadmin New Member

    Hi Friends,
    This log continue to grow, log backup is happening however log is not clearing/data is continuously loaded. Hence disk filled up.
    Please provide the solution as soon as possible.
  2. rohit2900 Member

    Amit...
    Can you add few more things as the size of database. The time difference between to log backups etc.
  3. ghemant Moderator

    Hi,
    What I feel is if feasible decrease the latency between two t-log backups.You should also see the auto-growth option of your database if it is set to % change it to some fix MB.
  4. madhuottapalam New Member

    Very common issue in SQL Server. This is because of the recovery model and the Inadequate TL backup frequency as already mentioned. If you are not taking TL backup regularly no need to keep the db in Full Recovery model. If you want to keep the db in FUll recovery then increase the TL backup frequency. SInce the TL is grown you may want to shrink it first and then change the recovery model/Backup frequency. Take a full backup soon after shrink the db
    you may check this http://madhuottapalam.blogspot.com/2008/05/faq-how-to-truncate-and-shrink.html
    Madhu
  5. satya Moderator

  6. Saurabh Srivastava New Member

    It looks like your log file is not clearing inactive logs after the backup. Run DBCC loginfo to find out number of virtual logs in log file. And run DBCC opentran. BTW- Is your database part of Replication?
  7. amitadmin New Member

    Thanks for reply, the database is not part of replication. We have reconfigured the Adhoc log backup job which runs when ever log increased upto a certain limit- but ever time the log backup job/adhoc log backup job runs it doen't decrease the log file size and its keep on increasing day by day.
    the datafile size is 3gb, while the log file size reached to 54gb and it shows its 94% used.
    My concerns are why the log file is not decreasing after backup. and why its keep on increasing- and help/solution would be appriciated.
  8. madhuottapalam New Member

    <quote>
    We have reconfigured the Adhoc log backup job which runs when ever log increased upto a certain limit- but ever time the log backup job/adhoc log backup job runs it doen't decrease the log file size and its keep on increasing day by day.
    </quote>
    If it means that, you do not need POINTINTIME RESTORE Option then change the Recovery model to Simple. If you need POINTINTIME RESTORE then increase the TL Backup frequency. SInce the TL is grown this huge you need to truncate it first. Soon after truncation take a full backup.
    Madhu
  9. rohit2900 Member

    Yes...go with what madhu suggested use full recover if and only if you need point n time recovery.... else go with simple recovery.
    Apart from this you didn't mentioned the frequency of TL backups... Are you doing differential backups...
    Have you specified the recovery interval as It might happen that the automatic heck point is not happening. Also check if truncate log on check point option is true or not.
    How r u taking the TL backups?
    Please refer below links to get more clear understanding on SQL Server checkpoints.
    http://blogs.msdn.com/psssql/archive/2008/04/11/how-it-works-sql-server-checkpoint-flushcache-outstanding-i-o-target.aspx
    http://doc.ddart.net/mssql/sql70/ca-co_6.htm
    http://msdn.microsoft.com/en-us/library/ms188748.aspx
    http://msdn.microsoft.com/en-us/library/aa174542(SQL.80).aspx
    Hope this helps.
  10. MohammedU New Member

    Was it at any time part of the replication? I have noticed when you remove the replication, it will not cleanup the stuff...
    select * from sysdatabases where name = <db name>
    and check category column if it is non zero then you need to clean up the replication...
  11. amitadmin New Member

    Thanks all for your input.
    The database is not a part of replication. We require point recovery thats why the recovery model is Full.
    The frequency of full and log backup once daily. its as below:
    1. intigrity check
    2. Log backup
    3. Full backup
    Above three are in one job and this job runs everyday - one time.
    Apart from this there is one adhoch log backup job which run if log size reached at 80% only. In our case even if adhoch log backup job run but it doesn't release space(active part) of log file.
  12. ghemant Moderator

    Hi,
    As suggested earlier, schedule your T-Log backup 1 hour, this will keep your database in shap.
  13. ghemant Moderator

    Hi,
    As suggested earlier, schedule your T-Log backup 1 hour, this will keep your database in shape.
  14. amitadmin New Member

    :) All Friends, thank you very much for your valuable support.
    This issue has been resolved becuase the log backup script mentiond the "NO_TRUNCATE" hence it was happening.
    Sorry for bother to everyone.

Share This Page