Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    17

    Question Unanswered: MS SQL 2000- Reduce transaction log size

    The size of my transaction log file is out of control. I've backed up the database and the transaction log went from 120 GB to 120 MB. Now, I can't reduce the size of the transaction log file. It's still at 120 GB (w/ almost all of that being held as Free Space). I get errors when I try to manually reduce the file size. Any tips?

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    How did you manually reduce the log size? Did you use DBCC Shrinkfile?

  3. #3
    Join Date
    Jan 2004
    Posts
    17

    Lightbulb

    Thanks for the reply! I hadn't tried DBCC Shrinkfile. So let's say I want to reduce the log file to 10 GB - would I do something like this (?):
    DBCC Shrinkfile (LogFileName, 10000)

  4. #4
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    See BOL regarding DBCC statements.

  5. #5
    Join Date
    Jan 2004
    Posts
    17
    I just tried DBCC Shrinkfile and got:

    ------------------------------------------------------------------------
    Cannot shrink log file 2 (sde_log) because all logical log files are in use.
    (1 row(s) affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    ------------------------------------------------------------------------

    Any tips? Also, what's a BOL? is that like an FAQ?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sounds like you're dumping a lot of data without trying to control transaction size. If you're using BCP then use this switch:
    -b<number_of_rows_that_would_be_viewed_as_1_transac tion>

    If this "data dumping" is a reqular process, then along with "-b" and well sized trx log pre-allocated space you may probably get a good grasp on the log size and keep it under 1G.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2004
    Posts
    17
    Thanks for the tips. I've been uploading an unusually large number of unusually large files to a spatial database in SQL. This level of traffic is not typical of this database.

  8. #8
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Reduce transaction Log size

    I just ran into this yesterday for the first time. I highligted the database on SQL EM, right-click and went to shrink the database. Click on file and VERY IMPORTANT: click down arrow on database file so the LOG is showing. (Example: Northwind_log) This will come in handy if you log grow anywhere near the one I have. It's over 1,000,000 records per month.

  9. #9
    Join Date
    Jan 2004
    Posts
    17

    Talking

    HA!! That did it! Thanks, garrydawkins!

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You can do the same thing with a script by using DBCC SHRINKFILE. Here is an example from BOL:

    USE UserDB
    GO
    DBCC SHRINKFILE (DataFil1, 7)
    GO

    This would allow you to automate your shrink as a job.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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