1

I have a strange issue with a SQLServer 2014 (12.0.4439.1) database.

I created some months ago a data file and table to play with In-Memory basic features and see if could improve some of our processes. When I finished I wanted to clean everything but I didn't know that "Once you create a memory-optimized filegroup, you can only remove it by dropping the database".

Well, I didn't want to delete my database (since it is used in the real life by real people) nor re-create / re-fill it (quite a lot of work).

So everything stayed like that : a normal DB with one empty file for in-memory stuff. And everything is going well.

But, once, log file of this database is going crazy : its size increases and nothing can stop it. And when I try to shrink it, I often (not always) get a "shrink failed for log file due to XTP_CHECKPOINT" which looks like related to in-memory features. And after trying backup / service restart / checkpoint and other esoteric commands, log file can be manipulated and shrunk correctly.

So briefly : I don't understand what happened.

Does someone has experienced that ? and/or know a clean way to avoid/fix this strange behaviour ?

[EDIT] : the issue looks very similar to https://support.microsoft.com/en-us/kb/3090141 but in my case, hotfix is already installed and the workaround suggested (offline / online) doesn't work

Usually, in SQLServer logs there's a HkHostLogCheckpointRecord() message every hour. Log file increasing started to happen when this messages stopped to appear. Is it the cause or the consequence ? that's the question....

Improve this question
7
  • Good example of "testing in production". Fill sorry for you. Have to take a look at that.
    – Slava Murygin
    Commented Aug 4, 2016 at 16:47
  • What exactly is your question?
    – Randolph West
    Commented Aug 4, 2016 at 23:34
  • @Slaya Murygin : it's not really production, that's why I don't feel sorry. BUT I don't want the same thing to happen when I will use this feature in real production !
    – irimias
    Commented Aug 5, 2016 at 7:04
  • @Randolph West : the question is : why log file size is suddenly increasing (up to dozens of Go...) although there is no activity on the in-memory objects ?
    – irimias
    Commented Aug 5, 2016 at 7:04
  • If the question only on the log, then look inside the log file at the transaction, which is holding it and see what it is doing.
    – Slava Murygin
    Commented Aug 5, 2016 at 14:51

1 Answer 1

Reset to default
1

OK, I didn't find a clear explanation, but found how to fix that.

It was because file used to store in-memory objects was full and could not allocate more space. So no more checkpoints, and logs and growing more and more. This is happening even though there is no in-memory objects in the database (and therefore no reason for the data file to grow).

Allowing file to grow more is not working, you need to add another file to your filegroup.

So, knowing that, it's easy to fix.

  1. add another file into the filegroup for in-memory
  2. checkpoint (or, if you can afford it, restart SQLServer service)
  3. shrink log file to a more reasonable size

Conclusion of the story : carefully monitoring data size is even more important with in-memory.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.