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.

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

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.

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

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)

, , ,
Previous Post
How to Create an Empty Table and Fool Optimizer to Believe It Contains Data? – Interview Question of the Week #204
Next Post
How to Sort a Varchar Column Storing Integers with Order By? – Interview Question of the Week #206

Related Posts

2 Comments. Leave new

  • Hi Pinal,
    Have 2 questions here
    1-what will be wait type for autogrowth process (how to make sure that its the cause of performance issue)
    2-how to change the file growth to weekly bases
    Thanks

    Reply

Leave a Reply

Menu