distribution database growing huge

Question

distribution database growing huge

ALIF-662928

SSCertifiable

Points: 6054
More actions
September 15, 2008 at 9:26 am

#192828

I am implementing T-replication, my distribution database file is huge now approx 60gb, is it transferring the data to the subscriber, if not whats the way to send this to the subscriber end, i see the synchonization status its receiving transactions, Please help me to find out why the distribution database is growing so much and what is the way to check if my transactions are moving from distributor to subcriber.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Do you have a job to delete the replicated transactions? There should be a job that runs the distribution cleanup procedure sp_MSdistribution_cleanup. If it is there, make sure it is processing.

You can right click on the subscription to validate it but if you are seeing it processing then my guess is that you are not cleaning out the distribution database.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 2

yes, there is a job name "Distribution Clean up: distribution", It was not running since many days, i started it but its running more than 30 mins now, so after this job is done does my distribution database will be free from space its using right now.

Thanks

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Yes, it should now go back to a more normal size when that job is complete. Don't be surprised if you see blocking occurring while the job is running. You may want to limit the duration to shorter bursts until you can get the old data cleaned up and then it should process fairly quickly unless you have a ton of transactions being replicated. You will have to shrink that database in order for it to return to its normal size.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 4

Thanks David,

I hope everything will be alrite after this job is finished, we have this job running every 10mins, so after this is completed i dont think it will be that long for the next schedule job to run for this much time. Since this job is running for quite a while, does it affect replication or performance.

Thanks

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 5

Yeah, you should limit the run time. Example, let it run for a couple of minutes then stop the job until you can get things cleared out. As stated in previous post you will experience blocking where replication efforts are being blocked which always affects performance. The other option, and probably the safest, is to wait until you have some low utilization time, maybe tonight, to have the job run and then let it run through to completion. Then have the schedule pick back up at the 10 minute run cycle.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 6

yeah, i will run this tonight after hours, and will see the size then also just see my post tomorrow, if i need your help again. I stopped the job right now, will run tonight and update you,

Thanks for your help David.

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 7

The distribution cleanup job ran successfully last night, and now its showing free space in the files, i tried to shrink it but its not shrinking, can anyone send me the syntax to shrink the distribution database,

Thanks

dbaker-620086 SSCrazy Points: 2336 More actions · Answer 8

LogReader writes to distribution

distrib.exe (Distrib Agent) jobs read from distribution

various jobs tidy up (eg after all subs have received changes)

Agent history clean up: distribution

Distribution clean up: distribution

Expired subscription clean up

Replication agents checkup

where these run at appropriate intervals (e.g. #2 every 10-minute interval)

you should NOT disable these, and stopping them is equally UNADVISED !

- transactions may contain MANY statements and you might break the flow

so just let them run as they will (no problem if exceed the 10 minute span as SQLAgent will just let the job tick on until finished).

distribution should behave like any other db (recovery=SIMPLE), so you may have to try several variants, e.g.

USE [distribution]

GO

DBCC SHRINKDATABASE(N'distribution', truncateonly )

GO

DBCC SHRINKDATABASE(N'distribution' )

GO

DBCC SHRINKFILE (N'distribution' , 0, TRUNCATEONLY)

GO

DBCC SHRINKFILE (N'distribution' , 1000)

GO

Dick

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 9

Thanks to both David and Dick,

I ran the shrink command

USE distribution

GO

DBCC SHRINKDATABASE(N'distribution' )

GO

and it released all the unused space.

Thanks again for your help.