I have a SQL Server 2005 database, configured for merge replication on a 4gb database to a SQL Server 2000 server. All was working fine and then yesterday everything stopped because the distribution database has reached 67Gb (Yes gigabytes) filling up the 80Gb C drive.
Now, Ive tried deleteing the subscription but it wont let me as there isnt enough disk space. Is there a way that I can manually truncate data in the distribution. I know it will prob break replication but I want to rebuild the entire replication set up now and just get it working as soon as possible.
Can someone please help! Im nearly out of hair to pull out!
Shane
I'm confused why the distribution database is so large since merge replication barely touches the distribution database with exception to the history tables. Are you sure it's the distribution database and not the published database? And which part of the database is taking up space - is it the data file or the log file?
If it's the distribution database, and assuming it's the data file, can you tell me what tables are taking up all the space, SSMS should have some reports you can run that will show you this. If it's the published database, that would make sense since there are a bunch of metadata tables. You might be able to change the retention to something smaller and then manually run metadata cleanup jobs to remove metadata. However I would change the retention rate little by little, otherwise the larger it is, the more log space at once it might consume and you'll be back to square one, running out of disk space.
If it's the log file, your best bet would be backup the log to some network share, then shrink the file.|||Thanks for your reply Greg.Its definately the distribution database. The distribution.LDF file is now 59GB and the distribution.mdf file is 12Gb, I dont understand what is going on!
There is only one table in the databas ewith many records, MSMergeHistory, with about 6 million records, seems a lot but then again maybe not. The database being replicated is our main intranet database used by 140 people daily.
Anyways, what can you suggest I do?
|||I should add that the distribution database recovery model is set to simple, so Im a little confused as to how this could happen. ie the log files growing so much...
|||Is it possible for me to truncate that MSMergeHistory table and then shrink perhaps? Or move the distribution to another 1Tb disk NAS I have attached. At the moment I cant to anything with the machine as this one DB is hogging all the space so Im lost in trying to find a way to find space. Every time I delete something the database expands immediately, Ive now got a bare C drive with only Windows and these databases. So frustrating. Any ideas or thoughts are greatly appreciated, Im desperate to get this working. Im now to the point of considering uninstalling and reinstalling SQL Server which is ridiculous...
|||
I agree it's ridiculous, however i don't believe 6 million rows in the history table would take up so many GB. Can you run those reports in Object Explorer which can tell you what tables take up what space to verify it's just the history table?
Let's assume it is the history table, then you can change the history retention so it's more aggressive to clean it up.
As for why your distribution log isn't truncating, I can't answer that, you'll have to do more investigating. There are articles in msdn that detailing what causes this to happen, log could be continuously growing, corruption ,etc. As I mentioned earlier, do a log backup to your NAS to free of the log space, then shrink the file to free of disk space.
|||The problem I have is that I cant run *anything* because I get Out of disk space errors. Ive tried stopping sql server and manually moving the mdf and LDF files to another disk but thats not helped. I will try moving them back now, doing a recovery but change the location to recover to. Maybe that way I can start running queries to reduce stuff.|||Then attach your network storage and add another data file or log file to the distribution database.|||OK, Ive got the database currently restoring to a much bigger disk.
The results of sp_helpfile and spaceused are:
name fileid filename filegroup size maxsize growth usage
-- - --
distribution 1 E:\SQLRoot\Data\distribution.MDF PRIMARY 12012160 KB Unlimited 1024 KB data only
distribution_log 2 E:\SQLRoot\Data\distribution.LDF NULL 59653312 KB 2147483648 KB 10% log only
database_name database_size unallocated space
--
distribution 69985.81 MB 20.97 MB
reserved data index_size unused
11990688 KB 11299568 KB 687784 KB 3336 KB
|||i could have sworn I said to back up the distribution log to the NAS, then shrink it. DId that not work?|||
Are you using merge agent in a continuous mode?
can you move the distribution database to the SAN: checkout: http://msdn2.microsoft.com/en-us/library/ms345408.aspx for more info.
|||Backing up the log did nothing to the log on size of the log, once it was backed up I tried to shrink it but still no joy, because I was out of disk space it couldnt execute anything.I have just now resolved the issue, by backing up the entire Distribution to the SAN, then doing a restore but changing the restoe location to the NAS drive (that had 1 terabyte free), once that was done I had free space on the C, room for the Distribution to expand while I ra\n the scripts to drop all replication.
The scripts I ran in order were then
1, Manually remove the Subscription,
2 Manually remove the Publication
3 Shrink the Distribution database
4 Run Exec sp_removedbreplication, then add it again as a replicated database to repair all the sysmergearticle tables etc
5 Ran my scripts then for recreating and initializing the Pub/Sub and its currently bulk copying.
As yet I still dont know why this happened but it seems to have been corrupted so rebuilding looks to have fixed although will know for sure ina day or 2 after the database has been used in anger.
Anyways, thanks for all the help and advice. This is a great resource and will be sure to return the favour one day :-)
No comments:
Post a Comment