0

I am currently working on a SQL Server database server that has multiple drives and databases. The server is running low on disk space due to some of the databases growing unexpectedly. Unfortunately, expanding the drives is not an option at the moment. Additionally, moving the database files is a downtime operation and should be avoided if possible.

I am looking for suggestions on how to efficiently manage the disk space on this server without causing unnecessary downtime. Are there any strategies or tools that can help me monitor and control the growth of these databases? Are there any best practices for managing disk space on a SQL Server database server with limited storage? Any help or advice would be greatly appreciated.

1 Answer 1

2

For monitoring (at least to start monitor your SQL servers), I can recommend DBADash. It's freeware and you can monitor the file sizes and file growth of your databases. However, it has very limited to none alerting options.

For controlling the growth, you can limit the maximum file size in the database properties -> files -> autogrowth/maxsize. However, hitting the maxsize might stop your application to fail.

Your problem might be caused by something not obvious at a first glance, like having very fragmented heaps in your database growing wildly. I can point you to this wonderful article for more details. From my experience, I was able to decrease the size of heavily fragmented (and with incredibly low fillfactor therefore) table from 400GB to 10GB.

You won't do anything wrong, for each database that causes problems, to check the biggest tables - people are often surprised by the findings. You'll right click the DB -> reports ->standard reports -> disk usage by top tables in SSMS.

1
  • Thanks for your valuable input! I'll take a look the recommended article and software. Jan 22, 2023 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.