see all data/log files used space at one time

I can use sysfiles and use the FILEPROPERTY(spaceused) to see how much each data/log file has unused space, but for only for one database at a time.

what system table will help you to gather this info for all databases?

thanks
LVL 6
anushahannaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
declare @sql nvarchar(max)
select @sql = coalesce(@sql + ' union all ','') + '
select fileid, groupid, size, maxsize, growth, status, perf,
name=name collate database_default, filename=filename collate database_default
from [' + name + ']..sysfiles'
from master..sysdatabases
set @sql = @sql + ' order by name, fileid'
exec (@sql)
0
k_murli_krishnaCommented:
Check all databases for file space used
http://www.bigresource.com/Tracker/Track-ms_sql-11S0piFE/
0
cyberkiwiCommented:
Plus this one
set nocount on;
create table ##spaceused(database_name sysname, database_size varchar(100),
unallocated_space varchar(100), reserved varchar(100),
data varchar(100), index_size varchar(100), unused varchar(100))
;
declare @sql nvarchar(max)
select @sql = coalesce(@sql,'') + '
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
		, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
		from [' + name + '].dbo.sysfiles

	select @reservedpages = sum(a.total_pages),
		@usedpages = sum(a.used_pages),
		@pages = sum(
				CASE
					-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
					When it.internal_type IN (202,204) Then 0
					When a.type <> 1 Then a.used_pages
					When p.index_id < 2 Then a.data_pages
					Else 0
				END
			)
	from [' + name + '].sys.partitions p join [' + name + '].sys.allocation_units a on p.partition_id = a.container_id
		left join [' + name + '].sys.internal_tables it on p.object_id = it.object_id

	/* unallocated space could not be negative */
    insert ##spaceused
	select 
		database_name = ''' + replace(name,'','''') + ''',
		database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
			* 8192 / 1048576,15,2) + '' MB''),
		''unallocated space'' = ltrim(str((case when @dbsize >= @reservedpages then
			(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
			* 8192 / 1048576 else 0 end),15,2) + '' MB''),
	/*
	**  Now calculate the summary data.
	**  reserved: sum(reserved) where indid in (0, 1, 255)
	** data: sum(data_pages) + sum(text_used)
	** index: sum(used) where indid in (0, 1, 255) - data
	** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
	*/
		reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + '' KB''),
		data = ltrim(str(@pages * 8192 / 1024.,15,0) + '' KB''),
		index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + '' KB''),
		unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + '' KB'')
'
from master..sysdatabases
;
set @sql = 
'
declare @dbsize float
declare @logsize float
declare @reservedpages bigint
declare @usedpages bigint
declare @pages bigint
' + @sql
;
exec (@sql);
set nocount off;
select * from ##spaceused;
drop table ##spaceused;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geek_vjCommented:
To get the details clearly in a readable format, you can execute the below code:

SELECT DB_NAME(database_id) AS DatabaseName,
CAST([Name] AS varchar(20)) AS NameofFile,
CAST(physical_name AS varchar(100)) AS PhysicalFile,
type_desc AS FileType,
((size * 8)/1024) AS FileSize,
MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size = 0 THEN 'NO_GROWTH'
WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))
ELSE 'Unknown'
END,
SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'
WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))
ELSE 'Unknown'
END,
Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'
WHEN growth > 0 THEN ((growth * 8)/1024)
ELSE 'Unknown'
END,
GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'
WHEN is_percent_growth = 0 THEN 'MBs'
ELSE 'Unknown'
END
FROM master.sys.master_files
WHERE state = 0
AND type_desc IN ('LOG', 'ROWS')
ORDER BY database_id, file_id
0
anushahannaAuthor Commented:
thanks all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.