Solved

SQL REplication - RELPDATA folder

Posted on 2004-10-21
728 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
Question by:kacang
    5 Comments
     
    LVL 2

    Expert Comment

    by:skeeveswp
    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
    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
    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
    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:
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction After workin in a plethora of programming languages like C, Pro*C, ESQL/C, C++, VC++, VB, Java, HTML,JavaScipt etc, technologies and frameworks like JSP, Servlets, Struts, Spring, IBatis etc and databases like MS Access, SQLServer, Inf…
    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles al…

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now