Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Transaction Log started growing a couple days ago

Posted on 2015-01-09
5
Medium Priority
?
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 52

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 52

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

664 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