February 7, 2020 at 2:07 pm
I have a 450GB database of which about 25% is index space, so 350GB of data, say. I have a 500GB disk for a replication snapshot, but after about 20 mins the snapshot agent fails and tells me that there is not enough space on the disk. Strangely the disk is only about 10% full at this stage.
Question 1: Is there any way of estimating how much space is needed for a replication snapshot for a database of a given size?
Question 2: Is it not rather strange behaviour for the snapshot agent to decide after 20 mins that it doesn't have enough space when the disk is still only 10% used?
February 7, 2020 at 2:51 pm
the replication snapshot should not be bigger than the database itself; at least it never happened to me.
the snapshot unc path(...\MSSQL\ReplData) may be on a different drive(say C:\) from your data drive(say D:\). You have space on D:, it could still be possible no space on C:\.
February 7, 2020 at 4:03 pm
The snapshot is explicitly placed on my 500GB disk. You can see it populating the folder on that drive. It's just that it stops when the drive is about 10% full. Is the snapshot agent constantly estimating the amount of space it will need during the snapshot process perhaps? And stopping when it estimates that it won't have enough space.
February 7, 2020 at 8:06 pm
This a VM or a physical piece of tin?
is it a virtual disk or a rdm?
is it thick or thin provisioned?
if it’s been thin provisioned and the the lun is full then windows looks like it has space when actually it hasn’t.
Is this snapshot to do the initial sync? As a work around could you initialise from backup instead?
February 7, 2020 at 8:12 pm
Also what snapshot options have you specified?
native character sets?
compressed snapshots?
any of the database compressed at row or page level?
February 8, 2020 at 10:00 am
It's an AWS vm and I'm pretty sure 500GB means 500GB. There's no compression in the database and I didn't try compressed snapshot. I can't initialise from backup as the subscriber is at a lower version of SQL. Ultimately I can get more space.
I'm just trying to understand the behaviour. Why would the snapshot agent run for 20 mins before deciding there wasn't enough space? Why would it decide that when the disk was only 10% used? The only rationale I can think of is that it is constantly re-evaluating and re-estimating. Unless there is some other factor tat I haven't thought of.
February 8, 2020 at 2:46 pm
Without seeing what’s going on it’s hard to say for sure.
my guess is something none native with the data types is happening so it’s extrapolating the sizes some what. Int is 4 bytes but 10000 written as a string is 5, so my guess would be something like that.
how many revisions below is the subscriber from the distributor? is it more than 2? Replication doesn’t like publishers and subscribers more than two versions above/below the distributor version.
Are you replicating the whole database?
could you perform a bcp out of all the Articles one by one to the drive? That will tell you what’s going one as you will find the table which is blowing up the space.
could you also post a screenshot of the publications properties for the subscription screen?
February 12, 2020 at 11:47 am
Thanks for your interest Anthony. In the end it was easier to get more space, which I've done. The successfully completed snapshot was 375GB, so much as expected and well within the 500GB that was originally available. So if SQL was estimating how much space would be needed it got that estimate wrong, and quite badly. If anyone has further insights to offer that would be of interest but for practical purposes problem solved.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply