SQL Server 2000 Data files

Can you have 2 .mdf's?

[dataguy]

I'm running MS SQL 2000 on several servers. A few months ago I added a hard drive for the data only on one of the servers. I disconnected the database, copied the mdf files over, specified the new location using the database properties/Data Files in Enterprise Manager and then connected it again. This all worked fine except for the downtime experienced while copying the data over.

When I did this, I noticed that you can have more than one location specified for the data on the Data Files tab in the database properties. What is this for? Could I possibly create a second set of datafiles, allowing me to not have to disconnect the database to move the data to a new hard drive?

[aspdaddy]

You can use multiple files for increasing I/O performance but its best to let your RAID system do this automatically, 1 file per volume.

[syber]

You can also add additional files (.ndf) to databases as an overflow area. So in your scenario, you could have left the existing .mdf file where it was and allocated additional space for the database to the new drive.

Another technique is to create a new "Filegroup" and assign storage on another hard drive to it. You would then place all your indexes on the new filegroup. This can give you a performance boost as you would now have two hard drives working to satisfy a query.

[aspdaddy]

A very simple way to boost performance is to put the log file (ldf) and device (mdf) on difefernt physical drives.

When SQL Server writes data it writes simultaneously to both the log file and the device, creating a bottleneck.

If you are doing a lot of updates, inserts, deletes within code loops , or set level inserts/updates on the database or importing a lot of data, then this mod will spped things up quite a bit. It wont however increase SELECT performance.