[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

MSSQL Server Transaction Log Growth Problem

I have an MSSQL Server 8.0.2039.
I have 4 user databases on this server.  The transaction logs for 3 of the databases grow normally and are usually ok to shrink with a script that can shrink it down.
The problem is with the fourth database.  It grows exponentially (usually about 2GB a day).  This database has our reporting data on it and is typically only connected when we are running reports.  There are 2 ways that we are using to connect.  One is with a VB program I created to do the reporting and copying of data from one database (Sybase) to the other.  The other way is with an Access DB Program.

I have no idea why this database is the only one that has a transaction log that grows exponentially.  Once a week we have to run the script that shrinks the transaction log.  This normally will take around 10 minutes to complete (where the others take only about 1 minute to complete).

Where can I look, what tools can I use to help diagnose why this transaction log grows so much?

Thanks in advance for any and all help!
0
ericlockshine
Asked:
ericlockshine
  • 4
  • 3
1 Solution
 
BryanMICommented:
Typically large transaction logs result from large amounts of transactions when you don't backup the transaction log.  If you aren't running transaction log backups, make sure that the database recovery model is set ti SIMPLE.  This is one option and it does not record everything to the transaction log.

If you are set to FULL or BULK-LOGGED, almost everything is kept in the transaction log for incremental backups.  The problem is that if you don't do the incremental backups, the log will grow until your disk is full or you dump the log.

To keep it from growing, set the option to SIMPLE.  To do this, right click your database and go to properties in SQL Enterprise Manager or SQL Management Studio, depending on the version of SQL you are running.  Go to the "Options" screen and you should see "Recovery Model".  This is where you would set that.

Once you change to Simple, you should be able to shrink the log and it should stay small.  It will grow a little, up to the size of the single largest transaction you run.  After that, it should hover right around that size indefinitely.
0
 
ericlockshineAuthor Commented:
Thanks BryanMI,
I have a couple more questions....
We are a 24/7 operation.  Can this be done on a live database? or would we need to shut down the database to change this? Is it also possible to make a script to run at a certain time to make this change?
Also, what other kind of impact could this have by making the change to a simple log?

If we decide not to change to SIMPLE logging, is there a way to deterime exactly which transactions are being stored?  And if we do change to simple logging and the log file still grows large is there any way to see what is happening?

Thanks in advance!
0
 
BryanMICommented:
It is absolutely something you can do without shutting down.  You can even truncate the T Log without shutting down should you need to clear it.

The only impact in making this change would be for backups and recovery, hence the name recovery model.  Basically, check out your backup scheme.  If this is a mainstream production database, you should probably leave it at full and have transaction log backups running no less than once per hour.

If it's relatively static and a daily backup is acceptable, the simple recovery model would work great.  The ramifications of it are solely that you lose the ability to backup the transaction log which would let you restore right up to the last log backup, or point in time.

If you go simple and the log file continues to grow (which would really surprise me), run SQL Profiler against your database and see what kinds of queries are hitting the database.  That should give you some indication inside SQL Server.

Finally, you could download/purchase a product like Log Explorer (google it) that would let you see inside your transaction log to find out exactly what is taking up so much space.

Bryan
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BryanMICommented:
Oh and yes it can be scripted:

alter database mydbname set recovery SIMPLE

-OR- (to revert to full recovery model)

alter database mydbname set recovery SIMPLE
0
 
ericlockshineAuthor Commented:
Thanks for your help!
Pretty much what we store here is data from the other databases, like reporting on orders and calls and the like. We also store basically static things (other than the call logs and order logs) in this database.  So we would be able to use SIMPLE only because the data that we store here is actually from some other databases (Order) and Call logs are copied from Sybase.

0
 
ericlockshineAuthor Commented:
I'll probably run this first thing Monday morning. (I don't want to have to go into the office again over the weekend if I can avoid it).  I'll let you know how this works out!
0
 
ericlockshineAuthor Commented:
I was finally able to update the transaction logging last week.  We are still having an issue that is causing the transaction log to grow (but not nearly as much as before).  We will continue to monitor it but is the mean time, thanks for your help.  It has really helped a lot.

0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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