June 13, 2008 at 12:18 pm
Hello room,
I needed to create 100 GB database on D and E drive.
Create database DB100GB
ON PRIMARY
(Name = 'DB100GB',
Filename = 'D:\SQL_Data\DB100GB.mdf',
Size = 100000MB,
Maxsize = 200000MB,
Filegrowth = 50MB)
LOG ON
(Name = 'DB100GBLog',
Filename = 'E:\SQL_Log\DB100GB_log.ldf',
Size = 25000MB,
Maxsize = 50000MB,
Filegrowth = 20MB);
go
When I executed on SSMS, it took more than hour.
Can anyone advise why it took so long to create and it's normal?
Note: my D drive only have 270GB space.
Regards,
TJ
June 13, 2008 at 2:23 pm
The slowness is because SQL engine works hard to get 100G space from your HD (if your HD already has other stuff, it may take longer).
Usually, I create a relatively small size, say, 500M, then, increase gradually.
June 13, 2008 at 2:31 pm
Try giving the initial size as the amount of space it would need during the first load.
You could keep the initial size as say 100 MB or 500 MB...just an example, it's unique to your case though. Initial size of 10GB tries to allocate all of that memory while creating the database and so it takes a very long time.
June 13, 2008 at 3:10 pm
Thanks for your advise. 😛
June 13, 2008 at 3:13 pm
Eventually, I created the database with initial size of 10GB.
Then, I incremented the database size.
June 13, 2008 at 3:29 pm
Hi,
I would recommend that if you are configuring your database on clean hard disks, that you reserve as much space as you think you will realistically need for the database. This is becuase allocating the data file in a single blobk will ensure that it is stored contiguosly on disk.
It is also why best practice typically advocates that you manually manage the growth of your databases where possible. For example suppose you have several databases that are set to autogrow at 50MB, each time additional space is required, you will end up with your data files being stored sporadically across the disk.
Hope this helps.
John
June 13, 2008 at 4:09 pm
You could speed up the database creation by using Instant File Initialization.
Read about it in Books Online.
http://msdn.microsoft.com/en-us/library/ms175935.aspx
Kimberly Tripp has written a blog post about it.
http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx
Ola Hallengren
June 13, 2008 at 4:10 pm
Using Instant File Initialization in SQL 2005 should bring the creation time down significantly. If it's not enabled, zeros are written to the data and log files when initialized.
To enable it, open Local Security Settings under Control Panel/Adminsitrative Tools. Under User Rights Assignment open Perform Volume maintenance tasks and add the service account used to run SQL Server, then reboot.
June 13, 2008 at 4:19 pm
Please note that instant file initialization only works for data files.
Log files are always zero initialized.
Ola Hallengren
June 13, 2008 at 8:25 pm
I wouldn't worry about how long it takes to create the initial space. The important part is that you create as large a piece as you think you'll need to support a year or two of growth. It'll prevent fragmentation and it'll keep people from trying to use your precious disk space for other stuff.
The other thing is that 50MB growth isn't enough. And growth should never take you by surprise if you do it right. You should have a 90% full alarm somewhere and, during your regular maintenance, you should identify how much space is added each week so you can actually plan for growth during quiet times. This will also allow you to plan for disk purchases instead of being in a panic when you run out of room.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply