Physical file size

Question

Physical file size

Ken Grissom-138180

SSChasing Mays

Points: 601
More actions
December 23, 2004 at 10:16 am

#87129

How do you determine how large you let you physical files in a database get?

If you have a large database, even if you only have one partition for the data files, is it a good practice to have more physical files instead of having one big one?

If you have one big file and you add another physical file, does SQL Server distribute the data currently in the database to both physical files or does it only use the new file for new data? If it does redistribute the data currently in the database, what kind of a performance hit do you take while that is being done? Is there a way using SQL to see the amount of free/used space in each physical data file?

As far as size I am currently dealing with about a 20 GB database (and growing about at the rate of 2.5 GB/month).

We have a 200GB RAID 5 partition and a 18 GB RAID 1 partition for log files on a SAN which is mirrored to another building.

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 1

Yes. It's better to have multiple data files then a single file because SQL Server creates a backup thread for each file that is being backed up.

If you already have a big file and you add another file, SQL Server will distribute the data to both files if both file size is set to unlimited. If the size is set to 'restrict file growth', it will distribute as long as it did not hit the restricted size. You can use task pad of EM (Enterprise Manager) to view the free space.

Ken Grissom-138180 SSChasing Mays Points: 601 More actions · Answer 2

You stated "It's better to have multiple data files then a single file because SQL Server creates a backup thread for each file that is being backed up." Does this depend on specifying multiple backup files in the backup statement?

Some more follow-up questions for you or anyone else:

On a 4 processor box what would be the optimum number of files? How large is a "big data file" in the SQL Server world?

Can someone answer the question: "If it does redistribute the data currently in the database, what kind of a performance hit do you take while that is being done?"

And to clarify the question: "Is there a way using SQL to see the amount of free/used space in each physical data file?" I would like to know if there is a way to do this from TSQL?

Can adding a data file be done while the server is under load? If so will it affect users access to the database?

THANG HOANG SSChasing Mays Points: 632 More actions · Answer 3

1) Typically, you'd have multiple files when you have direct control of seperate physical disk devices for data placement, and for the purpose of performance. But since you're already on RAID-5, which achieves similar objective (i.e. fault tolerance and performance via disk striping). In my opinion, you're not going to gain much if anything by having multiple data files.

2) The main advantage about multiple files with respect to backup is that you can selectively backup individual file.

3) I don't believe SQL Server redistribute the existing data when you're adding new file to the database, therefore it should not affect ongoing processes.