SQL REplication - RELPDATA folder

Hi guys,
   I am running replication for about 50 tables from one of my database to 2 other databases. Recently, i found my hard disk being utilised severly. I have tracked the source to the folder above (REPLDATA folder) as this is a folder used for the replication process, how do i clear this folder to generate more space in my hard disk? I am afraid my hard disk might fill up too soon causing my server to crash.

  Hope someone can provide a sound recommendation. Thank you.
Who is Participating?
The key is to retain all folders since the latest snapshot.  There are two elements to transactional replication, the snapshot and the transaction log.  The snapshot has a representation of the total database at a point in time, and each replication after the snapshot has the transactions that have occurred since the last replication (the snapshot counts as a replication).  Each snapshot actually has two pieces, by the way ... the snapshot plus all the transactions that occurred while the snapshot was being processed.

You can look at it rather like doing incremental backups.  First you run a FULL backup (like a snapshot) and then you can run INCREMENTAL backups from there.  When you want to restore a backup, you first have to restore the FULL and then all the INCREMENTALs since the last FULL.

If you need to restore a database in the field, you will need to have the latest snapshot and each replication run since the snapshot to make a complete picture.  So what you need to keep will be the last snapshot and all the replication runs since that snapshot.  In setting up transactional replication, you have to choose a snapshot interval (usually based on the size of your database and how long it takes for a snapshot to complete) AND a replication interval.  Referring back to the Incremental Backup scheme, what you need to keep is your latest FULL (Snapshot, in DB terms) and all the INCREMENTALS (replication runs) since then.

It sounds as though your system may be retaining all the old snapshots as you go along, and you really should not need them.  You may be able to modify your snapshot job to purge the old snapshots, and replication data between snapshots, once the new one is transferred and verified.

Just to be prudent, I would initially make a temporary location for the old folders and move all but those since the last snapshot to that location ... and, of course, get them on backup media.  Then, if there are no problems, you should be able to delete them to free up disk space.  From the timestamps on your folders it looks as though you are transferring data every hour, so I would hold the old folders in a test location for at least a couple of snapshot cycles before deleting them.

What is your snapshot interval?  You should be able to check your snapshot agent to find out how it is set up.

-- Steve

What version of SQL are you running?  What kind of replication are you using?  What is the relationship between your databases (same server, different server, etc.)?

-- Steve
kacangAuthor Commented:
Thanx Steve for your response. The info you are seeking is as below:

1. The source database is running on MS SQL 2000. The target databases are 2 MS SQL 2000 and a DB2 ver 7.1
2. The type of replication I am using is transactional replication from MS SQL 2000
3. All databases are residing on different servers.

FYI, currently all replications are woking fine. It is just that the server which is holding the source database is filling up quite fast atht e above said folder. I was thinking if deleting all files and sub-folder off that folder but was adviced against doing it from another friend before I run this problem thru the experts (and this is what I am doing now, lol...).

I hope the info I gave is enuf for you to advice me. Thank you.
Since you are using transactional replication, and I am assuming that your REPLDATA folder contains the distribution database, I am wondering whether you have your snapshot agent set to run automatically.  The issue is that the folder has to be large enough to accommodate the latest snapshot AND all transactions since the latest snapshot.  When you execute a snapshot, there should be a cleanup phase that will remove all transactions from the distribution database that are covered by the snapshot, and that should free up space.  If you are running the snapshot agent on an automatic schedule, then perhaps you need to set it to run more frequently.

At any rate, I would check the timestamp of the latest snapshot to see what is going on in that regard.  It it has not been run for a while, try doing a manual snapshot and see if that shrinks the distribution database size.  If the agent is running properly, then it should not be necessary to keep any but the latest snapshot ... although I would want the others on some kind of backup media.

If your distribution database is in the REPLDATA folder, then the growth is probably either from multiple snapshots or a bloated database.  In either case, you don't want to simply delete all the files ... that would require a good deal of work in re-synchronizing the databases, among other things.

For more information on disk size requirements for transactional replication, check the following article:

-- Steve
kacangAuthor Commented:
  Just to confirm, the sub-folders in the REPLDATA folder which are in the form of dates other than the latest dates can be deleted. Is that right?.. (i mean after i have backed them up that is)

eg. in the REPLDATA folder i have 2 folders namingly 'ftp' folder and 'unc' folder. say, i have another sub-foder called ReplAtoB, and in this folder i have many more sub-folders named by date-time, 20040805180807, 20040805190005, ... (here i should only keep the latest and delete the rest, right?...)

Thank you.
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.

All Courses

From novice to tech pro — start learning today.