• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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