SQL Server Autogrow just locked a database up

We've just had a problem with our SQL Server where one of the databases locked up on any data changes. We managed to get everything back up normally by restarting the service.

Afterwards, I started looking at the SQL Server logs, and saw a lot of autogrow events in quick succession on the log file for this particular database (screenshot attached)

What would cause this at such a rate? I always assumed there'd be one autogrow event, which takes a few seconds then it's done (I've not particularly looked for autogrow events before, but I know when it happens, everything seems to just work as normal)

I have checked the disk space on the partition where the log file for this database is, and there is 55Gb of free space

Cheers
sqllog.png
LVL 5
advfinanceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I always assumed there'd be one autogrow event, which takes a few seconds then it's done
Not really. Depends on what you set for the autogrow.  Imagine that you set 1MB for autogrow and SQL Server will need 100MB, then you'll have 100 autogrows of 1MB each and that's why it's also wise to set a value that's large enough to reduce the number of the autogrows but not so large so it won't take much time for the file to grow.

What would cause this at such a rate?
Check for transactions that works with massive data. Import/Export processes, reindexes, bulk inserts, ...
0
advfinanceAuthor Commented:
I've just had a look, the log file is 15Gb, and the autogrow is set to 10% (so I presume it was adding about 1.5Gb).

There doesn't appear to have been anything unusual going on with data changes, certainly nothing that would bulk insert a lot of lines, our SAS user (our risk analyst) wasn't doing anything with SAS to pull any data, and there was no unusual activity with any other parts of the system that we've identified.

However, would you say that 10% might be overkill, and I should look to setting it to an actual Mb value, maybe 512Mb?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
However, would you say that 10% might be overkill, and I should look to setting it to an actual Mb value, maybe 512Mb?
Avoid using percentages. 15GB will grow 1.5GB each time and if you reach 50GB will grow 5GB and so on.
Working with a constant value will help you to do your own capacity management for the disk resource.
You can try to start with 512MB but keep an eye on the transaction log to verify that there's no issues during the autogrowth. If so, reduce the value.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.