Eric,

I am using SQL7.0. My database is spanned across 5 physical disc drives. Pysical file 1.mdf resides in C:, file 1.ldf in d:, file 1.ndf in e:, 2.ndf in f: and 3.ndf in g:

1. My requirement is to know how much space is used in each file. 2. I also want to know how much space is available in each discs (I am not too sure if this is probably out of SQL Server discussion).

Thanks in advance,

Kothan.

-- Anonymous, November 27, 1999

Answers

Kothan,

I don't think that SQL Server has good tools for examining the space used in each file in a filegroup. But, in general, each file in a filegroup is going to have a similar percentage of free space. The SQL Server Books Online article titled, "Managing Space Used by Objects" states it this way:

SQL Server allocates a new extent to an object only when it cannot find a page in an existing extent with enough space to hold the row being inserted. SQL Server allocates extents from those available in the filegroup using a proportional allocation algorithm. If a filegroup has two files, one of which has twice the free space of the other, two pages will be allocated from the file with more empty space for every one page allocated from the other file. This means that every file in a filegroup should have a similar percentage of space used.

To know how much space is available on each disk, you can use the NT explorer and My Computer icon. Also, if you use SQL Enterprise Manager, right click on your database name, and select Properties... a popup dialog will appear titled, Properties. On this window, you can click on the ... button to be shown the Locate Database File dialog, which shows how much free space is available on each disk drive.

Hope this helps,

Eric

-- Anonymous, November 29, 1999