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

Question

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

Manikandan Murugesan · Answer

SELECT RTRIM( name ) AS [Segment Name ] groupid AS [ Group Id] filename AS [File Name ] CAST ( size /128.0 AS DECIMAL (102)) AS [Allocated Size in MB] CAST (FILEPROPERTY( name  'SpaceUsed' )/128.0 AS DECIMAL (102)) AS [ Space Used in MB] CAST ([maxsize]/128.0 AS DECIMAL (102)) AS [ Max in MB] CAST ([maxsize]/128.0-(FILEPROPERTY( name  'SpaceUsed' )/128.0) AS DECIMAL (102)) AS [Available Space in MB] CAST (( CAST (FILEPROPERTY( name  'SpaceUsed' )/128.0 AS DECIMAL (102))/ CAST ([maxsize]/128.0 AS DECIMAL (102)))*100 AS DECIMAL (102)) AS [Percent Used] FROM sysfiles ORDER BY groupid DESC

Manikandan Murugesan · Answer

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 Swain · Answer

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

Sundar · Answer

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