Home / server / Questions / 59936
Accepted
realMarkusSchmidt
Asked: 2009-08-29 04:15:28 +0800 CST

SQL Server file growth extremely slow

  • 772

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!

sql-server-2005

3 Answers

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

    • 0

  2. 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?

    • 0
  3. Best Answer

    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.

    • 0