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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.