I have a database with a size around 100 GB, and I have serious problems expanding the size of the database. This is my setup:

  • SQL Server 2005, Version 9.0.4035
  • OS: Windows Server 2003 SP2 x86
  • Running as a guest on Hyper-V
  • Data file and log file on different disks (virtual and physical)
  • The disk for the MDF file is 500 GB and has nothing else on it, so no fragmentation problems etc.

At first, I had default settings for auto-growth by 10%. That started to fail with timeouts when the database was around 30 GB big or so, so I changed it to 1000 MB. Now even this failes, so I tried to increase the file size manually. I had to cancel my attempts because they "never" completed until I went down to a increase of 50 MB, and that took over 22mins! :-o Something must be seriously broken...

I've been monitoring the Average Disk Queue length for all disks (system, data and log), they were all idle except the disk that holds the log file, this one had always a disk queue length over 10. Before and after this operation it was idle, too.

That's the command I used to increase the filesize manually (I added 50 MB to the initial size):


Edit: When I created this database months ago I initialized it with 10 GB, and I can't remember that I had any remarkable delay. I can also e. g. restore other databases on this server with 2 or 3 GB, and it just works fine. The I/O performance on this machine is not superior, but far from that bad.

Any ideas? Thank you very much in advance!


I'm guessing that you've got some sort of storage performance problem going on. If you run sp_who2 on the spid that's expanding the file and see what the wait type is.

  • I still don't know exactly what the problem was, but today, even before I could apply the latest updates, I could increase the database file by 10 GB (!) just in no time at all. I have no explanation for this. Thank you all for your hints anyway. – realMarkusSchmidt Aug 29 '09 at 18:57

Have you setup Instant File Initialisation? If not might be worth looking into and switching on and see if there is any difference.

  • Interesting point. I ran the script and it shows "Instant Initialization enabled". So at least in my case it didn't help. – realMarkusSchmidt Aug 28 '09 at 14:00

We've had the same problem, the solution was to always manually grow the database in a large chunk. It would take 2-3 minutes to expand 500MB, nowhere near the 22 minutes that you are seeing.

i'll go with Chirang's suggestion of Instant File Initialization, if you're not totally paranoid.

But still, something does sound wrong. Is it a mirrored or parity raid setup?

  • Well, the manual grow was what I finally did and what took those 22min for 50 MB (!)... so, sorry, still no luck. I'll reboot the machine over the weekend, apply the latest cumulative update pack for SQL Server and try again. – realMarkusSchmidt Aug 28 '09 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.