?
Solved

SQL recovery model and merge replication

Posted on 2013-02-05
2
Medium Priority
?
675 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 2000 total points
ID: 38854836
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
ID: 38855236
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

850 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