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 (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 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

Sundaram Subramanian · 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

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

Answers (4)