Database file usage alerts with In-Memory/Filestream Filegroups

Luke L

We implemented a new database using data and schema persisted in-memory objects and recently began seeing Databsae File usage alerts noting that autogrowth settings are turned off and that we should increase the datafile size to ~121 GB. 

The filestream Filegroup is ~6 GB in size, and when we review the data usage we never see the delta files using more than a few MBs of space.

BTW, this is SQL 2014 SP2 CU11 Enterprise Ed..  This is a small database that holds session data similar to ASP.Net sessions... it is in Simple recovery.  It is rather volatile but not very large.

Is this a valid alert?  it doesn't seem like it when we review the database?  Does SQL Monitor not handle filestream filegroups correctly?

Thanks,
-Luke.

Russell D

We're looking into it. At this point though, no Filestream filegroups aren't supported, best I can suggest to avoid getting the alert is to group any servers using them and disable the alert on that group.

ncbaras

Russell,
For what its worth, I have a SQL 2012 server using filestream for one of the databases (set to unlimited growth). It has not been an issue until this week when I upgraded to SQL Monitor 8.0.2.13400. Now it throws a "Data File Usage" warning thinking that logical file it is at capacity.  I can work around it, but it would be great if you could respond to this ticket once resolution (or even just a planned direction for this type of behavior) is identified.
Thanks,
Nick

Russell D

This will only have become a problem in v8+ because we added the database file usage alert in 8.0.2.

I'll come back to you about any future direction after standup.

Russell D

Ok at this point in time we have no plans to introduce this support I'm afraid. I'd advise posting this over on the SQL Monitor uservoice forum here: https://sqlmonitor.uservoice.com/ to help us gauge how much interest there is in this.

Luke L

ncbaras I'm going to post something over at the user voice site.  I'll update this post with a link shortly.

Thanks,
-Luke.

Luke L

I posted this to uservoice.  https://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/35470828-correct-database-file-usage-alerts-with-in-memory 

Please feel free to upvote it.
Thanks,
-Luke.

AllyParker

Hi Luke, @ncbaras,

Version 8.0.2 was the very first release with the Database File Usage alert in it, it has had various improvements based on customer feedback since it was first released in March, including being able to disable the alert if autogrowth is enabled.

Luke - Could you confirm which version of Monitor you are using down to build number?  There was a regression where there were false positives of this alert for files where autogrowth was enabled (is this the case for your filestream?), which was fixed in 8.0.16. I'd recommend upgrading to the latest version 8.0.17.

I'd be very interested to know if you look at the Analysis screen for the "File Usage" metric for your filestream if that seems correct?

Thanks,

Ally

Luke L

We are currently on 8.0.2.13400.  Yes, auto-growth is enabled for the filestream file/filegroup.

The file usage metric on the analysis graph bounces up and down between 5.4 and 6 GB, yet the tables involved are pretty small...

Per the SSMS Disk Usage report, currently the In-Memory OLTP files have 5.84GB Reserved and only 1.5 MB of Space used.

We generally only update once or twice a year due to time constraints or for a particular feature.  I'll see if I can get it on the list for sometime in the next few weeks...