Tuesday 21 October 2008

IO - Filespace report

Pulled straight out of SSMS :), reports on file space utilization, without having to use the UI, useful if you want to reclaim space, or for checking whether you are close to hitting a manual limit on a file if you prevent automated file growth

set xact_abort off
set ansi_warnings off
set arithignore on

create table #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
insert #tmpspc EXEC ('dbcc showfilestats')

SELECT
s.file_id AS [ID],
s.name AS [Name],
s.physical_name AS [FileName],
CAST((s.size * CONVERT(float,8)/1024) as decimal(20,0)) AS [Size MB],
CAST(CAST(tspc.UsedExtents*convert(float,64) AS float)/1024 as decimal(20,0)) AS [UsedSpace],
cast(((tspc.UsedExtents*convert(float,64)) /(s.size * CONVERT(float,8)))*100 as decimal(10,0)) as [Percentage Used]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
ORDER BY
[ID] ASC
--[UsedSpace] DESC
drop table #tmpspc

No comments: