Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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):

ALTER DATABASE [MyDB] MODIFY FILE ( NAME = N'MyDB', SIZE = 103987200KB , FILEGROWTH = 51200KB )

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!

share|improve this question
add comment

3 Answers

up vote 0 down vote accepted

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.

share|improve this answer
    
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. –  markus Aug 29 '09 at 18:57
add comment

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

share|improve this answer
    
Interesting point. I ran the script and it shows "Instant Initialization enabled". So at least in my case it didn't help. –  markus Aug 28 '09 at 14:00
add comment

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?

share|improve this answer
    
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. –  markus Aug 28 '09 at 21:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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