Sudden database size growth

Question

Until yesterday morning database size was approximately 30 gb but today morning it has grown to 35.6 gb with 5.5 gb showing in 'available space' in database properties; work load on db was regular yesterday. There isn't any workload reason for db to grow by this scale. Average workload leads to growth of 1 gb every 5 to 5.5 months; however it has grown by 5.6 gb in a day. Please suggest for probable causes that it can be checked.

Answer 1

My biggest table is 3.5 gb. Guide please.

Sounds like re-indexing operation to me.

Yeah, I notice that you say:

Index maintenance: No manual index maintenance was done that day;

Having answered questions for many years in this type of forums, I know that it is not uncommon that people who ask questions are often mistaken in their assumptions.

if database engine had done auto index maintenance or auto updated statistics.

There is no such thing as auto-index maintenance. Autostats can happen, and you can use the stats_date to see when a statistics most recently was updated. Then again, statistics update does not cause database growth.

There are several other possibilities. Maybe someone bulk-loaded a big file into a table in the database. Maybe it was in error, and the user dropped the table.

But the more important question is: why do you bother? Yes, in per cent it is a considerable growth, but if you bother about growth of 6 GB you are focusing in the wrong things.

By the way, it would have been possible to get some idea of what happened, as autogrowth is captured by the default trace. I say "would have been possible", because the way the default trace is configured, events are aged out within a day or so. (It is size-based, so how long data stays, depends on what is going on at the instance.) But again, investigating a growth of 6GB is something that should be low on your priority list for your daily DBA work.

Answer 2

How big is your biggest table? If that table is 5GB, you need 5GB to rebuild the clustered index of that table.

Answer 3

Hi @DM,

There could be several reasons why your database size has suddenly increased.

Data Growth: It's possible that your application has generated more data than usual, such as new records or increased file attachments. You can view your transaction logs.

Index Maintenance: Database indexes are crucial for performance, but they also require space. If you have recently performed index maintenance, it's possible that the growth is due to the index rebuild operation. When you rebuild an index in SQL Server, it creates a new copy of the index and then drops the old one. During this process, SQL Server creates a temporary copy of the index, which requires additional space.

Autogrowth Settings: Check the database autogrowth settings. If the database is set to grow automatically, it's possible that the sudden growth is due to autogrowth events triggered by data growth.

TempDB Usage: TempDB is used for temporary storage during query processing. If your queries require a lot of temporary storage, it's possible that the sudden growth is due to TempDB usage. You can restart your SQL Server service to clear it.

Log File Growth: Check the size of your database transaction log file. If it has grown unexpectedly, it's possible that your database is in full recovery mode, and the log file has not been backed up.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".