Check if database files are nearing the SQL Server (Express Edition) limits

Check Sql Server file size limit.sql

-- To check if database files are nearing the SQL Server (Express Edition) limits

DECLARE @version varchar(32) = CONVERT(varchar(32), SERVERPROPERTY ('ProductVersion'));
SELECT @version, @@VERSION;

DECLARE @limit_GB float;
IF SERVERPROPERTY('EngineEdition') = 4 -- Express Edition
BEGIN
	-- SQL Server Express Edition:
	-- Up till SQL Server 2008, limit is 4 GB / file
	-- As from SQL Server 2008 R2, limit is 10 GB / file

	DECLARE @major int = CONVERT(int, SUBSTRING(@version, 1, CHARINDEX('.', @version) - 1));
	IF @major > 10 -- > SQL SERVER 2008 R2
		SET @limit_GB = 10.0;
	ELSE IF @major < 10 -- < SQL SERVER 2008
		SET @limit_GB = 4.0;
	ELSE 
	BEGIN
		DECLARE @minor int = CONVERT(int, SUBSTRING(@version, CHARINDEX('.', @version) + 1, CHARINDEX('.', @version, CHARINDEX('.', @version) + 1) - CHARINDEX('.', @version) - 1));
		IF @minor > 5 -- SQL SERVER 2008 R2
			SET @limit_GB = 10.0;
		ELSE -- SQL SERVER 2008
			SET @limit_GB = 4.0;
	END
END
ELSE
	SET @limit_GB = NULL;

DECLARE
	@SqlStatement nvarchar(MAX), @DatabaseName sysname;
	
IF OBJECT_ID(N'tempdb..#DatabaseSpace') IS NOT NULL
	DROP TABLE #DatabaseSpace;
	
CREATE TABLE #DatabaseSpace(
	database_id	int,
	file_id		int,
	spaceused	bigint
	);
	
DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
	SELECT db.name FROM sys.databases db WHERE db.state = 0;
	
OPEN DatabaseList;
WHILE 1 = 1
BEGIN
	FETCH NEXT FROM DatabaseList INTO @DatabaseName;
	IF @@FETCH_STATUS = -1 BREAK;
	SET @SqlStatement = N'USE '	+ QUOTENAME(@DatabaseName) + ';'
		+ CHAR(13) + CHAR(10)
		--+ 'DBCC UPDATEUSAGE(' + QUOTENAME(@DatabaseName) + ');'
		+ CHAR(13) + CHAR(10)
		+ N'INSERT INTO #DatabaseSpace
	SELECT
		[database_id] = DB_ID(),
		[file_id] = f.file_id,
		[spaceused] = CONVERT(int, FILEPROPERTY(f.name,''SpaceUsed''))
	FROM sys.database_files f;';

	EXECUTE(@SqlStatement);
	
END
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

--SELECT * FROM #DatabaseSpace;

SELECT
	db.name, mf.name, 
	CONVERT(bigint, size) * 8192 / 1000000000.0 size_GB, 
	--CASE max_size WHEN -1 THEN NULL ELSE CONVERT(bigint, max_size) * 8192 / 1000000000.0 END max_size_GB, 
	--@limit_GB limit_GB,
	CONVERT(bigint, size) * 8192 / 1000000000.0 * 100.0 / @limit_GB size_Prc,
	ds.spaceused * 8192 / 1000000000.0 used_GB,
	ds.spaceused * 8192 / 1000000000.0 / @limit_GB * 100.0 used_Prc
FROM sys.master_files mf
INNER JOIN sys.databases db ON db.database_id = mf.database_id
INNER JOIN #DatabaseSpace ds ON ds.DATABASE_ID = mf.database_id AND ds.FILE_ID = mf.file_id
ORDER BY size DESC;

DROP TABLE #DatabaseSpace;