How to Track Autogrowth of Any Database? – Interview Question of the Week #205

Question: How to Track Autogrowth of Any Database?

Answer: Just the other day during the Comprehensive Database Performance Health Check, we identified that the SQL Server Performance problem of the user was because of the constant Autogrowth of their main database which ran lots of e-commerce orders.

We put our database under observation and figured out a high amount of activities during a certain period of the time and along with that also noticed that we have an ETL job running at the particular time which overlapped some busier time of the database.

We finally decided to put a trace flag to identified when and who did the autogrowth for the database. Once we identified the issue, we increased the value of the auto growth for database files and eventually resolve the problem. Here is the configuration setting where we can increase the autogrowth settings for database files.

If you want to identify when and who did auto growth for the database, I strongly recommend you to read this blog post: SQL SERVER – Script – When/Who did Auto Grow for the Database?

I have also recorded Two Free Videos about SQL Server Performance Tuning and Optimization which you can watch to learn similar other settings related to SQL Server Performance optimizations.

Reference: Pinal Dave (https://blog.sqlauthority.com)