April 7, 2010 at 7:38 pm
Hi Folks,
i think this might be an easy question and i should know it.
the datafiles in my database and getting big and I would like to create a new one.
i was just wondering should i kick everyone out of the database before I create these databases files?
thanks
April 7, 2010 at 7:56 pm
Why do you think you need to add another file? Are you having performance issues with the current file? How large is the file now?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 7, 2010 at 9:11 pm
3 files approx 60gb each.
easy maintenance mainly - e.g if i ever need to move to database detach and reattach is easier.
Performance is another concern even though i have no current complaints.
April 7, 2010 at 9:20 pm
I'm not sure why attach / detach would be easier with more files. Typically adding more files makes maintenance slightly more complicated.
If you can separate different objects onto different filegroups, or you need to move files/filegroups to new storage because you are running out of space, that makes sense, but 60GB isn't that big. I've had 100GB single file databases.
To create a new filegroup, you can use ALTER DATABASE (http://msdn.microsoft.com/en-us/library/aa275464%28SQL.80%29.aspx), or the SSMS GUI. Go to the Properties for the database and just type in a new file or filegroup. http://technet.microsoft.com/en-us/library/ms189253.aspx
April 7, 2010 at 9:41 pm
Ok you got me on that.
Is there any disadvantage to let your data file grow continously? maybe fragementation
April 7, 2010 at 9:50 pm
You could get fragmentation, but you should not be "letting them grow." Monitor space, and when it gets low, make a big growth to give you 3-6 months of space. If you haven't done this, then you might need to defragment the files themselves. Also, you might have fragmented tables when you are done, so you would want to rebuild their clustered indexes.
April 7, 2010 at 10:00 pm
bodhilove (4/7/2010)
Ok you got me on that.Is there any disadvantage to let your data file grow continously? maybe fragementation
That would all depend upon what your IO subsystem is capable of and what kind of SAN you have. I have several databases that are more than 200GB in single files - on the same server with no problems.
I also have several systems with 800GB single file databases and no issues with IO or fragmentation. Most are on DMX with a couple on a Clarion.
In my case, we have lots of spindles backing those LUNs so there isn't a big issue. If I didn't have that, I would consider splitting to separate arrays to get improved performance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply