Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: Help a MSSQL Noob Manage Growing Log Files

    It seems the one thing I'm doing consistently is managing 2012 production SQL instances where the transaction logs are growing at an alarming rate (usually due to a failed or stopped backup job). Can you SQL Server veterans please reference me with some decent guides on how to correctly handle out of control log files spanning the entire free space on disk? What is the best reactive solution for remedying this common issue I see. I'm sure there are several best practices NOT being implemented currently which results in my log files growing out of control but for now I'd like to analyze the actual issue and what is the fastest / safest way to fix?

    *I don't mean shrinking the data file(s) on data partition to reclaim disk space.* I understand that is a huge bad practice no-no.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Most monitoring packages will have a transaction log used % counter that you can configure alerts on. Once the transaction logs are properly sized, you should only have to worry about one-time or special events, such as large data deletes/updates, archiving, or a failed transaction log backup. One important thing to note is that shrinking the transaction log files regularly is a bad thing, but after a special event such as the ones above, it can be acceptable.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First and foremost, configure the SQL Agent jobs that are failing to notify you.

    Email is one choice, but every cell phone I've ever owned has had an email address for sending it SMS/MMS messages... Depending on your email server, your SQL Server, and other factors the steps will vary but you can get the SQL Agent to send you a message when a scheduled job fails.

    This notification will allow you to fix the root cause of the problem instead of trying to find ways to triage the effects.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Also, check for any active open transactions using DBCC OPENTRAN that may be preventing log truncation and causing the logs to grow at an alarming rate.

Posting Permissions

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