February 3, 2014 at 10:24 am
We're in the middle of creating a 2008 database with the following files: (see the attachment)
The creation process has been running for three hours. Is this unusual given the size of the files? Disk activity seems fairly low, as does CPU and RAM. This is on a 2008 R2 64-bit server with 28GB of RAM.
We weren't expecting this would take anywhere near this long. Any thoughts would be greatly appreciated.
thanks,
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 3, 2014 at 10:41 am
The speed of the create depends on the speed of the disk files plus the fragmentation of the underlying disks. Creating a DB on a SAN-based SSD will be rapid; the identical DB create on a slow SATA drive directly attached will be significantly slower.
Thanks
John.
February 3, 2014 at 10:43 am
Thank you John. I was basically looking for any answer other than, "Oh my no, something's definitely wrong." And you provided it! 🙂
thanks,
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 3, 2014 at 11:27 am
Looking at the size of the files, the instant file initialization will probably also shave off some time. Ofcourse there could be considerations to not enable that option.
February 3, 2014 at 11:29 am
sqlsniper (2/3/2014)
Looking at the size of the files, the instant file initialization will probably also shave off some time. Ofcourse there could be considerations to not enable that option.
At this point we're over four hours into the process, so I'm hesitant to kill it and start over with a different option enabled.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 3, 2014 at 11:32 am
ronmoses (2/3/2014)
Thank you John. I was basically looking for any answer other than, "Oh my no, something's definitely wrong." And you provided it! 🙂thanks,
ron
My pleasure. This may sound weird, but does sp_who2 show the SPID that is running the create database blocked? If no, do you see the I/O counts increasing?
Thanks
John.
February 3, 2014 at 11:32 am
With an operation already running it's not the best thing to do (makes no difference).
Also it's a Windows policy, definitely one to check out as it disables the 'zeroing out' of datafiles.
February 3, 2014 at 11:43 am
JohnFTamburo (2/3/2014)This may sound weird, but does sp_who2 show the SPID that is running the create database blocked? If no, do you see the I/O counts increasing?
Hmmm... it is blocked. And when I kill the process that's blocking it, another process jumps in and blocks it again. I think it's time to turn this over to our dba. Thanks for the help.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
February 3, 2014 at 11:56 am
ronmoses (2/3/2014)
JohnFTamburo (2/3/2014)This may sound weird, but does sp_who2 show the SPID that is running the create database blocked? If no, do you see the I/O counts increasing?
Hmmm... it is blocked. And when I kill the process that's blocking it, another process jumps in and blocks it again. I think it's time to turn this over to our dba. Thanks for the help.
ron
What's blocking it?
Thanks
John.
February 3, 2014 at 11:58 am
JohnFTamburo (2/3/2014)
What's blocking it?
Some service I know nothing about. I've brought it to the attention of the appropriate folks.
thanks!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply