Lesson Learned #292: The database 'UserDB' has reached its size quota. Partition or delete data

We got a new customer service request where they got the following error message: The database 'UserDB' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

We found that database reached it's size limit (1024GB) with Space Used 1023.253 GB. After analyzing these two links Lesson Learned #150: Calculating the space used by table and per column - Microsoft Community Hub and Lesson Learned #227: How much data is using my database per table - Microsoft Community Hub and we didn't find that might be related with PVS, so, the immediate option is to increase the SLO to give you more available space

Alternatively other options might be:

• Reduce the space used using one of the suggested mitigations below. This will make more room within your current space allocation
  
• Look at the batch size/ order of your operations within your pipeline so that your pipelines need less room to work with, eg if you are copying data between tables then deleting it from the source, a smaller batch would need less room.
  

Some longer term mitigations that might be worth considering:

• Analyze if column data types are the right ones for the data they will store.
  
• Test compressing tables and/or indexes.
  The data compression feature help's to reduce the size of the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application.
   
   
  
• Depending on the workload and service tier, test Columnstore indexes
  
• If possible, export and remove data that is not needed or move to another database, with lower service tier, if the access pattern is low.
  

Continue reading...