OK, this is valuable information.

But did I miss something after "... just run the following code:" ?

Leendert.

Try this and compare it to the task pad view in Enterprise Manager.

 SELECT AA.name, AA.filename, -- AA.dbSize,
  BB.name, BB.filename, --BB.dbSize,
  (AA.dbSize) * CC.low / 1048576 AS DBSize, -- This is the total database size.
  (AA.dbSize) * CC.low / 1048576 - AA.AvailableSpaceInMB AS DBUsedSpace,
  AA.AvailableSpaceInMB as DBAvailableSpaceInMB,
  (BB.dbSize) * CC.low / 1048576 AS LogSize,
  (BB.dbSize) * CC.low / 1048576 - BB.AvailableSpaceInMB AS LogUsedSpace,
  BB.AvailableSpaceInMB as LogAvailableSpaceInMB,
  (AA.dbSize + BB.dbSize) * CC.low / 1048576 AS TotalSize,
  AA.AvailableSpaceInMB + BB.AvailableSpaceInMB as TotalAvailableSpaceInMB
 FROM
 (select name, filename, convert(dec(15),size) as dbSize,
  size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB
  from dbo.sysfiles
  where (status & 64 = 0)) AA CROSS JOIN

 (select name, filename, convert(dec(15),size) as dbSize,
  size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB
  from dbo.sysfiles
  where (status & 64 <> 0)) BB CROSS JOIN

 (select low
  from master.dbo.spt_values
  where number = 1
   and type = 'E') CC

This is nice, but what if I want to see available space for all databases on my server?  I tried doing this by using the sp_msforeachdb proc, but for some reason I'm getting nulls for AvailableSpaceInMB.  Anybody know why?

exec sp_msforeachdb 'SELECT ''?'' as DBName, name AS FileName,
  size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )AS int)/128.0 AS AvailableSpaceInMB
 FROM [?].dbo.SYSFILES'

I could always result to using a cursor, looping thru the databases but I'd rather not do that.  If I can get the above statement to work, the next step would be to insert the results into a temp table so I can then see the free space for all files for all databases as the result of a single query.

The fileproperty function needs to be used in the context of the database.  I plan to call this from an MS Access database (I will convert it to .Net later) looping through each database on the server.  I will also be using this on multiple servers.

My plan is to use this to determine file growth, space issues, and to help project future disk space needs.

-- Try this.  It'll go through all the DBs if no name is specified, otherwise, just -- return it for the DB specified.

-- Wayne Fairless, DBA

-- J.T.

"I may not always know what I'm talking about, and you may not either."

Opps.  Forgot to change the DB between cursor lookups.  Edited to work on case sensitive collations, too.  Try this one instead....

USE MASTER

GO

IF (SELECT COUNT(*) FROM dbo.sysobjects WHERE NAME = N'proc_ShowAvailableSpace') = 1

DROP PROCEDURE dbo.proc_ShowAvailableSpace

CREATE PROCEDURE dbo.proc_ShowAvailableSpace

@DBName NVARCHAR(256) = NULL

SET NOCOUNT ON

DECLARE @TSQL NVARCHAR(512),

@RETCODE INT,

@CurrentDB VARCHAR(256)

IF @DBName IS NULL

BEGIN

DECLARE DB_CUR CURSOR

FOR

SELECT NAME FROM master.dbo.sysdatabases

ORDER BY NAME

OPEN DB_CUR

FETCH NEXT FROM DB_CUR INTO @CurrentDB

PRINT @CURRENTDB

WHILE @@FETCH_STATUS = 0

BEGIN

SET @TSQL = 'USE ' + @CurrentDB+ '; SELECT name AS [File Name (' + @CurrentDB + ')], ' +

'size/128.0 -CAST(FILEPROPERTY(name, ' +

CHAR(39) + 'SpaceUsed' + CHAR(39) +

')AS int)/128.0 AS [Available Space (MB)] ' +

'FROM [' + @CurrentDB + '].[dbo].[sysfiles]'

EXEC (@TSQL)

--print @tsql

FETCH NEXT FROM DB_CUR INTO @CurrentDB

END

CLOSE DB_CUR

DEALLOCATE DB_CUR

END

ELSE

BEGIN

PRINT 'Free space for database "' + @DBName + '":'

-- Check to see if the DB exists

IF (SELECT DB_ID(@DBName)) IS NULL

BEGIN

PRINT @DBName + 'does not exist.'

RETURN

END

ELSE

BEGIN

SET @TSQL = 'SELECT name AS [File Name], ' +

'size/128.0 -CAST(FILEPROPERTY(name, ' +

CHAR(39) + 'SpaceUsed' + CHAR(39) +

')AS int)/128.0 AS [Available Space (MB)] ' +

'FROM [' + @DBName + '].[dbo].[sysfiles]'

EXEC (@TSQL)

END

END

-- EXEC dbo.proc_ShowAvailableSpace 'master'

-- EXEC dbo.proc_ShowAvailableSpace