SQL Server 2005/2008 Express edition has the limitation of 4 GB per database. As far as I known the database engine considers data only, thus excluding log files, unused space, and index size.
Getting the length of the MDF file should not give the correct database size in terms of SQL Server limitation. My question is how to get the database size?
This question is related to
sql-server
tsql
sp_helpdb
no looping needed, unlike sp_spaceused.
In SQL Management Studio, right-click on a database and select "Properties" from the context menu. Look at the "Size" figure.
You could use this old fashioned one as well...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
DECLARE @iCount int, @iMax int, @DatabaseName varchar(200), @SQL varchar (8000)
Select NAME, DBID, crdate, filename, version
INTO #TEMP
from MAster..SYSDatabASES
SELECT @iCount = Count(DBID) FROM #TEMP
Select @SQL='Create Table ##iFile1 ( DBName varchar( 200) NULL, Fileid INT, FileGroup int, TotalExtents INT , USedExtents INT ,
Name varchar(100), vFile varchar (300), AllocatedSpace int NUll, UsedSpace int Null, PercentageFree int Null ) '+ char(10)
exec (@SQL)
Create Table ##iTotals ( ServerName varchar(100), DBName varchar( 200) NULL, FileType varchar(10),Fileid INT, FileGroup int, TotalExtents INT , USedExtents INT ,
Name varchar(100), vFile varchar (300), AllocatedSpace int NUll, UsedSpace int Null, PercentageFree int Null )
WHILE @iCount>0
BEGIN
SELECT @iMax =Max(dbid) FROM #TEMP
Select @DatabaseName = Name FROM #TEMP where dbid =@iMax
SELECT @SQL = 'INSERT INTO ##iFile1(Fileid , FileGroup , TotalExtents , USedExtents , Name , vFile)
EXEC (''USE [' + @DatabaseName + '] DBCC showfilestats'') ' + char(10)
Print (@SQL)
EXEC (@SQL)
SELECT @SQL = 'UPDATE ##iFile1 SET DBName ='''+ @DatabaseName +''' WHERE DBName IS NULL'
EXEC (@SQL)
DELETE FROM #TEMP WHERE dbid =@iMax
Select @iCount =@iCount -1
END
UPDATE ##iFile1
SET AllocatedSpace = (TotalExtents * 64.0 / 1024.0 ), UsedSpace =(USedExtents * 64.0 / 1024.0 )
UPDATE ##iFile1
SET PercentageFree = 100-Convert(float,UsedSpace)/Convert(float,AllocatedSpace )* 100
WHERE USEDSPACE>0
CREATE TABLE #logspace (
DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int
)
INSERT INTO #logspace
EXEC ('DBCC sqlperf( logspace)')
INSERT INTO ##iTotals(ServerName, DBName, FileType,Name, vFile,PercentageFree,AllocatedSpace)
select @@ServerName ,DBNAME, 'Data' as FileType,Name, vFile, PercentageFree , AllocatedSpace
from ##iFile1
UNION
select @@ServerName ,DBNAME, 'Log' as FileType ,DBName,'' as vFile ,PrcntUsed , LogSize
from #logspace
Select * from ##iTotals
select ServerName ,DBNAME, FileType, Sum( AllocatedSpace) as AllocatedSpaceMB
from ##iTotals
Group By ServerName ,DBNAME, FileType
Order By ServerName ,DBNAME, FileType
select ServerName ,DBNAME, Sum( AllocatedSpace) as AllocatedSpaceMB
from ##iTotals
Group By ServerName ,DBNAME
Order By ServerName ,DBNAME
drop table ##iFile1
drop table #logspace
drop table #TEMP
drop table ##iTotals
Common Query To Check Database Size in SQL Server that supports both Azure and On-Premises-
Method 1 – Using ‘sys.database_files’ System View
SELECT
DB_NAME() AS [database_name],
CONCAT(CAST(SUM(
CAST( (size * 8.0/1024) AS DECIMAL(15,2) )
) AS VARCHAR(20)),' MB') AS [database_size]
FROM sys.database_files;
Method 2 – Using ‘sp_spaceused’ System Stored Procedure
EXEC sp_spaceused ;
According to SQL2000 help, sp_spaceused includes data and indexes.
This script should do:
CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18),
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''
-- SELECT * FROM #t ORDER BY name
-- SELECT name, CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3)) FROM #t ORDER BY name
SELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) FROM #t
DROP TABLE #t
I always liked going after it directly:
SELECT
DB_NAME( dbid ) AS DatabaseName,
CAST( ( SUM( size ) * 8 ) / ( 1024.0 * 1024.0 ) AS decimal( 10, 2 ) ) AS DbSizeGb
FROM
sys.sysaltfiles
GROUP BY
DB_NAME( dbid )
sp_helpdb
no looping needed, unlike sp_spaceused.
In SQL Management Studio, right-click on a database and select "Properties" from the context menu. Look at the "Size" figure.
According to SQL2000 help, sp_spaceused includes data and indexes.
This script should do:
CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18),
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''
-- SELECT * FROM #t ORDER BY name
-- SELECT name, CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3)) FROM #t ORDER BY name
SELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) FROM #t
DROP TABLE #t
The best solution is maybe to calculate the size of each database file, using the sys.sysfiles view, considering a size of 8 KB for each page, as follows:
USE [myDatabase]
GO
SELECT
[size] * 8
, [filename]
FROM sysfiles
The [field] column represents the size of the file, in pages (MSDN Reference to sysfiles).
You would see there will be at least two files (MDF and LDF): the sum of these files will give you the correct size of the entire database...
According to SQL2000 help, sp_spaceused includes data and indexes.
This script should do:
CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18),
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''
-- SELECT * FROM #t ORDER BY name
-- SELECT name, CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3)) FROM #t ORDER BY name
SELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) FROM #t
DROP TABLE #t
In SQL Management Studio, right-click on a database and select "Properties" from the context menu. Look at the "Size" figure.
I always liked going after it directly:
SELECT
DB_NAME( dbid ) AS DatabaseName,
CAST( ( SUM( size ) * 8 ) / ( 1024.0 * 1024.0 ) AS decimal( 10, 2 ) ) AS DbSizeGb
FROM
sys.sysaltfiles
GROUP BY
DB_NAME( dbid )
You could use this old fashioned one as well...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
DECLARE @iCount int, @iMax int, @DatabaseName varchar(200), @SQL varchar (8000)
Select NAME, DBID, crdate, filename, version
INTO #TEMP
from MAster..SYSDatabASES
SELECT @iCount = Count(DBID) FROM #TEMP
Select @SQL='Create Table ##iFile1 ( DBName varchar( 200) NULL, Fileid INT, FileGroup int, TotalExtents INT , USedExtents INT ,
Name varchar(100), vFile varchar (300), AllocatedSpace int NUll, UsedSpace int Null, PercentageFree int Null ) '+ char(10)
exec (@SQL)
Create Table ##iTotals ( ServerName varchar(100), DBName varchar( 200) NULL, FileType varchar(10),Fileid INT, FileGroup int, TotalExtents INT , USedExtents INT ,
Name varchar(100), vFile varchar (300), AllocatedSpace int NUll, UsedSpace int Null, PercentageFree int Null )
WHILE @iCount>0
BEGIN
SELECT @iMax =Max(dbid) FROM #TEMP
Select @DatabaseName = Name FROM #TEMP where dbid =@iMax
SELECT @SQL = 'INSERT INTO ##iFile1(Fileid , FileGroup , TotalExtents , USedExtents , Name , vFile)
EXEC (''USE [' + @DatabaseName + '] DBCC showfilestats'') ' + char(10)
Print (@SQL)
EXEC (@SQL)
SELECT @SQL = 'UPDATE ##iFile1 SET DBName ='''+ @DatabaseName +''' WHERE DBName IS NULL'
EXEC (@SQL)
DELETE FROM #TEMP WHERE dbid =@iMax
Select @iCount =@iCount -1
END
UPDATE ##iFile1
SET AllocatedSpace = (TotalExtents * 64.0 / 1024.0 ), UsedSpace =(USedExtents * 64.0 / 1024.0 )
UPDATE ##iFile1
SET PercentageFree = 100-Convert(float,UsedSpace)/Convert(float,AllocatedSpace )* 100
WHERE USEDSPACE>0
CREATE TABLE #logspace (
DBName varchar( 100),
LogSize float,
PrcntUsed float,
status int
)
INSERT INTO #logspace
EXEC ('DBCC sqlperf( logspace)')
INSERT INTO ##iTotals(ServerName, DBName, FileType,Name, vFile,PercentageFree,AllocatedSpace)
select @@ServerName ,DBNAME, 'Data' as FileType,Name, vFile, PercentageFree , AllocatedSpace
from ##iFile1
UNION
select @@ServerName ,DBNAME, 'Log' as FileType ,DBName,'' as vFile ,PrcntUsed , LogSize
from #logspace
Select * from ##iTotals
select ServerName ,DBNAME, FileType, Sum( AllocatedSpace) as AllocatedSpaceMB
from ##iTotals
Group By ServerName ,DBNAME, FileType
Order By ServerName ,DBNAME, FileType
select ServerName ,DBNAME, Sum( AllocatedSpace) as AllocatedSpaceMB
from ##iTotals
Group By ServerName ,DBNAME
Order By ServerName ,DBNAME
drop table ##iFile1
drop table #logspace
drop table #TEMP
drop table ##iTotals
According to SQL2000 help, sp_spaceused includes data and indexes.
This script should do:
CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18),
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''
-- SELECT * FROM #t ORDER BY name
-- SELECT name, CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3)) FROM #t ORDER BY name
SELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) FROM #t
DROP TABLE #t
In SQL Management Studio, right-click on a database and select "Properties" from the context menu. Look at the "Size" figure.
The best solution is maybe to calculate the size of each database file, using the sys.sysfiles view, considering a size of 8 KB for each page, as follows:
USE [myDatabase]
GO
SELECT
[size] * 8
, [filename]
FROM sysfiles
The [field] column represents the size of the file, in pages (MSDN Reference to sysfiles).
You would see there will be at least two files (MDF and LDF): the sum of these files will give you the correct size of the entire database...
Common Query To Check Database Size in SQL Server that supports both Azure and On-Premises-
Method 1 – Using ‘sys.database_files’ System View
SELECT
DB_NAME() AS [database_name],
CONCAT(CAST(SUM(
CAST( (size * 8.0/1024) AS DECIMAL(15,2) )
) AS VARCHAR(20)),' MB') AS [database_size]
FROM sys.database_files;
Method 2 – Using ‘sp_spaceused’ System Stored Procedure
EXEC sp_spaceused ;
Source: Stackoverflow.com