• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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
0
anushahanna
Asked:
anushahanna
4 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now