Solved

SQL recovery model and merge replication

Posted on 2013-02-05
2
615 Views
Last Modified: 2013-05-13
Hi All,

I have a database that I wish to temporarily change the recovery mode on to simple so that I may forcefully shrink the transaction log(due to maintenance tasks screwing up and not reducing the file)

My question is :-

Can i change the recovery model to simple without breaking the merge replication on this database?? Then change it back to full?

Server : SQL 2008 server
Compatability level : SQL server 2000 (80)
Recovery model currently : Full
0
Comment
Question by:gardmanIT
2 Comments
 
LVL 4

Accepted Solution

by:
jjjosef earned 500 total points
Comment Utility
Merge will not be impacted by changing the recovery model.

You don't need to follow any extra steps for Merge, just manage the T-Log of the Database the same was as you do for other non replicated databases.

~ Edit by Modulus_Twelve - This post is unattributed content from http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/eb157294-5888-48da-91d9-1c8bb5e25486 ~
0
 
LVL 4

Author Comment

by:gardmanIT
Comment Utility
Thank you.. it got to 185Gb and I dont have the spare room to get that down.. i assume to get that down "normal way" I would just backup the transaction log and then do a shrink returning freed space to operating system..??

I am going to run the following command out of hours...



-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE KLSTOCK
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (KLSTOCK_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE KLSTOCK
SET RECOVERY FULL;
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now