Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question

migrated from serverfault.com Feb 9 '12 at 11:19

This question came from our site for professional system and network administrators.

    
@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 Feb 9 '12 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 Feb 9 '12 at 10:24
1  
@NimChimpsky - love the username, wonder how many will fully 'get it' :) –  Chopper3 Feb 9 '12 at 10:36
    
@NimChimpsky - moved my comments to an answer and tidied them up a bit –  Rob Moir Feb 9 '12 at 10:49

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
will it reduce automatically from then on, and I don't need to shrink ? –  NimChimpsky Feb 9 '12 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 Feb 9 '12 at 13:44

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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