t-sqldatabase-replication

Older Snapshot folder content is not deleting automatically (Snapshot Replication)


I am using SQL 2017, SSMS v19.0.2

I successfully set up snapshot replication, but had to disable it when I saw disk space being consumed.

The distribution cleanup job is running successfully but reports no deletions:

DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Deleted 0 row(s) per millisecond from MSrepl_commands [SQLSTATE 01000] (Message 22121) Deleted 0 row(s) per millisecond from MSrepl_transactions [SQLSTATE 01000] (Message 22121) Removed 0 replicated transactions consisting of 0 statements in 10 milliseconds (0 rows/millisec). [SQLSTATE 01000] (Message 21010). The step succeeded.

When I purposefully exec the stored procedure, I also get a success message but the files and folders remain:

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

If I understand things correctly, if I manually kick off the distribution cleanup job, I should get files deleted.

I have had errors from the snapshot agent, but I am working through those as separate issues. The two (unrelated in my mind errors I am working through):

  1. Database '...' cannot be opened. It is in the middle of a restore. (I can schedule things better.)
  2. Reference to database and/or server name in '...' is not supported in this version of SQL Server." (I will dig into that view, I am sure.)

I understand from another SE post, that:

The snapshot agent will only try once to remove the previous snapshot files so if those files were locked at that moment (by the merge agent e.g.), it is possible that snapshot files will be left over.

I don't think locking is the issue but these files are on a network share, so I am thinking a permission issue. I am perplexed that the enabled snapshot replication process that creates its own jobs can write the files to the location, but cannot delete them without at least reporting a failure to do so.

I am looking for advice on troubleshooting such as issue without the benefit of an error message.

  1. Does the absence of an error message itself suggest a root cause?
  2. Might I find an error message somewhere else, such as Event Viewer?
  3. I have verified that xp_cmdshell is enabled on the server.
  4. I believe I should be able to validate a fix is successful by manual execution of dbo.sp_MSdistribution_cleanup with parameters guaranteed to delete some of the files from disk. Can executing this remove files without the agent running? Please let me know if there is a better/different certificate of success.

Solution

  • In the end, I created a new snapshot folder with wide open security and re-created the replication using that folder. Then, it cleaned up itself as expected. So, there was some security issue with the folder it appears, that neither I nor the hosting company could determine.