INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Large transaction log file

Large transaction log file

(OP)
We have a 20M database but a 140M transaction log file. Attempting to shrink the log files only gives me about 10M back. I have done tlog backups as well as full backups but the log file seems to stay the same. Would it be safe to setup the backup to simple then change it back to full after a backup? Any other solution you can recommend?

Thank you in advance.

RE: Large transaction log file

1) how much does your database change? this includes rebuilding indexes
2) is the database in log shipping?
3) does it matter if point in time restore is lost for that period?
4) are there uncommitted transactions?

I see you are backing up the log file and the database both so that is a good thing.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

RE: Large transaction log file

When the database was created what was the size used for the log file? Maybe you can't shrink it because that is the size someone decided the log file should be?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Large transaction log file

(OP)
1) how much does your database change? this includes rebuilding indexes
Very little. We have about 10 users out of which only 2 are considered heavy users
2) is the database in log shipping?
No log shipping. We have a single SQL2005
3) does it matter if point in time restore is lost for that period?
We do daily full backups, every 6h tlogs. Not messing with incremental or differential since our dbases are small.
4) are there uncommitted transactions?
Just checked them and no uncommitted tlogs are present.

Thanks in advance again.

RE: Large transaction log file

As SQLBill indicated check the size restrictions, then during off hours, after a full backup you can do as you thought. Change to simple, shrink, change to full.

This should shrink the size, however you will need to watch the size over the next few days to see if there is excessive growth.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

RE: Large transaction log file

(OP)
Thank you Djj. I'll attempt these changes over the coming weekend.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close