• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • Last Modified:

How do I reduce the size of a transaction log safetly

I have a database that has never had the transaction log backed up.  Therefore, it is 40GB in size, where the database file is about 750MB.  I've asked our VM guys to increase the size of the drive by 50GB to account for growth in the database file when I backup the transaction log.
My plan is to:
1.  Backup the database to a remote directory (not on the server).
2. Backup the log file to a remote location.
3.  Srink the log file to recover disk space.

Any thoughts or advice? I've been away from SQL Servers for awhile.
0
Jack Seaman
Asked:
Jack Seaman
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
Have a read of this article: http://www.experts-exchange.com/Database/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html

It should tell you everything you need to do.

One thing the article doesn't say .. you may need to perform the shrink twice (in order to get past any transactions already in flight when you do it the first time).

Alternatively, if you're not concerned about temporarily breaking your recoverability chain, set to simple recovery mode, following the advice in the article to shrink, set back to FULL recovery mode and IMMEDIATELY take a full backup.

Then, start backing up your logs to stop it happening again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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