how can i query my sql server to only get the size of database?
I used this :
use "MY_DB"
exec sp_spaceused
I got this :
database_name database_size unallocated space
My_DB 17899.13 MB 5309.39 MB
It returns me several column that i don't need, maybe there is a trick to select database_size column from this stored procedure ?
I also tried this code :
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) = 'MY_DB'
It gives me this result:
DatabaseName Logical_Name Physical_Name SizeMB
MY_DB MY_DB D:\MSSQL\Data\MY_DB.mdf 10613
MY_DB MY_DB_log D:\MSSQL\Data\MY_DB.ldf 7286
So i wrote this:
SELECT SUM(SizeMB)
FROM (
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) = 'MY_DB'
) AS TEMP
I got: 1183
So it works but maybe there is a proper way to get this?
This question is related to
sql
sql-server
sql-server-2008
sql-server-2008-r2
The answer is
You can check how this query works following this link.
IF OBJECT_ID('tempdb..#spacetable') IS NOT NULL
DROP TABLE tempdb..#spacetable
create table #spacetable
(
database_name varchar(50) ,
total_size_data int,
space_util_data int,
space_data_left int,
percent_fill_data float,
total_size_data_log int,
space_util_log int,
space_log_left int,
percent_fill_log char(50),
[total db size] int,
[total size used] int,
[total size left] int
)
insert into #spacetable
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
select x.[DATABASE NAME],x.[total size data],x.[space util],x.[total size data]-x.[space util] [space left data],
x.[percent fill],y.[total size log],y.[space util],
y.[total size log]-y.[space util] [space left log],y.[percent fill],
y.[total size log]+x.[total size data] ''total db size''
,x.[space util]+y.[space util] ''total size used'',
(y.[total size log]+x.[total size data])-(y.[space util]+x.[space util]) ''total size left''
from (select DB_NAME() ''DATABASE NAME'',
sum(size*8/1024) ''total size data'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
,case when sum(size*8/1024)=0 then ''divide by zero'' else
substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
from sys.master_files where database_id=DB_ID(DB_NAME()) and type=0
group by type_desc ) as x ,
(select
sum(size*8/1024) ''total size log'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
,case when sum(size*8/1024)=0 then ''divide by zero'' else
substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
from sys.master_files where database_id=DB_ID(DB_NAME()) and type=1
group by type_desc )y'
select * from #spacetable
order by database_name
drop table #spacetable
This query generates size for both log and data in MB as well as GB
SELECT X.database_name,
X.log_size_mb,
X.log_size_mb / 1024 AS log_size_gb,
X.row_size_mb,
X.row_size_mb / 1024 AS row_size_gb,
X.total_size_mb,
X.total_size_mb / 1024 AS total_size_gb
FROM (SELECT database_name = DB_NAME(database_id),
log_size_mb = CAST(SUM(CASE
WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8, 2)),
row_size_mb = CAST(SUM(CASE
WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8, 2)),
total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8, 2))
FROM sys.master_files WITH (NOWAIT)
WHERE database_id = DB_ID() -- current db by default
GROUP BY database_id) AS X
There are already a lot of great answers here but it's worth mentioning a simple and quick way to get the SQL Server Database size with SQL Server Management Studio (SSMS) using a standard report.
To run a report you need:
- right-click on the database
- go to Reports > Standard Reports > Disk Usage.
It prints a nice report:
Where the Total space Reserved is the total size of the database on the disk and it includes the size of all data files and the size of all transaction log files.
Under the hood, SSMS uses dbo.sysfiles view or sys.database_files view (depending on the version of MSSQL) and some kind of this query to get the Total space Reserved value:
SELECT sum((convert(dec (19, 2),
convert(bigint,SIZE))) * 8192 / 1048576.0) db_size_mb
FROM dbo.sysfiles;
EXEC sp_spaceused @oneresultset = 1
show in 1 row all of the result
if you execute just 'EXEC sp_spaceused' you will see two rows Work in SQL Server Management Studio v17.9
If you want to simply check single database size, you can do it using SSMS Gui
Go to Server Explorer -> Expand it -> Right click on Database -> Choose Properties -> In popup window choose General tab ->See Size
Source: Check database size in Sql server ( Various Ways explained)
Check Database Size in SQL Server for 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 ;
SELECT
DB_NAME (database_id) as [Database Name],
name as [Database File Name],
[Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END,
size*8/1024 as 'Size (MB)',
physical_name as [Database_File_Location]
FROM sys.master_files
ORDER BY 1,3
Output
Database Name Database File Name Type Size (MB) Database_File_Location
--------------------------- ------------------------------- ------------------- ----------- ---------------------------------------------------------------
AdventureWorksDW2017 AdventureWorksDW2017 Data File(s) 136 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2017.mdf
AdventureWorksDW2017 AdventureWorksDW2017_log Log File(s) 72 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2017_log.ldf
DBA_Admin DBA_Admin Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\DBA_Admin.mdf
DBA_Admin DBA_Admin_log Log File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\DBA_Admin_log.ldf
EventNotifications EventNotifications Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\EventNotifications.mdf
EventNotifications EventNotifications_log Log File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\EventNotifications_log.ldf
master master Data File(s) 4 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
master mastlog Log File(s) 2 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
model modeldev Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\model.mdf
model modellog Log File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\modellog.ldf
msdb MSDBData Data File(s) 19 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf
msdb MSDBLog Log File(s) 13 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf
tempdb temp2 Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_2.ndf
tempdb temp3 Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_3.ndf
tempdb temp4 Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb_mssql_4.ndf
tempdb tempdev Data File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
tempdb templog Log File(s) 8 E:\MSSQL14.MSSQLSERVER\MSSQL\DATA\templog.ldf
Also compare the results with the following query's result
EXEC sp_helpdb @dbname= 'MSDB'
It produces result similar to the following
There is a good article - Different ways to determine free space for SQL Server databases and database files
Worked perfectly for me to calculate SQL database size in SQL Server 2012
exec sp_spaceused
SELECT sys.databases.name AS [Database Name],
CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' AS [Size]
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY sys.databases.name
Source: Stackoverflow.com