Distribution dB
-
Hi guys,
I am new to replication and have been out here lately posting alot of questions. Thanks for all your help. I have another one:
Does the distribution dB get "cleaned" out with the sp_repldone sproc? My distribution database is really huge and I'm haveing a hard time managing it. Ordinarily I would shrink files and perhaps a variety of other things to manage my space. But I am unsure as to if this data is needed and the appropriate "way" to get the dB back down to manageable size. Please help.
Christine
Aurora
-
It should be pretty much self maintaining. It will see quite a bit of activity, both in the mdf and in the log files. I typically let mine grow until it stabilizes, only shrink if its ballooned up due to a very large transaction. Transactions are cleaned up once they are posted, if you have a subscriber offline they will sit there until the subscription expires.
Andy
-
Yes that's what I thought, but...Well it appears that after very large transactions posted the dB, it has not "cleaned" itself. I would like to know if there is a (safe)way I can shrink the dB. Can I forcibly shrink .mdf files? Is it recommeded? Should I run sp_reattach? We are really pressed for disk space and need to do something quickly.
BTW, our Network Admin has ordered a server class machine to replace (what we have painfully learned) boxes that simply were subpar.
Thanks for your help.
Aurora
-
Shrink db or shrinkfile should do it. If the data is still there (not cleaned out!) then shrinking isnt going to help. If more likely the data has been posted correctly, shrinking should free up some space. Check that your distribution clean up job is running also.
Andy
-
Is the clean up job a system run process or custom? (I told you I was new to replication)
In the meantime I will attempt shrinking the files. I think I already tried this and it did not work.Chris
Aurora
-
Job should get created when you set up replication, runs this:
EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72
Andy
-
Did that although it suggested that it cleaned up a few transaction (not a significant # that would make the dB "weigh" in at a whopping 9GB. I also ran DBCC SHRINKFILE and it let me know that the "EstimatedPages" and "UsedPages" are even at 30136. Does this mean there is no room to shrink although my properties are telling me that there is 96% space available? Can I or should I run sp_reattach and can I run this while the distribution dB is being written to? Your thoughts?
Edited by - Christine Austin on 08/20/2002 11:11:00 AM
Aurora
-
Cant detach while replication is live. Maybe start by updating statistics and space usage, then seeing which tables are using all the space?
Andy
-
Yes, have done that and it appears that the MStransactions and MScommands tables that are incredibly huge. Does that shed any light? We feel like the data in the tables should have been "purged" but it wasn't and I can't explain why or shrink the files.
Signed "In Replication Hell"
Edited by - Christine Austin on 08/20/2002 12:21:07 PM
Aurora
-
Yeah, but isnt it great to have a problem to figure out?
Do you have any subscribers that aren't connecting? Distribution agent is definitely running? Depending on the size of the data, how about just re-initing the publication, sending over a new snapshot. That should get it to clean up.
Its been a while so I'd have to look, but you "could" clean up the stuff manually if you're sure its not needed. Or combine the two, truncate the table and then snapshot.
Tread lightly with these suggestions.
Andy
-
Thank you so much Andy for your time. At this point I do not have anything to lose so I will probably give the manual thing a go. I have the data, the distribution dB just all of a sudden (I think in an effort to play catch up) got really big (for us). I think they tried to sync up replication and this happened. I will keep you posted.
Thanks again,
Chris
Aurora
-
Andy, I am right there with you. I think this actually will solve the problem. I am not terribly concerned about lost data. I think we are okay here. Its just odd that the distribution dB did what it did, its as if it didn't complete something. I will however, drop the publications, re init, WITHOUT sync. (again I'm not to concerned about data and I know the schema has not changed). I'll let you know how we faired.
Thanks again for all your help!
Christine
Aurora
In the meantime I will attempt shrinking the files. I think I already tried this and it did not work.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply