Post reply

How to manage increased log file in SQL Server 2000?

  • September 15, 2006 at 6:04 am

    #91255

    Hello Friends!

    I am facing the problem of increased log file of my several databases.

    Following are the database data and log file details:

    Sr. No.

    Database Name

    Size of Data File

    Size of Log File

    1

    DB1

    811,584 KB

    1,219,712 KB

    2

    DB2

    3,390,848 KB

    4,240,704 KB

    Awaiting for your pink and healthy response,

    Regards,

    Umesh.

     

     

     

  • September 15, 2006 at 6:28 am

    #660664

    Umesh

    Large transaction logs aren't necessarily bad in themselves in an OLTP database.  Are your databases in Full or Simple recovery mode?  How often do you back up the transaction logs?

    John

  • September 15, 2006 at 7:39 am

    #660696

    Hello John!

    Thanks for the reply.

    Well! normally we take backups of entire database, everyday at 12:00 PM.

    And i don't have much insight @ recovery mode.

    So, can you give some tips on this bases.

    Normally, we request excel template out of this data and hence majority of the time log file increases tremendously.

    Regards,

    Umesh.

     

  • September 15, 2006 at 7:50 am

    #660703

    Umesh

    To see what the recovery mode is, right-click on the database in Enterprise Manager, choose Properties, and click on the Options tab.

    Backing up the entire database doesn't truncate the transaction log, and this is why your log is growing.  12:00 PM?  That's midday - are you sure you don't mean 12:00 AM?  Anyway, you should, at the very least, include a step in your backup job that truncates the transaction log before you do the full backup.  Look up BACKUP LOG in Books Online.  You may also wish to run a regular log backup during the day - hourly, perhaps.  Or you could create a job that backs up the log when it gets to 80% full, say.  I have posted instructions to do that on this site before, so you could search for it if that's what you want to do.

    I recommend that you spend half a day searching sites like this one, and Microsoft, for TRANSACTION LOG, and BACKUPS, so that you can familiarise yourself with how this all works.

    Good luck!

    John

  • September 15, 2006 at 10:00 pm

    #660864

    Hello John,

    Thanks for your reply.

    Regards,

    Umesh.

Viewing 5 posts - 1 through 4 (of 4 total)

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