Microsoft SQL Server 2008
--
Questions
--
Followers
Top Experts
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
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
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
I always assumed there'd be one autogrow event, which takes a few seconds then it's doneNot 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, ...
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?
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?
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Microsoft SQL Server 2008
--
Questions
--
Followers
Top Experts
Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.