SQL Server Performance

Database log file size not growing despite great activity

Discussion in 'SQL Server 2005 General DBA Questions' started by craigkornacki, Sep 27, 2007.

  1. craigkornacki New Member

    We have a SQL Server 2005 database that gets a lot of daily activity, but whose log file size is unexpectedly low.
    <database name> log size (MB) 1255.68 % used 1.878029 0
    Is this anything to be concerned about? We're afraid that if we need to do a restore from the log that it would not be of much use to us.
    What would cause the log file size used to be so low?
    Thanks for any help that you can provide.
    Craig
  2. Raulie New Member

    Is your database using the Simple Recovery Model or are there any jobs that are truncating your Transaction Log?
  3. craigkornacki New Member

    No. It is using the full recovery model. Also, no jobs are truncating the log file.
  4. martins New Member

    If you say that there is a lot of activity...what kind of activity are we talking about? Are there many inserts/updates/deletes?
  5. craigkornacki New Member

    Re: Database log file size not growing despite great activity

    Reply Favorites Contact
    If you say that there is a lot of activity...what kind of activity are we talking about? Are there many inserts/updates/deletes?
    Well, users are constantly adding new rows to our database, making updates to the rows and deleting those rows. Is there a SQL tool to tell you how many of those actions are going on?
  6. Raulie New Member

    [quote user="craigkornacki"]
    No. It is using the full recovery model. Also, no jobs are truncating the log file.
    [/quote]
    Ok so how often are you running Tlog backups? If you are running frequent Tlog backups as part of your backup plan then I wouldnt worry about it. But if you are still concerned then just do a planned restore of your database and transaction logs (not on production) to clear up any doubts.
  7. satya Moderator

    Run DBCC SQLPERF(LOGSPACE) in order to monitor the log growth between the transaction log backups, also monitor what kind of activity you are having on the server and try to capture bulk loads & transactions load too.
  8. craigkornacki New Member

    >>Run DBCC SQLPERF(LOGSPACE) in order to monitor the log growth between the transaction log backups, also monitor what kind of activity you are having on the server and try to capture bulk loads & transactions load too.
    What's the easiest way to monitor the kind of activity I have on the server? Also, what's a good way to capture bulk loads and transaction loads?
    I appreciate your help. I'm kind of a newbie at this stuff.
  9. martins New Member

    Ok, those kind of transactions should be logged. The best way to see what is going on is to run a trace (use Profiler) for a day or so. You would be able to determine from this what kind of queries are running...and how many.
    Hope this helps.
  10. satya Moderator

  11. craigkornacki New Member

    After running a trace, then what should be the next step? What types of things should I look for that would cause the transaction log to stay the same in size or decrease in size?
  12. satya Moderator

    Any activity on the database except the BULK LOAD operations will cause the input to the Transactions log records.
    Mostly here I have seen people asking why Transaction log is bulging up even though there are no activities on the database. Your case is opposite to that, I would say rather than going circles you could monitor the activity using PROFILER and take your time to go through the articles section on this website to take care of performance which is most required in any enterprise.
    Hope this helps.
  13. craigkornacki New Member

    Also, can SQL Profiler be used to just monitor transaction log activity. If so, how? Thanks again for your help!
  14. martins New Member

    What a trace will show you, is what queries are executed on the server and how long they take. Maybe there are not as many inserts/updates/deletes taking place as you expect and therefore would not need to worry about the log not growing quickly.
    I'm not too sure whether you can use the trace to monitor transaction log activity, but generally any insert/update/delete operation would have to be logged in the transaction log.
    Just another question though: Do you do a lot of periodic transaction log backups?

Share This Page