OK, this is valuable information.
But did I miss something after "... just run the following code:" ?
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
-- EXEC dbo.proc_ShowAvailableSpace 'master'
-- EXEC dbo.proc_ShowAvailableSpace
-- 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....