Skip to Content
avatar image
Former Member

Creating the datafiles in SQL2000

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Apr 02, 2009 at 08:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 02, 2009 at 07:36 PM

    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

    Add comment
    10|10000 characters needed characters exceeded