Discussion:
Is it Possible to Shrink Distribution.mdf ?
(too old to reply)
Stuart
2008-03-03 22:16:01 UTC
Permalink
I've been replicating for over a year and this file has grown to twice the
size of my biggest db.
I've tried shrinking, the ldf will shrink but the mdf doesn't change.

Any suggestions ?

Thanks
Rubens
2008-03-03 23:31:25 UTC
Permalink
That seems very odd, almost like transactions are still in the database. Do
you get a lot of records returned when running the following?

exec distribution.dbo.sp_browsemsreplcmds

Rubens
Post by Stuart
I've been replicating for over a year and this file has grown to twice the
size of my biggest db.
I've tried shrinking, the ldf will shrink but the mdf doesn't change.
Any suggestions ?
Thanks
Stuart
2008-03-04 16:19:04 UTC
Permalink
Hi Rubens,

Thanks for responding.

Yes, after 20 minutes, it has generated 50k plus records and is still
running...
Post by Rubens
That seems very odd, almost like transactions are still in the database. Do
you get a lot of records returned when running the following?
exec distribution.dbo.sp_browsemsreplcmds
Rubens
Post by Stuart
I've been replicating for over a year and this file has grown to twice the
size of my biggest db.
I've tried shrinking, the ldf will shrink but the mdf doesn't change.
Any suggestions ?
Thanks
Rubens
2008-03-04 20:27:48 UTC
Permalink
That's probably not unusual. However if that returns a ridiculous amount of
transactions (i.e. several million) I would suspect the transactions aren't
being pushed across and something is up with replication. Your distribution
database should grow, but not larger than your largest database.

Rubens
Post by Stuart
Hi Rubens,
Thanks for responding.
Yes, after 20 minutes, it has generated 50k plus records and is still
running...
Post by Rubens
That seems very odd, almost like transactions are still in the database.
Do
you get a lot of records returned when running the following?
exec distribution.dbo.sp_browsemsreplcmds
Rubens
Post by Stuart
I've been replicating for over a year and this file has grown to twice the
size of my biggest db.
I've tried shrinking, the ldf will shrink but the mdf doesn't change.
Any suggestions ?
Thanks
Stuart
2008-03-04 21:29:00 UTC
Permalink
I stopped in after 45 minutes @ 545k, it was still running.

My biggest db is 11g and distribution.mdf is 18g+
Post by Rubens
That's probably not unusual. However if that returns a ridiculous amount of
transactions (i.e. several million) I would suspect the transactions aren't
being pushed across and something is up with replication. Your distribution
database should grow, but not larger than your largest database.
Rubens
Post by Stuart
Hi Rubens,
Thanks for responding.
Yes, after 20 minutes, it has generated 50k plus records and is still
running...
Post by Rubens
That seems very odd, almost like transactions are still in the database.
Do
you get a lot of records returned when running the following?
exec distribution.dbo.sp_browsemsreplcmds
Rubens
Post by Stuart
I've been replicating for over a year and this file has grown to twice the
size of my biggest db.
I've tried shrinking, the ldf will shrink but the mdf doesn't change.
Any suggestions ?
Thanks
Paul Ibison
2008-03-04 20:42:46 UTC
Permalink
If you want a quick check, run Select count(*) from MSrepl_commands
(nolock).
Rgds,
Paul Ibison
(www.replicationanswers.com)
Stuart
2008-03-04 21:31:03 UTC
Permalink
It returned a count of 29,983,135.
Post by Paul Ibison
If you want a quick check, run Select count(*) from MSrepl_commands
(nolock).
Rgds,
Paul Ibison
(www.replicationanswers.com)
Paul Ibison
2008-03-04 21:49:56 UTC
Permalink
OK - it depends on your workload but I'd say that is big for most people's
money. As mentioned in my other post please check that the cleanup agent is
running and check the retention period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Paul Ibison
2008-03-04 20:40:15 UTC
Permalink
Have a look at sp_spaceused in the database to get an idea if the shrinking
could work.
After that, check that the cleanup agent is running and check the retention
period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Stuart
2008-03-04 21:56:00 UTC
Permalink
Thanks for responding.

I think this may be it.

If I'm looking in the right place, I right clicked on the db name under
Local Publications, and the Subscription experation is set to Never Expire
under the Publication Properties.

Am I in the correct place ?
Post by Paul Ibison
Have a look at sp_spaceused in the database to get an idea if the shrinking
could work.
After that, check that the cleanup agent is running and check the retention
period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Kevin3NF
2008-03-05 13:36:20 UTC
Permalink
Look in the jobs or replication monitor for the Distribution Cleanup
job...is it running and completing successfully?
--
Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for responding.
I think this may be it.
If I'm looking in the right place, I right clicked on the db name under
Local Publications, and the Subscription experation is set to Never Expire
under the Publication Properties.
Am I in the correct place ?
Post by Paul Ibison
Have a look at sp_spaceused in the database to get an idea if the shrinking
could work.
After that, check that the cleanup agent is running and check the retention
period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Stuart
2008-03-05 15:33:03 UTC
Permalink
Thanks for joining.

There is a Subscription cleanup job, but not a Distribution under SQL Server
Agent/Jobs.
Post by Kevin3NF
Look in the jobs or replication monitor for the Distribution Cleanup
job...is it running and completing successfully?
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for responding.
I think this may be it.
If I'm looking in the right place, I right clicked on the db name under
Local Publications, and the Subscription experation is set to Never Expire
under the Publication Properties.
Am I in the correct place ?
Post by Paul Ibison
Have a look at sp_spaceused in the database to get an idea if the shrinking
could work.
After that, check that the cleanup agent is running and check the retention
period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Kevin3NF
2008-03-05 17:15:47 UTC
Permalink
So you don't have this job on your Publisher or Distributor:

Distribution clean up: distribution

If you are SQL 2000, this may very well be the issue. No idea what it looks
like in 2005, since I don't have a 2005 replication testbed set up...
--
Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for joining.
There is a Subscription cleanup job, but not a Distribution under SQL Server
Agent/Jobs.
Post by Kevin3NF
Look in the jobs or replication monitor for the Distribution Cleanup
job...is it running and completing successfully?
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for responding.
I think this may be it.
If I'm looking in the right place, I right clicked on the db name under
Local Publications, and the Subscription experation is set to Never Expire
under the Publication Properties.
Am I in the correct place ?
Post by Paul Ibison
Have a look at sp_spaceused in the database to get an idea if the shrinking
could work.
After that, check that the cleanup agent is running and check the retention
period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Stuart
2008-03-05 20:52:01 UTC
Permalink
No, I do not. I'm using 2005.

I did some research on this aspect, and apparently this is a known bug:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2293924&SiteId=1

... I'm doing more research.
Post by Kevin3NF
Distribution clean up: distribution
If you are SQL 2000, this may very well be the issue. No idea what it looks
like in 2005, since I don't have a 2005 replication testbed set up...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for joining.
There is a Subscription cleanup job, but not a Distribution under SQL Server
Agent/Jobs.
Post by Kevin3NF
Look in the jobs or replication monitor for the Distribution Cleanup
job...is it running and completing successfully?
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for responding.
I think this may be it.
If I'm looking in the right place, I right clicked on the db name under
Local Publications, and the Subscription experation is set to Never Expire
under the Publication Properties.
Am I in the correct place ?
Post by Paul Ibison
Have a look at sp_spaceused in the database to get an idea if the shrinking
could work.
After that, check that the cleanup agent is running and check the retention
period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Stuart
2008-03-06 15:18:05 UTC
Permalink
Solved !

exec dbo.sp_MSdistribution_cleanup

Took 2 hours, went from 18g to 47meg.

I will add this to my Jobs queue.

Thanks for your help, you got me pointed in the right direction. Wonder why
that job wasn't added when replication was setup? I guess it's possible the
job got deleted.

Thanks all !
Post by Kevin3NF
Distribution clean up: distribution
If you are SQL 2000, this may very well be the issue. No idea what it looks
like in 2005, since I don't have a 2005 replication testbed set up...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for joining.
There is a Subscription cleanup job, but not a Distribution under SQL Server
Agent/Jobs.
Post by Kevin3NF
Look in the jobs or replication monitor for the Distribution Cleanup
job...is it running and completing successfully?
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for responding.
I think this may be it.
If I'm looking in the right place, I right clicked on the db name under
Local Publications, and the Subscription experation is set to Never Expire
under the Publication Properties.
Am I in the correct place ?
Post by Paul Ibison
Have a look at sp_spaceused in the database to get an idea if the shrinking
could work.
After that, check that the cleanup agent is running and check the retention
period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Kevin3NF
2008-03-06 15:50:14 UTC
Permalink
Happy to assist. Jobs that people don't recognize get deleted all the time.
Make sure you run it regularly enough to keep it under control. Default is
every 10 minutes
--
Kevin3NF
SQL Server dude

You want fries with that?
http://kevin3nf.blogspot.com/

I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Solved !
exec dbo.sp_MSdistribution_cleanup
Took 2 hours, went from 18g to 47meg.
I will add this to my Jobs queue.
Thanks for your help, you got me pointed in the right direction. Wonder why
that job wasn't added when replication was setup? I guess it's possible the
job got deleted.
Thanks all !
Post by Kevin3NF
Distribution clean up: distribution
If you are SQL 2000, this may very well be the issue. No idea what it looks
like in 2005, since I don't have a 2005 replication testbed set up...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for joining.
There is a Subscription cleanup job, but not a Distribution under SQL Server
Agent/Jobs.
Post by Kevin3NF
Look in the jobs or replication monitor for the Distribution Cleanup
job...is it running and completing successfully?
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Post by Stuart
Thanks for responding.
I think this may be it.
If I'm looking in the right place, I right clicked on the db name under
Local Publications, and the Subscription experation is set to Never Expire
under the Publication Properties.
Am I in the correct place ?
Post by Paul Ibison
Have a look at sp_spaceused in the database to get an idea if the shrinking
could work.
After that, check that the cleanup agent is running and check the retention
period.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Paul Ibison
2008-03-05 18:24:02 UTC
Permalink
Hi Stuart, this is different. have a look at sp_helpdistributiondb to see
the retention period, or in the distributor properties.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Stuart
2008-03-05 20:49:03 UTC
Permalink
Hi Paul,

max_distretention = 72
history_retention = 48
Post by Paul Ibison
Hi Stuart, this is different. have a look at sp_helpdistributiondb to see
the retention period, or in the distributor properties.
Rgds,
Paul Ibison
(www.replicationanswers.com)
Paul Ibison
2008-03-10 20:52:49 UTC
Permalink
OK - nothing odd there. Assuming the cleanup agent is scheduled to run
regularly this should keep things undewr control now that you have removed
the redundant transactions.
Rgds,
Paul Ibison
(www.replicationanswers.com)

Loading...