Change File Growth

GarryB

After a discussion and demo of SQL Clone to my team yesterday the question came up that made me think.

If the same records are updated over and over will all those changes be tracked in the difference file and bloat the files for the clone on disk.

So our main purpose of the tool is to help QA test data. If they run the same test 1000 times that update the same set of customer accounts in the clone database will each change be added to the file?

GrahamH

The difference file stores the current state of each block on disk (if different from the base image). So in general, updating the same value should change the same disk block and overwrite the previous change. So the first time you do the test you'll obviously expand the difference file, but for the next 999 tests I'd expect it to stay about the same size.

That said, I'd encourage you to try it out for your use case and see if it works in a way that's useful to you! If not, we'd love to hear the details of why in case we can do something to improve that.

JonathanD

So I noticed that my clone files went from about 50mb, when first created, to about 3.30gb without having even connected to that clone or read anything from it. Other than writes, what would cause the growth?

Update:
I've seen the clone file get larger without having actually touched/updated/read the data. Clone is now at almost 6gb from 3.3gb when I posted. What causes this growth?

Mark R

That's odd. Could there be any automated process (eg SQL Agent jobs) doing maintenance tasks that result in writes?

JonathanD

Mark R wrote: »

That's odd. Could there be any automated process (eg SQL Agent jobs) doing maintenance tasks that result in writes?

On the clone that I've loaded on the Server perhaps, but for my local no. The only thing that I could think of would be auto-update stats; however that wouldnt account for the growth over the weekend when the system was idle.

davidwilliams

Hi,

I've also seen this strange behaviour, stars out small (50 - 70 MB) and increases to around a few GB without any direct access, in most cases the growth increase stops around that mark but I've also seen it carry on over 100 GB and fill the drive.

This issue is being seen on SQL 2017 using SQL Clone 2.5.2, and I never saw these issues during our trial of the product on earlier 2.x versions of the software.

I've tried disabling auto stats and auto shrink, and even the ghost cleanup process, and there are no agent jobs running on the server when these issue occur, but I can see read/write activity against the data files for the clone database affected.

Also rather strange is that we clone three copies of the image with a slightly different database name and generally only see the issues on one or two of the database, but never all of them.

Cheers,

David.

RichardL

Hi @davidwilliams

Thanks for your post. 

Can we just check if you are using row level compression? 
Are there any operations being carried out on the clones? Does SQL Server have any pages pending? If so, when you create images and clones if you use the wait SQL Clone operator in Powershell then it should wait for each operation to complete before creating your Clones. This might be why you are seeing this on one but not all Clones. 

I hope this helps. 

Kind regards

Richard Lynch. 

davidwilliams

Hi Richard,

No row level compression on our databases, but i think I've traced it down to the Ghost Cleanup process, I previously changed activated trace flag (661) on a global level via DBCC TRACEON command, but this didn't seem to have an effect on this issue.

In re-creating this issue today, I could see that the clone growth was linked to the ghost clean-up process as that was the only process running against the affected databases, so after adding the trace flag to the SQL service startup options and restarting SQL, the issue appears to have stopped and I haven't been able to re-create it as I could before this change.

The main difference between seeing this issue now and not previously when trialling the software, is that were are using the modification script option in the image process to remove quite a bit of logging/temp data and related objects from the image to reduce disk usage, but it looks like the after affect of this is the ghost cleanup process is kicking off on the clones of the database which is causing the large number of differences to be generated.

I'll keep and eye on the clones following this change, to ensure it's fixed the issue for good.

Cheers,

David.

bobking

We are plagued by a very similar issue with ghost page cleanup after cloning, and had to set the same trace flag on our dev SQL box. I'm nervous, though, because we don't do any modification of our image as David does. So how are we ending up with ghost pages left over from our production backups (we image from a .bkp file)? Ghost page cleanup appears to be running fine in production, but something fishy is going on. We've been using Clone since before Christmas and this seems to have started only recently (last six weeks or so?). We can't pin it to a Clone release, but I'm wondering if an SQL Server patch changed something? We're on SQL 2014 CU 11 for both prod and dev.

RichardL

Hi @bobking

Thanks for your post. 

If you don't receive any help quickly from the community and have a support contract then please send us a ticket so that a product specialist can investigate this for you. 

Kind regards

Richard Lynch. 

MBarlow85

Was a cause for this behaviour discovered? We have a similar scenario and whether the clone is using an image created from BAK or directly from DB is irrelevant. Clone is created at 44MB and within a couple of hours can reach 6GB without any activity (6GB Seems to be ceiling). We tried disabling SQL Agent and creating a clone and it still grew to 3.5GB (ruling out SQL Agent jobs). Any insight gained from this issue would be greatly appreciated.

bobking

We never really got a resolution on this, and think we just have the trace flag set permanently on our dev box. 

MBarlow85

Hi All, The issue for me was because we did not realise Query Store was enabled on the database. The activity generated within caused the growth. When I disabled query store on the clone after creating it, the clone remained the same size (44MB) thereafter. May help someone else!