SQL Server Performance

.ldf file growing but not .mdf

Discussion in 'SQL Server 2005 General DBA Questions' started by aarvind1, Jan 5, 2010.

  1. aarvind1 New Member

    I have an issue that I have not seen before. I am a beginner with SQL server so I have tried what I know but no success. I have SQL 2005 SP3 (9.00.4035.00) on one of the SQL DB servers. There are two databases on this server. I have noticed that for last 2 weeks or so the .ldf file grows but the .mdf file will only get modified once if that. I have had to backup the DB and then truncate the log file as it starts throwing tran log full errors. I ran consistency checks on the DB (DBCC checkdb(test)) and no errors found. I tried to change the recovery model from "Full" to "Simple" and back to "Full". This has not helped. Has anyone see this before. I would appreciate any assistance with this.
  2. satya.sqldba New Member

    First question, do you have auto growth enabled on the transaction log file? Go to properties of the database and check the tab 'files' and then 'autogrowth'. If there is no auto growth enabled on the log file, your log file will grow to the initial size it was set and will throw tran log full error messages. Enable the auto growth to fix this error message.
    If you have that enabled and still get this messages, it means that your log file is growing so huge that it is occupying the whole disk on the server and throws the error messages because there is no more space on the disk for the file to grow. In this case you need to check the transaction batches that are coming into your database and tune them (split them into batches and not a single huge transaction) so that they don't occupy the whole space in transaction log and prevent it from reusing the space.
    I am guessing your problem may turn out to be the first possibility that I mentioned above.
    Good luck!
  3. aarvind1 New Member

    The files are set for autogrowth by 10% restricted to 1GB MAX. The issue is that the .MDF file only gets updated once a day while .LDF gets udpated 3 to 4 times a day and grows about 150MB at a time.
    I will schedule a nightly backup of the the tlog to see if that helps. My main concern is that .mdf file is not changing as I would think that that should change often as the data is being written.
  4. satya.sqldba New Member

    Is there a reason why you set the max growth for a t-log file to be 1 GB? If you have enough space on the disk, please set it to reasonably large. I personally would never set a limit of 1 GB for a log file if there are active transactions going on.
    When you say 'the .mdf is not changing' , are you looking at the physical size of the file or the data space used within the data file? If you are looking at the physically allocated space on disk, then you will not see a growth until the data file is fully filled up logically.
  5. aarvind1 New Member

    Due to HD space constraints. I have increased it to 2GB as it grows about 1.5GB per day. I have also added a nightly job to backup tlogs.
    Thanks for all your help. Appreciate it.
  6. rohit2900 Member

    But still I want u to go back and analyze again as taking tlog backups once a day that too in night doesn't make any sense to me...... and if this is the situation then do u really need full recovery???
  7. moh_hassan20 New Member

    I agree with rohit , no need to full backup , except you schedule backup the log ,for example , every 4 hour (or a reasnable period) for disaster recovery
    otherwise use simple recovery model
  8. aarvind1 New Member

    Thanks Rohit and moh_hassan20. I appreciate all your help and quick responses. Our expectable DR policy is to go back to previous night backup and restore. The issue is that the server was rebooting randomly every morning (for last 2 or 3 weeks) which I am having our hardware team look at. The server reboots is the only reason I would keep the full recovery as the transactions can be useful when the server comes backup.I am checking with the management team to see if they are still ok with the previous night back up and if they are then I will be changing the recovery model to simple.One thing that is still not clear to me is if I take a tlog backup and if I have a full backup after the tlog backup. Do I need to keep the tlog backup? For example, tlog backup occurs at 11pm and a full db backup occurs at 12am. If I need to restore next day I should be able to go to full backup and restore right?Also, the reason I am taking a tlog backup at night is after increasing the tlog size to 2GB it allows more than full day of work so that is why I scheduled it at night so it does not impact business.
  9. rohit2900 Member

    [quote user="aarvind1"]
    Our expectable DR policy is to go back to previous night backup and restore.
    [/quote]
    You can achieve this by simple recover and daily nightly full backups and make sure u keep a copy of backup to some network share.
    [quote user="aarvind1"]
    The server reboots is the only reason I would keep the full recovery as the transactions can be useful when the server comes backup.
    [/quote]
    I didn't undst this as I don't think in this case full recovery without tlog backup will help you.
    [quote user="aarvind1"]
    One thing that is still not clear to me is if I take a tlog backup and if I have a full backup after the tlog backup. Do I need to keep the tlog backup? For example, tlog backup occurs at 11pm and a full db backup occurs at 12am. If I need to restore next day I should be able to go to full backup and restore right?Also, the reason I am taking a tlog backup at night is after increasing the tlog size to 2GB it allows more than full day of work so that is why I scheduled it at night so it does not impact business.[/quote]
    See this is of no use unless your server crashes after tlog backup and before next full backup i.e. between 11 Pm & 12 AM. once you have a full backup all the tlog backups prior to that are useless, unless you want to go back to some point in case of any accidental update or delete.
    And taking tlog backups doesn't impact server performance & tlog backups are the backups which we should take in peak time as in my company we're taking tlog backups every 30 mins from 8AM to 6PM and nightly full backups and the logic is such that the first tolg backup will over write the yesterdays tlog backup as I've already taken full backup.
    Hope its more clear now.
  10. aarvind1 New Member

    Thanks for the explanation. This makes sense now. Appreciate the quick response.
  11. rohit2900 Member

    Hi Aarvind,
    Welcome to forums....
    I got your issue.... now in order to resolve this you need to understand how transaction log works for each recovery model.
    Whenever your db is in full recovery you need to take tlog backups on a constant intervel as tlog will continue to grow unless you take log backup, so my suggestion is to check whether you really need full recovery or not.... if not then it will be a good idea to keep you db in simple recover else you need to schedule log backup which will prevent the ldf file to grow.
    Tell me if this helps....!!!
  12. MohammedU New Member

Share This Page