How to fix sudden increase in DB size

Question

Hello Team, We are seeing a sudden increase in size consumed in our database. We were at 60% usage from months and from FEB 19-20,2024 we see jump to 90% usage. Our data plan is limited to 750 GB. We have auto tuning enabled and we have no processes that was newly introduced during this period. Our largest table (existing table for months ) has about 198181709 records with the basic data types used .Other tables have a average of 862868 records put together with basic data types How can we address this issue further

Answer 1

Hi Nitya V,

Thanks for reaching out to Microsoft Q&A.

What does your data Growth Analysis say about the sudden increase of your db size? most probably this will give you the required info. Table Growth: Examine the growth patterns of individual tables. Identify any specific tables that have experienced significant growth during this period.

https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-sql-database-azure-monitor?view=azuresql

Try to find the index size which will aid in helping find the exact cause of particular object which is causing size increase. Index Size: Check if index sizes have increased. Bloated indexes can contribute to database size. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql?view=azuresqldb-current

Setting up strategy for bigger tables:

It is always recommended to maintain archiving or partitioning strategy with so many records in a table. https://reintech.io/blog/managing-data-growth-archiving-azure-sql-database

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.

Answer 2

@Nitya V Thank you for reaching out.

My understanding is that you are seeing a sudden increase in database size.

Please do Identify long-running queries or inefficient execution plans. You can use the query store to do that. Please check if you have that enable.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16

Although you have auto-tuning enabled, check if there are any recommendations related to indexes, statistics, or query performance. Apply relevant recommendations to optimize resource usage.

Please do also check if there was not a bulk-loaded big file into a table in the database.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/what-caused-the-sudden-dtu-cpu-spikes-to-the-limit/ba-p/811218

Regards,

Oury

Answer 3

This apparently was caused by the data sync agent .When we dropped the sync group we were able to reclaim 30%.Wondering what does the sync do in background

Answer 4

Nitya V: Glad to know that you were able to figure this out and thanks for sharing the root cause. By the way, Microsoft Q&A community has a policy that "The question author cannot accept their own answer, they can only accept answers by others.". Hence request you to accept this as an answer so we can close this thread. I am summarizing the issue and root cause you have found for it.

Issue Summary:

Sudden increase in size consumed in the azure SQL database.

Troubleshooting & Fix:

The bloat was apparently caused by the data sync agent in the background. After dropping the sync group able to reclaim about 30% of free space in the database.

What is SQL Data sync?

SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple databases, both on-premises and in the cloud.

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql