Post reply

Distribution Database Size not coming down

  • March 1, 2005 at 10:51 am

    #163743

    I have problem here where in the distribution database size is grown astonishingly and is not coming down. The concern is the Distribution database is on the same server as the subscriber is. The worst concern is they share the same disk array for their data files.

    I have come to a point that there is negligible disk space on these drives. But the distribution agent still running. It times out sometimes, and sometimes it says its waiting for response.

    Is there anyway I can fix it? can anyone please tell me what might be the reason for the distribution database to grow up so much?

    Also, is there any possible way to detach the distribution database, put the files on a different drive and then attach it back without bringing down the replication?????????? (I can afford to stop the SQL server agent).

    Help appreciated..

    Cheer

  • March 1, 2005 at 2:56 pm

    #544710

    Did you check the trunc. log on checkpt (SQL 7) or Recovery Model (SQL 2000) for the "distribution" DB?  Should be set to "truncate" or "Simple" model.

    You didn't say what type of replication (e.g., snapshot/transactional/merge) you have.

    Is it possible you have lots of activity?

    You might actually need to stop the replication to fix the problem.  Presuming the physical files are huge, you can shrink the "distribution" database files with: 

    DBCC SHRINKFILE

    or

    DBCC SHRINKDATABASE

    or better yet just blow away the whole replication, and recreate it from scratch, this time with the distributor located on a different server with more disk space.

    - john

     

  • March 2, 2005 at 3:58 am

    #544815

    Thanks John,

    FYI its a transactional replication and is a simple recovery model.

    I did try shrinking the log file of the distribution database using DBCC. But it did not help much. It only made the data file of the distribution database grab the free space.

    The subscriber and the distributor are set on the same server. Also that they share the same disk drive. So, I recon the distibutor does not fined enough disk space to replicate the commands on the subscriber and times out each time. and unless it writes on the subscriber it does not delete the commands from the distribution database.

    My concern now is why did the distribution databse grow to such an extent before even writing anything on the subscriber.

    What may be the possible reason for it to grow?

    -- To much of activity on the publisher which coused a delay in updating the subscriber and hence the distribution database kept growing since the log reader continued doing what its supposed to do. THIS MAY BE ONE OF THE REASON

    Any other reason??????????????

    Cheers

    Vikram

  • March 2, 2005 at 7:19 am

    #544867

    >> Too much of activity on the publisher which coused a delay in updating the subscriber and hence the distribution database kept growing since the log reader continued doing what its supposed to do.

    That would be the only reason i could think of.

  • March 2, 2005 at 11:36 am

    #544959

    Thanks a lot John

     

    I found the problem why the distribution database grew so rapildy and so big. Actually i had 6 osql.exe running on the subscriber server, which aparently were started fromt he command prompt. the command prompt was closed but the osql still kept running. so there were a huge amount of blocks created in various processes.

    These blocks which I ignored (my mistake) earlier were not allowing the distribution agent to write on to the subscriber. I killed the osql from the task manager and we were in business. The distribution database is now coming down.

    Thanks for you help...

    Cheers

    Vikram

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply