Feb 9, 2012
tom

sql server 2008, switching to simple recovery mode

Question

Currently our transaction log is 50gb; 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.)

Asked by NimChimpsky

Answer

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.

Answered by Mathias R. Jessen

No related posts.

Leave a comment