Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL REplication - RELPDATA folder

Posted on 2004-10-21
5
Medium Priority
?
816 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:kacang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:skeeveswp
ID: 12383128
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
0
 

Author Comment

by:kacang
ID: 12386583
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.
0
 
LVL 2

Expert Comment

by:skeeveswp
ID: 12387389
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:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_271q.asp

-- Steve
0
 

Author Comment

by:kacang
ID: 12397502
Steve,
  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.
0
 
LVL 2

Accepted Solution

by:
skeeveswp earned 1000 total points
ID: 12401249
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



0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

618 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