Home Dashboard Directory Help
Search

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


Status: 

Active


3
0
Sign in
to vote
Type: Bug
ID: 805116
Opened: 10/11/2013 4:27:26 AM
Access Restriction: Public
1
Workaround(s)
view
1
User(s) can reproduce this bug

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:
Details
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
Sign in to post a workaround.
Posted by AndyB1978 on 10/11/2013 at 4:28 AM
If you backup with compression, the backup size becomes acceptable.
If you enable TDE on the database, the backup size becomes acceptable.