Solved

high transactions on our database

Posted on 2011-03-24
4
301 Views
Last Modified: 2012-05-11
I just received the following email from my hosting company.

I have 2 questions.

1) In general what would cause transaction logs being so high
2) what are the effects of being in simple recovery model.

"Can i ask precisely what you're doing with the xxxx database at present ?

Yesterday some 77GB of transaction logs were generated, today has been 10GB - which is more than all the other databases on the server !

To ensure this database doesn't affect other users on the server, i've had to set your database to use a simple recovery model..."
0
Comment
Question by:frosty1
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 35208319
1. Insert/update/deletes are all logged transactions in SQL plust other operations like some DDL changes and a re-index for instance.

2. Setting database to Simple recovery will reduce somewhat transaction logging however this may impact your disaster recovery/point in time restore/log shipping capabilities.

http://msdn.microsoft.com/en-us/library/ms189275.aspx
0
 
LVL 7

Expert Comment

by:Todar
ID: 35208354
The log is so large because backups are not scheduled to run on it or not often enough.
0
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35208458
When my logs fail to back up, they will grow uncontrollably. Just last week, rights on the backup server were modified and I didn't catch it until the next day. The logs consumed the entire log drive, an extra 150 GB in 18 hours. Once I discovered the problem, I had to do a full backup and then another transaction log backup before I was able to shrink the log file back down to the appropriate size.

I believe @Todar is correct in that your hosting company is not backing up the logs frequently enough to keep them small. As @Icohan indicated, simply recovery will prevent restoring to a point in time other than your full backup.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35209379
Backing up the db's and t-logs should not be left for the hosting company to do but the owner of the database business rules should drive the backup/restore from disaster recovery point of view nut just regular maintenance. Backing up regularely the T-Logs will reduce the amount of disk space the T-logs are taking and indeed keep that under control but will NOT eliminate excessive logging. For that you may need to do a code review and eliminate unnecessary transactions that are filling up your t-logs.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

825 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