2

Currently our transaction log is 50 GB; impressive huh?

I want to switch to simple recovery mode and remove the transaction log entirely.

Do I need to stop any user activity and detach the database. Or can I simply go into management studio and change the recovery mode. How do I then remove the file ?

Edit:

I switched the live server to simple recovery mode, and then performed a shrink file. Has worked perfectly so far. The file is now 1mb.

(I am aware switching to simple mode will restrict recovery : I won't be able to go to s specific point in time, only to when the last back up was.)

Improve this question
4
  • @RobMoir I said in my question I want to switch to simple recovery mode. I wanted to know the correct procedure for shrinking reducing the transaction log size.
    – NimChimpsky
    Commented Feb 9, 2012 at 10:12
  • @RobMoir after switching to simple recovery mode, which can apparently be done on a live server with no effects. Do I need to do anything to shrink the log file, or will it just start reducing down automatically over time ?
    – NimChimpsky
    Commented Feb 9, 2012 at 10:24
  • 1
    @NimChimpsky - love the username, wonder how many will fully 'get it' :)
    – Chopper3
    Commented Feb 9, 2012 at 10:36
  • @NimChimpsky - moved my comments to an answer and tidied them up a bit
    – Rob Moir
    Commented Feb 9, 2012 at 10:49

2 Answers 2

Reset to default
3

Don't remove the files! In simply recovery mode, the transaction log files are still used by SQL, they are simply being discarded periodically, even without log backups, so as not to consume to much disk space.

Yes, you can just go in and change the recovery model in the GUI while the database is online, should not be a problem.

Improve this answer
2
  • will it reduce automatically from then on, and I don't need to shrink ?
    – NimChimpsky
    Commented Feb 9, 2012 at 10:14
  • You will have to shrink it manually. See the documentation for DBCC SHRINKFILE. But don't shrink it down so much that it has to grow back up to a workable size. You can use DBCC SQLPERF(LOGSPACE) to get an idea of how much of the log is in use (probably very little in simple recovery mode).
    – db2
    Commented Feb 9, 2012 at 13:44
4

What exactly do you mean by "remove the transaction log entirely"? I only ask because if you mean that literally the only possible answer is "You can't do that and have a working database".

This is a good article to explain the implications of changes to recovery mode and what it does for you.

Can't remember if all the steps are needed, but what I did last time was change recover mode to simple, carry out a backup then carry out a file shrink operation on the database (Right click on the database in the management centre and choose the option to shrink files that's in there somewhere)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.