Creating the datafiles in SQL2000

[Former Member]

i am using SQL2000 with win2003server.

My query is, is that possible to create more datafiles in SQL2000?

i mean, currently i am having three datafiles with one logfile.

(i.e. prddata1.mdf,prddata2.ldf,prddata3.ldf and prdlog1.ldf)

i want to create the three more datafiles.

after creating the new datafiles, database growth should be with newly created datafiles only.

is there such concept exists in sql?

Regards,

Gayathry.

[Former Member]

Hello my friend

To have a multiple data files is a good idea with 100 - 200GB each, locates on separated logical drives which span as many disk as you can to maximize I/O performance. In SQL 2000, it must be performed while SAP instances are offline.

And regarding free percentage in each data file, I don't think it can be controlled in SQL server world if I'm right here. As long as SQL server has sufficient space for this DB, it distributes this kind of space resource automatically. But for performance perspective, there's another option called "fill factor" that you can control, which is about how full each index page can be. It's introduced in here: http://msdn.microsoft.com/en-us/library/aa196711(SQL.80).aspx

Generally, if you have enough resource of storage, I'd like to set it to 90. But with SQL 2000, if this value of fill factor is higher than 95, storage I/O performance could be vary, depends on what kind and which brand of your storage device is.

Thanks,

Effan

[Former Member]

yes you can add more datafiles to the SQL server,that will not be an issue and it is quite easy with SQL server studio

You can find the procedure for the same here:

http://technet.microsoft.com/en-us/library/ms189253.aspx

but your second question,I am not sure but if there is no space in the existing data files,SQL server wil write to the datafiles which have free space,so if you add new files,SQL server will write to them

Rohit