Sunday, March 25, 2012

Database files free space (SQL Server 2005)

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;

No comments:

Post a Comment