SQL Server DB Backups and Transaction log fileseize

pts.
Tags:
SQL Server
I have a DB that has a DB filesize of 6 gb and a transaction log filesize of 66gb!!!! This DB has a potential for alot of growth because it holds scanned documents for retention and they are in the process of scanning in several years worth of documents from what I understand. I have my maintenance plan setup to backup the DB full overnight and transaction log backups hourly during the day. usually my transaction log backups are small - but on friday, the transction log backup was 66gb and it filled up the disk. after doing some cleanup, I ran a full and then a transaction log backup and everything seems OK. But the transaction log file itself remains at 66gb! I'm just wondering how I can optimize my Database AND especially my maintenance plans so that the transaction log gets back down to a manageable size?? Can I or should shrink the transaction log?? Should my DB be set to 'autoshrink'???????

Answer Wiki

Thanks. We'll let you know when a new response is added.

The only I have able to resize a transaction log is…1) do a full backup, 2) detached the database, 3) reattached the database, but not the log..sql will recreate a smaller log.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jpotucek
    Thank you. a Full Backup left the transaction log file at 66gb, so it sounds like my only recourse is to detach and reattach. My DB is setup for full recovery model. Should I also set it to auto-shrink inthe DB options???? I just want to prevent this from happening again.
    0 pointsBadges:
    report
  • swede27410
    User of autoshrink may cause performance problems, if the server happens to shrink the database during peak time. I perfer to control the shrinking myself through a scheduled job, maybe early the morning or on weekends. see DBCC SHRINKDATABASE.
    0 pointsBadges:
    report
  • Jpotucek
    Thank you very much!! Will using shrink database from EM also work????
    0 pointsBadges:
    report
  • swede27410
    yes, shrinking from em will only shrink the database (mdf) not the log (ldf)
    0 pointsBadges:
    report
  • Etheis
    You might want to read this article about shrinking logfiles. http://www.mssqlserver.com/faq/logs-shrinklog.asp
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following