Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Bangalore
    Posts
    3

    Smile Unanswered: Trimming Log File in SQL 2000

    Hi

    I wanted to know if there is any way we can trim the transactional log file in MS SQL 2000.
    My log file has reached 2 GB and has started usurping a lot of HDD space. Is there anyway to prevent/control the same.

    Regards
    Ryan

  2. #2
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    A nice feature that was added with SQL 2000 is that you now need to backup the database using the SQL Backup utility in order for the transaction log to shrink. I think that many of us were truncating the log at checkpoint in 7.0 and using a third party backup. We looked at a few things here and ended up setting up a backup device and then doing a complete backup once a day and appending a diff backup every three hours. Seems to do a good job on keeping the log at a reasonable size.

    Hope this helps.

  3. #3
    Join Date
    Oct 2002
    Location
    Bangalore
    Posts
    3

    Thanks

    The backup technique actually works. When before backing up the database my disk space utilised was about 3 GB data file and log file together. So I created a blank database. Imported the database from the old one. Dropped the old database. Created one more new DB with the old DB name and imported the data from the dummy DB. My disk space reduced from 3 GB to 250 MB.

  4. #4
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Ryan,
    Glad it works for you. From what you described on deleting and recreating the database to recover the disk space (If I understand it properly), I think that you could have used either DBCC SHRINKFILE or DBCC SHRINKDATABASE to accomplish the same thing. Books online has good descriptions on using these commands.

    If you setup a backup device that backs up the database on a regular basis and overwrites itself you should not have any future problems with the logfile disk usage.

    Brent

  5. #5
    Join Date
    Oct 2002
    Location
    Bangalore
    Posts
    3
    Hi Brent

    That exactly what I have done.
    Have fun.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •