Try this query:
if object_id('tempdb..#dbsize') is not null
drop table #dbsize;
GO
create table #dbsize (database_name sysname,
currrent_size int,
max_size int,
is_unlimited int,
current_log_size int,
max_log_size int,
is_log_unlimited int);
exec sp_msforeachdb '
insert #dbsize
select ''?'',
sum(case when filename like ''%.ldf'' then 0 else size end),
sum(case when filename like ''%.ldf'' then 0 else maxsize end),
min(case when filename like ''%.ldf'' then 0 else maxsize end),
sum(case when filename like ''%.ldf'' then size else 0 end),
sum(case when filename like ''%.ldf'' then maxsize else 0 end),
min(case when filename like ''%.ldf'' then maxsize else 0 end)
from [?].sys.sysfiles';
select database_name as DatabaseName,
currrent_size / 128.0 as CurrentDBSize,
case when is_unlimited = -1 then 'unlimited' else str(max_size/128.0) end as MaxDBSize,
current_log_size/128.0 as CurrentLogSize,
case when is_log_unlimited = -1 then 'unlimited' else str(max_log_size/128.0) end as MaxLogSize
from #dbsize
order by database_name;
GO
if object_id('tempdb..#dbsize') is not null
drop table #dbsize;
GO
When a database on SQL Server reaches its maximum file size, it may result in poor performance. The query above lists the current and max for each database. Sample results:
The default for the max size is unlimited for the primary data file and 2097152 for the log file. When unlimited is used the size of the database is limited by the physical disk space available. To change these settings see the screen shot below:
).
