Dear friends
I need to report the amount of free space in each datafile of a database.
I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.
Please help.
Regards
Parviz
Take a look at the sp_spaceused text. You should be able to derive from there.exec sp_helptext 'sp_spaceused'|||
Thanks for the fast reply, but please note that sp_spaceused returns the free space for the whole database files including the LOG file. I need the info fo each file in a database seperately.
If you have a look at summary reports for a database (SQL 2005 SSMS),you would see a table and a pie chart showng the status for each file.
Thanks again.
Parviz
|||Yup. sp_spacedused can be used to get the data file usage. You can then use 'dbcc sqlperf(logspace)' to get the log usage.|||Here's some code, cribbed from a sqldbatips report designed to mimic the taskpad in SSMS. (http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx). All I did was add some code to show the amount free. Seems to work well. Posting here because this was one of the top results in Google when I went looking for code myself.
create table #data(Fileid int NOT NULL,
[FileGroup] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[Name] sysname NOT NULL,
[FileName] varchar(300) NOT NULL)
create table #log(dbname sysname NOT NULL,
LogSize numeric(15,7) NOT NULL,
LogUsed numeric(9,5) NOT NULL,
Status int NOT NULL)
insert #data exec('DBCC showfilestats with no_infomsgs')
insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')
select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from
(
select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]
from #data
union all
select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()
--order by [Type],[Name]
)a
order by [Type],[Name]
drop table #data
drop table #log
|||This will work for you:
-- 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;
