Issue with In-Memory Databases having HUGELY disproportionate backups. - by AndyB1978

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 805116 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 10/11/2013 4:27:26 AM
Access Restriction Public

Description

Good afternoon,
I raised a question on the TechNet forums regarding strange backup sizes when using InMemory tables.

(http://social.msdn.microsoft.com/Forums/en-US/6ab85c63-08aa-48cc-8468-01d6239208bf/why-on-earth-are-backups-so-large?forum=sql14inmemtech)

While this may be fixed in CTP2, I was asked to raise a bug in Connect and steps to repro:
Sign in to post a comment.
Posted by SQLDBAs on 12/19/2013 at 12:08 PM
Sunil/Kevin
Still this bug exists in this build 12.0.1740.0 . I can reproduce this .
Posted by Microsoft on 10/23/2013 at 10:55 AM
We have resolved the primary issue in CTP2, as noted by Sunil, and have designs in place to make the situation even better by RTM, so I'm closing this bug.
Thank you for your help in reporting the issue.

Kevin Farlee
Posted by Microsoft on 10/23/2013 at 10:55 AM
We have resolved the primary issue in CTP2, as noted by Sunil, and have designs in place to make the situation even better by RTM, so I'm closing this bug.
Thank you for your help in reporting the issue.

Kevin Farlee
Posted by Microsoft on 10/17/2013 at 10:06 AM
Andy,

With CTP2, the full database backup was around 140MB. Note, the data files are sized at 128MB. So that is the minimal size. I also looked at the total in-memory size of the objects in the database. It was around 50MB. The key change between CTP1 and CTP2 is that we don't backup 'free files'.

Having said this, we are looking into reducing the size of backup even more.

Thanks

Sunil
Posted by Microsoft on 10/11/2013 at 2:55 PM
Andy,

As you noted, the size of backup up disproportionately large compared to the size of the database. When you create memory-optimized FG and add one or more memory-optimized tables, SQL Server pre-allocates 8 data/delta file pairs to minimize any waits in creating/initializing files as transaction load is running. At this time, these files get included in the backup even though they don't have any data. Also, there may be files (source of merge operations) that are not yet garbage collected by FileStream. These are also included in the database backup though they are not really needed. We are working on this issue as part of post-CTP2 work. As Kevin indicated, please try it on CTP2 bits and let us know any difference you see.


I have the repro from you and I will try it

thanks
sunil