SQL merge replication snapshot

Hi all,

how can I check where the SQL merge replication snapshot store? if we are running out of disk space, we have to clean up some space, can we delete the replication snapshot without affecting the existing running replication ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Hi Marrowyung,
In SQL Server Management Studio, please expand the Replication node and then the Publications node and then right-click on your publication and choose Properties.

In the Publication Properties dialog, select the Snapshot page and then look for the 'Location of snapshot files' in the right of the dialog about half way down.
Here the snapshot files folder is specified.

Please let me know if this helps.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Barry Cunney,

you can explain everything clear and please help me more on replication, just like this:

http://www.experts-exchange.com/questions/28815399/Constraint-causing-replication-problem.html#a41213765

I am on a new company for 2 months and we have merge replication and it seems each deployment we will have problem on replication, some change like "table partitioning" can kill replication and I am on the path of learning this,

please help more.

I found that and one question is, once the merge replication is ongoing, can I delete the folders inside to save disk space?

it seems that we can zip everything inside ?
0
Barry CunneyCommented:
Hi marrowyung,
You can find out if your replication is set up to replicate DDL changes by using the following command:

sp_helpmergepublication 'PublicationName'

When a new Publisher is being created, it is possible to set Article Properties and in Article Properties for Tables there are the following properties which are relevant to Table Partitioning:
Copy table partitioning schemes
Copy index partitioning schemes
These can be set to True or False

On an existing Publication, you can right-click on the publication, choose Properties and in the properties dialog, you can select the 'Articles' page and the Article Properties.

SQL Server uses the retention setting to determine how long the snapshot is good. After the maximum retention time has expired the snapshot is of no use anymore and can be deleted. The default for this setting is 72 hours
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
"On an existing Publication, you can right-click on the publication, choose Properties and in the properties dialog, you can select the 'Articles' page and the Article Properties."

wait, what is that for ? to set table and index partitioning schemes for replication to copy to subscriber?

"SQL Server uses the retention setting to determine how long the snapshot is good. After the maximum retention time has expired the snapshot is of no use anymore and can be deleted"

but before that period, let say I found the disk is out of space as the retention period is too long and I tried to remove some snapshot, is it possible for me to delete it first BEFORE retention period end ?
0
Barry CunneyCommented:
Hi Marrowyung,
Yes the Article Properties 'Copy table partitioning schemes' for a table are to copy the partition design for a partitioned table to the subscriber - so if you use table partitioning you should explore this option.

Ideally it would be best practice to set up a 'test' replication environment to test these features with your specific database.

With regard to deleting the snapshot files/folders, I would exercise caution.
Look at these folders and see if you can find the folders/files with the oldest date.
I am not sure if it would definitely help, but I would copy the oldest one off the drive with space issues
as opposed to just deleting, even just to have it for cross-checking.
Before doing this maybe try to rename the folder/files and see if anything complains.

Long term you need to take measures to try and automatically and proactively manage the space on the snapshot drive location.
In the Publication properties 'Snapshot' page it is possible to specify a folder for the snapshots and there is a checkbox to Compress snapshot files.
When replication is configured there is a 'Distribution clean up: distribution' clean up job which is also created. So  ensure that this job and the 'Agent history clean up: distribution' job is scheduled to run periodically and this should handle the clean up of old snapshot files.
Details of these can be seen in the Agents tab in Replication Monitor.

Also a general tip would be to set up a SQL Server Agent alert which sends an e-mail alert when space on any drive drops below a specified threshold, so as you can detect space issues before they become a problem.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"Yes the Article Properties 'Copy table partitioning schemes' for a table are to copy the partition design for a partitioned table to the subscriber - so if you use table partitioning you should explore this option.

 Ideally it would be best practice to set up a 'test' replication environment to test these features with your specific database."

tks and I will take a look later.

"With regard to deleting the snapshot files/folders, I would exercise caution."

I just keep seeing our DBA in US keep doing it whenever replication failed just because a new article added, he just try to save HDD space by deleting the existing snapshot of any failed SQL replication job just because new article/table added to the publication .

then he start the SQL replication job again and the new snapshot folder by the name with snapshot creation date and time will be created, inside that we can see a lot of .bcp files with the size keep growing.

"I am not sure if it would definitely help, but I would copy the oldest one off the drive with space issues
 as opposed to just deleting, even just to have it for cross-checking."

sometime our DBA in US just zip it and move it to somewhere else.

but copy it out from the same disk don't help out the issue, right?

"Long term you need to take measures to try and automatically and proactively manage the space on the snapshot drive location.
 In the Publication properties 'Snapshot' page it is possible to specify a folder for the snapshots and there is a checkbox to Compress snapshot files."

I check publication properties and distributor properties, I don't see that option , any idea? what is your page if you can show me.

"When replication is configured there is a 'Distribution clean up: distribution' clean up job which is also created. So  ensure that this job and the 'Agent history clean up: distribution' job is scheduled to run periodically and this should handle the clean up of old snapshot files."

you are talking about history retention date from distributor properties sheet ? usually it should say how many snapshot it will keep but I don't know

"Also a general tip would be to set up a SQL Server Agent alert which sends an e-mail alert when space on any drive drops below a specified threshold, so as you can detect space issues before they become a problem. "

we have but we want to know more on how to control snapshot's behavior!
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

"sp_helpmergepublication 'PublicationName'"

so if replicate_ddl is 1 then it means partitioned table can be replicate to subscriber?

but the:

Copy table partitioning schemes
 Copy index partitioning schemes

option is set to false.

how come?

"In the Publication properties 'Snapshot' page it is possible to specify a folder for the snapshots and there is a checkbox to Compress snapshot files."

I see there are 2 x options:
1) Put files in the default folder
2) Put files in the following folder

OMG, they are exactly the same option and why it has 2 ?

can I just copy the path in the default folder to 2) one and click the compress option? will SQL server then start to compress that folder I copy from the default folder?

and once doing this we will not have long time performance impact except during the initial compress stage, right?

"When replication is configured there is a 'Distribution clean up: distribution' clean up job which is also created. So  ensure that this job and the 'Agent history clean up: distribution' job is scheduled to run periodically"

the one for me is every 10 mintues, not possible, right? or is just relies on the parameter the job calls?

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

Open in new window


which is 72 hours?
0
marrowyungSenior Technical architecture (Data)Author Commented:
please also answer my last concern. which is the last part of this ticket.
0
Barry CunneyCommented:
Hi Marrowyung,
Please fully clarify your last concern and I will try to help you.

Thank you
0
marrowyungSenior Technical architecture (Data)Author Commented:
"sp_helpmergepublication 'PublicationName'"

 so if replicate_ddl is 1 then it means partitioned table can be replicate to subscriber?
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi are you in holiday ? wish you happy new year.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.