Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

SQL Transaction Log started growing a couple days ago

SQL 2008 R2 single instance, hosting one database

This week I was performing an analysis of our server to make sure SQL is configured in an appropriate manner.  Using this question to guide me.

Since I started my analysis, our transaction logs have grown from 700 MB to 90 GB.  I wasn't making changes that I'm aware of.  I ran the Best Practice Analyzer and a few select statements.

What could cause my transaction logs to grow?  How do I get them back to a manageable size?
0
bmsande
Asked:
bmsande
  • 2
  • 2
1 Solution
 
strivoliCommented:
Transaction Logs grow as you use SQL. You can shrink running backups and shrink jobs.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
BPA runs a lot of tests and for some of them it will need to create and drop objects and that's why the transaction log grows. As said above you need to perform a transaction log backup and then shrink the transaction log file.
0
 
bmsandeAuthor Commented:
This system has been alive for years.  We've never seen our transaction logs grow beyond 2 gig.

Since Monday, (5 days ago), our transaction log is 90 gig and rising.  

Something changed, that's what I need to figure out.  Then I need to fix it.
0
 
bmsandeAuthor Commented:
Good to know about BPA @Victor.  Thanks.  I'll research your suggestion as it sounds like the likely reason and best fix.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Since I started my analysis, our transaction logs have grown from 700 MB to 90 GB.  I wasn't making changes that I'm aware of.  I ran the Best Practice Analyzer and a few select statements.
I think you already figured it out :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now