July 8, 2009 at 3:09 am
Hello gents/ladies from this forum
I am currently testing a snapshot replication to a network shared unc location \\Server1\replication. Server1 is also used as the publisher/distributor to put snapshot files to the above-mentioned location on a scheduled hourly basis and Server2 is used as the subscriber to synchronize with Server1's publication.
Right now every hour one set of snapshot files (for example at 1 am, \\Server1\Replication\unc\SERVER1_****_PUBLICATION\20090708010002 and at 2 am, \\Server1\Replication\unc\SERVER1_****_PUBLICATION\20090708020002) are generated by Server1 on the shared folder, this eats up the disk space really quick. What's the best practice out there to clean up all the old snapshot files? Or did I miss something during setting up the publisher?
Thanks very much for your answers!
Bazinga!
July 8, 2009 at 4:06 am
I would say that if your replication scenario demands that you generate a new snapshot every hour, you are using the wrong type of replication and should consider Transactional Replication instead. It is possible to run the Log Reader Agent on a schedule rather than continuously if for some reason you don't want transactions to be replicated as they happen.
As for removing old snapshot files, I was under the impression that the generation of a new snapshot would remove the old snapshot fils and folder, but from what you say that does not seem to be the case.
You could add a CmdExec step to the Snapshot Agent job that would execute a script to remove the files. Alternatively, use a Scheduled Task but if there was a problem with the Snapshot Agent job you could get into the situation of removing old snapshot files before a new one had been generated.
Regards
Lempster
July 8, 2009 at 5:05 am
Hello Lempster
The hourly snapshot replication setup is just for testing purposes since I am a newbie for administration work (used to work as a SQL Developer for 1.5 years). After some research, I think I already traced back to the cause of the problem which lies in the job "Distribution clean up: distribution" which is generated through the replication wizard. In this job, a t-sql script "EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72" is run against database "distribution" and the error message from the job history log is as follows:
Executed as user: NT AUTHORITY\SYSTEM. Could not remove directory '\\Server1\Replication\unc\SERVER1_NORTHWIND_NORTHWIND_PUBLICATION\20090708003116\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
Also, I manually run the above t-sql directly against the "distribution" database and get the following error message:
Msg 20015, Level 16, State 1, Procedure sp_MSreplremoveuncdir, Line 83
Could not remove directory '\\Server1\Replication\unc\SERVER1_NORTHWIND_NORTHWIND_PUBLICATION\20090708100001\'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory.
Replication-@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only check rowcou: agent distribution@rowcount_only parameter must be the value 0,1, or 2. 0=7.0 compatible checksum. 1=only scheduled for retry. Could not clean up the distribution transaction tables.
So it seems that the xp_cmdshell security context might be the root cause of everything. Then I run the following script to enable xp_cmdshell
EXEC sp_configure ’show advanced options’, 1
GO
—- To update the currently configured value for advanced options.
RECONFIGURE
GO
—- To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
—- To update the currently configured value for this feature.
RECONFIGURE
GO
And the run value becomes 1, if I run "exec master.dbo.sp_configure 'xp_cmdshell'". But after setting up all this, the job "Distribution clean up: distribution" still fails to clean up all the old snapshot directories.
Any advice is warmly welcome! BTW, I am using SQL Server 2008 Developer Edition.
Regards,
Ning
Bazinga!
July 8, 2009 at 5:29 am
Hello Ning,
Well done for tracking down that it is the 'Distribution clean up' job that removes the old snapshot files and folders! I had considered that, but discounted it because neither the job nor BOL make any reference to snapshot files, merely data in the Distribution database.
You are running this job as 'NT AUTHORITY\SYSTEM; does that user have the necessary permissions on the location of your snapshot files? I would recommend running the job under a Domain account. The same goes for the agents involved in replication.
You mentioned that you ran the same T-SQL statement manually; trying running it using EXECUTE AS (some Domain user with write/modify permission on the snapshot file location) > as the first statement and see if it succeeds.
Regards
Lempster
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply