Tuesday, March 27, 2012

Database Grow Problems

I am having problems when I try to grow my database. It seems to be taken a
long time (1 hour and still going) and is also making the system
unresponsive. The CPU is at 0 but the computer is so slow nothing can be
done. This is a dual processor system with 2ghz processors. I am using the
express version and am trying to grow a database from 45 mb to the maximum
of 4096 mb.
Is this normall or is there something wrong with the hard drive, system or
database? I ran the dbcc tool and it did not report any problem with the
tables or index.
The reason I decided to grow it to the max is because my incremental grows
were slowing down my application. I figured if I set it to the maximum it
would speed things up. I didn't know growing it would take this long.
Bob
That seems excessive for just 4GB of growth but by default it will have to
zero initialize every bit in the file. This normally takes a while and
depends heavily on the hardware as to how long. If you are running on
Windows XP or Windows 2003 then you may be able to take advantage of Instant
file initialization. From BOL:
Instant file initialization is only available if the SQL Server
(MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME.
Members of the Windows Administrator group have this right and can grant it
to other users by adding them to the Perform Volume Maintenance Tasks
security policy. For more information about assigning user rights, see the
Windows documentation.
Andrew J. Kelly SQL MVP
"Bob" <msgdev@.hotmail.com> wrote in message
news:%23RvjnVQVGHA.5288@.TK2MSFTNGP14.phx.gbl...
>I am having problems when I try to grow my database. It seems to be taken
>a long time (1 hour and still going) and is also making the system
>unresponsive. The CPU is at 0 but the computer is so slow nothing can be
>done. This is a dual processor system with 2ghz processors. I am using
>the express version and am trying to grow a database from 45 mb to the
>maximum of 4096 mb.
> Is this normall or is there something wrong with the hard drive, system or
> database? I ran the dbcc tool and it did not report any problem with the
> tables or index.
> The reason I decided to grow it to the max is because my incremental grows
> were slowing down my application. I figured if I set it to the maximum it
> would speed things up. I didn't know growing it would take this long.
>
> Bob
>

No comments:

Post a Comment