For those of you not familiar with the Query Store settings, please check out my post which lists each one, the defaults, and what I would recommend for values and why. When discussing MAX_STORAGE_SIZE_MB, I mention monitoring via sys.database_query_store_options or Extended Events. As much as I love Extended Events, there isn’t an event that fires based on a threshold exceeded. The event related to size is query_store_disk_size_over_limit, and it fires when the space used exceeds the value for MAX_STORAGE_SIZE_MB, which is too late. I want to take action before the maximum storage size is hit.
Therefore, the best option I’ve found is to create an Agent job which runs on a regular basis (maybe every four or six hours initially) that checks current_storage_size_mb in sys.database_query_store_options and calculates the space used by Query Store as a percentage of the total allocated, and then if that exceeds the threshold you set, send an email. The code that you can put into an Agent job is below. Please note you want to make sure the job runs in the context of the user database with Query Store enabled (as sys.database_query_store_options is a database view), and configure the threshold to a value that makes sense to your MAX_STORAGE_SIZE_MB. In my experience, 80% has been a good starting point, but feel free to adjust as you see fit!
Click through for the script.