Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59

    Unanswered: Log file size increases over and over

    Hi everybody,

    I have a database in production server with 3,5 GB of data file size and 10 GB log file size. This is very strange isn't it?
    The features of this database are:

    SQL Server 2000
    Recovery Model = Full
    Auto Update Statistics = Yes
    Torn page detection = Yes
    Auto create statistics = Yes
    Full database backup taken once daily.
    No log backup is taken.

    So, I would like to apply some statregy to avoid the log file increase out of control. Can you give me your suggestions???
    My free disk space is very low.

    Thank you all,
    Nadia

  2. #2
    Join Date
    Jul 2004
    Posts
    191
    Since you do not backup the transaction log anyway, you can create a job to run on whatever schedule you choose to run code below:



    BACKUP LOG DB_Name WITH TRUNCATE_ONLY

    Lystra

  3. #3
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59
    This command didn't work...
    My log file size didn't decrease anything....
    Does someone can help me with another suggestion?
    Nadia

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try to shrink the database from SQL Enterprise Manager, or use the DBCC SHRINKDATABASE command from SQL Query Analyzer.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Before shrinking anything do DBCC OPENTRAN (db_name) to ensure there are no uncommitted transactions. Their presence wil;l prevent any attempt to shring log files.

    It's also clear that you don't want to take advantage of Full recovery mode (I wonder why?), so set it to Simple.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Dec 2002
    Location
    Brazil
    Posts
    59
    ok, now I got it!
    It worked....

    Thank you all....
    Nadia

Posting Permissions

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