Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Transaction Log started growing a couple days ago

Posted on 2015-01-09
5
Medium Priority
?
358 Views
Last Modified: 2015-01-09
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
Comment
Question by:bmsande
  • 2
  • 2
5 Comments
 
LVL 20

Expert Comment

by:strivoli
ID: 40540386
Transaction Logs grow as you use SQL. You can shrink running backups and shrink jobs.
0
 
LVL 54

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40540453
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
 

Author Comment

by:bmsande
ID: 40540456
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
 

Author Comment

by:bmsande
ID: 40540458
Good to know about BPA @Victor.  Thanks.  I'll research your suggestion as it sounds like the likely reason and best fix.
0
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40540462
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question