March 4, 2008 at 10:44 am
hello, well my problem is that the distribution.mdf fille is growing really fast, right now has 60gb, i been trying to reduce de size, but with out success, any one who can help me? 🙂
March 4, 2008 at 10:56 am
How r u trying to reduce the size? If the database is in full recovery mode you cannot reduce the size without taking a log backup or full backup.Once a backup is taken run dbcc shrinkfile. If you don't want to take backup run,
backup command with truncateonly option. If it is in simple recovery mode , you should be able to reduce the size by using dbcc shrinkfile.
March 6, 2008 at 10:28 am
If it's the distribution database, then you have subscribers that have not gotten their subscriptions, so the data cannot be removed. Be sure all subscribers configured have gotten their data.
March 6, 2008 at 10:40 am
pmohan (3/4/2008)
How r u trying to reduce the size? If the database is in full recovery mode you cannot reduce the size without taking a log backup or full backup.Once a backup is taken run dbcc shrinkfile. If you don't want to take backup run,backup command with truncateonly option. If it is in simple recovery mode , you should be able to reduce the size by using dbcc shrinkfile.
Taking a full backup has no effect whatsoever on the transaction log - only a log backup allows the active portion of the log to be truncated.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 8, 2008 at 9:51 pm
i'd try to reduce de size by runing the agent history clean up job but the log file grows until fills the disk and then gives me an error for no disk space, also i'd run the distribution clean up job, but it doesn't work, the distribution.mdf keps growing, rigth now has 65gb, please help me :hehe:
March 8, 2008 at 10:41 pm
Check your distribution clean up job. It might be failing or not running. I had the same problem. My distribution cleanup job was not running at all.
March 9, 2008 at 11:19 am
Vania Villarreal (3/8/2008)
i'd try to reduce de size by runing the agent history clean up job but the log file grows until fills the disk and then gives me an error for no disk space, also i'd run the distribution clean up job, but it doesn't work, the distribution.mdf keps growing, rigth now has 65gb, please help me :hehe:
The SOP for this is to drop all replication, wait for quiescence, purge everything that is left, then rebuild your replication environment one step at a time.
Now, while there are many good reasons why you may not want to do this, no matter what else you do, you should always be prepared to do this, and make sure that you always keep yourself in the best position to do this. Because 1) you may not be able to get anything else to work and 2) if you try something else and if goes bad, this is your fallback plan, so make sure that you can do it at all time.
That said, there are several things that you can try:
1) Make sure that all of your replication clients are running and receiving their replication info in a reasonable time. If you cannot accomplish this, you may be forced to use the fallback plan. Make sure the the minimum retention time on all of your replication queues is small. A couple of hours is fine.
2) Make sure that your distribution DB is in Simple recovery mode. Note that this is potentially *dangerous*, so make sure that your fallback plan can still work.
3) Get more log space for you distribution DB by adding logs on other disks.
4) Change the parameters of how your clean up job runs so that it purges *much* smaller amounts at a time (sorry I forget how to do this, but it can be done).
5) one other thing to consider: you could try to stop feeding the beast by turning off the Logreader agent(s). Note, however, that this will cause your publisher DBs' Log files to keep growing, even after a Log backup (the replication info has to be held *somewhere*).
After this, you just have to be patient and wait it out (I once worked on a problem like this that took 2 months to complete clean-up and rebuild everything). If you cannot get this to work, then I would recommend going to the fallback plan regardless.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 10:06 am
hello every one, thanks for your help, i'd finally solved the problem in a very simple way, i'd chek up all the system tables in the distribution db, and then cheked the size of heach table, and notice that the Msmerge_history table has 60gb of size, so i'd made an scrip for create the table, later i'd delete the table and creat it again. :D, every thing it's working fine, now i have to see way that table grows sow much :doze:
and sorry for my speelling :blush:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply