how to check maximum db size and used data size sql server

Hassan Siddiqui

Hi Every one
I want to know what size of data a sql server 2008R2 database can store and what size is actually used and remaining data size

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manikandan Murugesan9y ago
SELECT RTRIM(name) AS [Segment Name], groupid AS [Group Id], filename AS [File Name],
   CAST(size/128.0 AS DECIMAL(10,2)) AS [Allocated Size in MB],
   CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used in MB],
   CAST([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max in MB],
   CAST([maxsize]/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB],
   CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST([maxsize]/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles
ORDER BY groupid DESC
Manikandan Murugesan9y ago
Refer the below links

For check the limit of the disk : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7a44947a-9082-4ad6-a3f7-46e69d461630/find-a-database-size-limit-from-within-the-sql-server?forum=transactsql

For check the Space availability :
https://www.mssqltips.com/sqlservertip/1805/different-ways-to-determine-free-space-for-sql-server-databases-and-database-files/
Rajkiran Swain9y ago
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
Sundaram Subramanian9y ago
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO