Post reply

Transaction Log taking up too much disk space

  • July 22, 2008 at 12:15 am

    #191378

    I have a client running SQL Express. They use a small database but the transaction log is eating up the disk space. it is up to 28 gig.

    Backing up and shrinking did not work. I backed up took the backup to my test machine and restored it.

    I noticed the Recovery model is on Full.

    I changed it to simple.

    Then tried to shrink the log file.

    It went down to 1meg.

    If I do this on the live system, will the database run correctly?

    I noticed that the options for the log was to Enable Autogrowth by 10% but resctrict file growth to 2Gig. Is there a reason it would keep growing?

    Can I stop it from doing this again?

    Thanks in advance.

  • July 22, 2008 at 1:19 am

    #846193

    If the database is in simple recovery model the log is effectively used in a circular fashion, and will only grow large enough to cope with the largest oncurrent transactions. BUT if you lose the disc with database on it your best recovery option is the most recent backup.

    In FULL mode you can restore the most recent db backup AND the information from the log, to "roll forward" the database by re-applying all the transactions from the log. This can be very useful in maintaining continuity of employment! But this means the log will keep growing unless you organise regular LOG backups which will clear out the space in the log and allow it to be re-used.

    So in full mode something like daily log backups , plus weekly db backups may be sensible.

    How often you do log and database backups very much depends on your database size and transaction rate, so "daily" and "weekly" are justy examples.

    Hope that helps

    Mike John

  • July 22, 2008 at 1:32 am

    #846195

    Thanks. Now I have charge of the server I will backup the log file daily.

    What I need to do is reduce the size of the log file as it is using up what is left of disk space on the server.

    I backed up the database and logs and put it on my test PC.

    I tried shrinking etc but to no avail.

    So I changed the recovery model from Full to Simple.

    Then tried a shrink.

    This reduced the size of the log file to 1meg.

    I cannot test the db though. I can only do that on their live data.

    What I need to know is, by changing from Full to Simple recovery mode then shrinking the log. Is that going to do any damage to the database.?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply