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 files

Do 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

DBCC showfilestats

sp_databases

Hi

DBCC showfilestats will not give you exact size , sp_helpdb can give you size of all db.

You can also use linked query to find complete details.

Script To Find Size of All Databases In SQL Serve