February 15, 2013 at 4:54 pm
I was recently invited to checkup on a new server. On this server, I ran a series of diagnostic checks adapted from Glen Barry's collection of scripts.
To cut to the chase, I had databases with a 'high' vlf counts (574, etc) and for each database began the process of getting the vlf count down to a reasonable level by first backing up the transaction log, shrinking the log and then running DBCC LogInfo to check the impact on the vlf total. In some cases I did this twice (Backup, Shrink, DBCC LogInfo) until I got a low vlf count.
Once the I was satisfied with the number of vlfs, I wanted to regrow the log files - that is where the fun began. First I tried growing the log file by a multiples of 8 GB using:
ALTER DATABASE dbname MODIFY FILE (NAME = N'dbname_log', SIZE = 8GB)
But the transaction would lock up and time out with a PREEMPTIVE_OS_WRITEFILEGATHER wait type.
Not wanting to throw the database into single user mode, I ended up growing the log in 2 GB increments.
Although the outcome was good, and I suspect this all comes down to workload, blocking and substandard IO configuration, I would like to see other viewpoints on what the problem might have been, how it could have been prevented.
February 15, 2013 at 5:23 pm
There is a bug if you grow in 4gb multiples. See http://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/
Use 8000mb instead of 8gb
February 16, 2013 at 6:36 am
Thank you for your response Vedran,
I was somewhat aware of the issue over 4GB increments, and will consider this next time I manually grow a log file. But at this point I do not believe this was the issue.
My log grow statement timed out, but blogs about the 4GB issue indicate that the regrowth operation executed quickly, almost instantly, but did not actually regrow the log.
Bret
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply