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

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

DBCC CheckDB

Does DBCC CheckDB update the sysindexes table, similar to DBCC Updateusage?
Thanks,
MitchSince DBCC CHECKDB by itself does not acquire a table lock, it suggests the
system tables are not necessarily updated. This is the same case with DBCC
CHECKTABLE we well.
Hoewever, if you use the REPAIR options associated with DBCC CHECKDB, it
will update the sysindexes table since it can include allocation and
deallocation of rows and pages for correcting allocation errors, structural
row or page errors, and deletion of corrupted text objects.
Anith