How to split datafiles of a Database in to two

Question

How to split datafiles of a Database in to two

venu_ksheerasagaram

SSCommitted

Points: 1911
More actions
April 16, 2009 at 6:42 am

#203024

Hi friends
Here is my problem-- how to split the data files of a DB in to two with out effecting the process of running aplication.
Thanks
Venu Gopal.K
Software Engineer
INDIA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Philip-918225 Default port Points: 1491 More actions · Answer 1

Hi Venu,

Tips: Please make sure to provide as much information (db, versions etc) as possible regarding your environment to get a prompt reply from the group.

Thanks,

Philip.

venu_ksheerasagaram SSCommitted Points: 1911 More actions · Answer 2

venu_ksheerasagaram (4/16/2009)
Hi friends
Here is my problem-- how to split the data files of a DB in to two with out effecting the process of running aplication.
I am using SQL server on the system having Windows Server as OS.
The Database size is nearly of 200GB and the Datafile is growing like any thing. so, as to make it chunks i want to split those datafiles in to smaller datafiles so that it may be useful for me to store
Thanks

Venu Gopal.K
Software Engineer
INDIA

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

First of all find out the reason why you need to split the datafile?

One option is you add another datafile and sql server internally manage the same. You can also restrict the growth of datafile 1 once you add datafile 2.

Better undersatnd your requirement/environment before any ACTION.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 4

venu_ksheerasagaram (4/16/2009)
venu_ksheerasagaram (4/16/2009)
Hi friends
Here is my problem-- how to split the data files of a DB in to two with out effecting the process of running aplication.
I am using SQL server on the system having Windows Server as OS.
The Database size is nearly of 200GB and the Datafile is growing like any thing. so, as to make it chunks i want to split those datafiles in to smaller datafiles so that it may be useful for me to store
Thanks

200GB in a single file ? I hope you are extending with more than 1MB at a time (and not in percentages !)

It will certainly be worth to split. At least move the big tables to their own file(group)

If you are on SQL 2005 Enterprise Edition, doing that you can perform partial restores in case of DRP.

To facilitate this , you would be better off having only your catalog data in the primary filegroup, and all user objects on other filegroups !

Just ad a filegroup, allocate a sized file for it (so it immediately has at least the size of the current table + a little growth ).

Then move your table to it.

Have a look at this article : http://www.sqlservercentral.com/articles/Administration/64275/

or http://www.sqlservercentral.com/articles/Files+and+Filegroups/65538/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bguo SSC Veteran Points: 290 More actions · Answer 5

Can anyone help? How to split 4TB database from one datafile into (8)multiple data files? I have created a new database with 8 .mdf and one .ldf on a new server, on the current server, it only have one .mdf file, one .ldf file, how can I split the data file from one to right? Thanks a lot.

east400@Hotmail.com

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Before we can answer your question, why do you want to do the split to begin with? Your reasons will help determine the best method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

What's the reasons behind the split? What are you trying to achieve?

Please in future start a new thread for your questions. Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass