Data file space used calculations

Hi,

Is there any table I can query from to get the space used by a data or log file? I can get this info from TaskPad but I want to query from tables. The sp_spaceused procedure gets the info for a database or table but not the 'data or log' files.

Thanks for any help.

VinnieYou can do this

select cast(size * 8 as int) as Size from dbo.sysfiles

The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

HTH|||Originally posted by rhigdon
You can do this

select cast(size * 8 as int) as Size from dbo.sysfiles

The sysfiles table stores the size as size of 8kb pages so you have to multiply. (the statement output is in KB, if you wanted it different you can change the math)

HTH

Thanks a lot but the sysfiles can give me only size whereas I need the space used and space free for a data file /log file. Any other suggestions are appreciated.

Thanks in advance.|||For log file you can do:

dbcc sqlperf('logspace')

I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

HTH|||Originally posted by rhigdon
For log file you can do:

dbcc sqlperf('logspace')

I'm not sure about how to do for the data file, I know sp_spaceused is pretty close.

HTH

Hi,

That is exactly what I am looking for is 'data file'. I could get for log file in sysperfinfo table. I was unsuccessful to find any info for datafile to calculate space used or space free. How does the Taskpad display these things? They may be doing from table. What is it is the question? If any one can help It will be a great help.

Thanks
Vinnie|||True, you can use SP_SPACEUSED to get the data, index space usage and DBCC SQLPERF(LOGSPACE) for the %age of Tlog used for any database.

Run SP_HELPFILE to get the information for physical files associated to that database.

Refer to Vyas's link (http://vyaskn.tripod.com/track_sql_database_file_growth.htm) for more information.