DBCC command for data file size

Is their a DBCC command or some other kind of command that can show how much of data file is actually being used? I looking for something similar to the DBCC SQLPERF (LOGSPACE) command, but for data filesDo you want a DBCC command or will any other command also be ok ?

sp_spaceused

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

the sp_spaceused seems to do the trick, but only for one database. Is there a way to show database size for a number of databases like the DBCC comand that shows all the log files.

|||

You might try this:

-- Individual File Size query
SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *
FROM sys.database_files;

You could write a script like this to hit all of your databases.

USE DatabaseName;

GO

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *
FROM sys.database_files;

GO