December 14, 2007 at 2:14 pm
Hello rooms,
Please help and advise best practice to create over 100 GB database in D and E drive Raid 1 + 0.
Some suggested create filegroups.
My t-sql:
Create database DB01
ON PRIMARY
(Name = 'DB01'
,Filename = 'D:\SQL_Data\DB01.mdf'
,Size = 120000MB
,Maxsize = 200000MB
,Filegrowth = 20MB)
LOG ON
(Name = 'DB01Log'
,Filename = 'E:\SQL_Log\DB01_log.ldf'
,Size = 30000MB
,Maxsize = 60000MB
,Filegrowth = 10MB
)
GO
Thank you for your help.
Best regards,
TJ
December 14, 2007 at 3:52 pm
Assuming all your harddisks have that much free space available, I don't see any reason not to stay with what you have.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 14, 2007 at 7:27 pm
I listened to Kimberly Tripps webcast. If I am not mistaken, she advised to create file group for DB > 100 GB.
Any suggestions.?
Thank you.
TJ
December 15, 2007 at 3:02 pm
When you create a database, all of the system objects go into the filegroups where they reside in the model database. Unless you've changed that, that means they all go to the PRIMARY filegroup.
A sound piece of advice is to create at least one more filegroup, and set that to be the default filegroup for the database. Any new user object - tables, indexes, etc., will then be placed in the second filegroup, unless the create script for the object included the phrase ON [PRIMARY].
That way, you have separation of system objects and user objects. There are several reasons to do this, but keeping your database up is one of the bigger ones.
If you have all of your system and user objects in one filegroup, and your user objects consume all the available space in the filegroup, you may chew up the space you need in the system tables to do anything about it.
Also, SQL Server 2005 Enterprize Edition can bring a database online during a restore with only the primary filegroup. If that small - only the system stuff - you can recover it quickly. Then start bringing other filegroups online - restoring them in order of importance (provided you added more filegroups, and split things between them by priority). If your database is only 100GB, that's overkill, though. You can use multiple filegroups for tuning, which is another topic entirely.
If you keep your user objects separate from your system objects, you can give the system filegroup plenty of free space for the system objects to grow. Even if the user filegroup grows and fills to consume all available free disk space, there will still be plenty of room in the primary filegroup to perform steps to keep things going (adding files on other LUNs, etc.).
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply