Select SQL Server database size

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?

Try this one -

**Query:**


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() -- for current db
GROUP BY database_id

**Output:**

-- my query
name log_size_mb row_size_mb total_size_mb
-------------- ------------ ------------- -------------
xxxxxxxxxxx 512.00 302.81 814.81

-- sp_spaceused
database_name database_size unallocated space
---------------- ------------------ ------------------
xxxxxxxxxxx 814.81 MB 13.04 MB

**Function:**

ALTER FUNCTION [dbo].[GetDBSize]
(
@db_name NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN

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(@db_name)
OR @db_name IS NULL
GROUP BY database_id

**UPDATE 2016/01/22:**

Show information about size, free space, last database backups

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
DROP TABLE #space

CREATE TABLE #space (
database_id INT PRIMARY KEY
, data_used_size DECIMAL(18,2)
, log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
SELECT '
USE [' + d.name + ']
INSERT INTO #space (database_id, data_used_size, log_used_size)
SELECT
DB_ID()
, SUM(CASE WHEN [type] = 0 THEN space_used END)
, SUM(CASE WHEN [type] = 1 THEN space_used END)
FROM (
SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
FROM sys.database_files s
GROUP BY s.[type]
) t;'
FROM sys.databases d
WHERE d.[state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT
d.database_id
, d.name
, d.state_desc
, d.recovery_model_desc
, t.total_size
, t.data_size
, s.data_used_size
, t.log_size
, s.log_used_size
, bu.full_last_date
, bu.full_size
, bu.log_last_date
, bu.log_size
FROM (
SELECT
database_id
, log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
FROM sys.master_files
GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
SELECT
database_name
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_finish_date
, backup_size =
CAST(CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0 AS DECIMAL(18,2))
, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC

**Output:**

database_id name state_desc recovery_model_desc total_size data_size data_used_size log_size log_used_size full_last_date full_size log_last_date log_size
----------- -------------------------------- ------------ ------------------- ------------ ----------- --------------- ----------- -------------- ----------------------- ------------ ----------------------- ---------
24 StackOverflow ONLINE SIMPLE 66339.88 65840.00 65102.06 499.88 5.05 NULL NULL NULL NULL
11 AdventureWorks2012 ONLINE SIMPLE 16404.13 15213.00 192.69 1191.13 15.55 2015-11-10 10:51:02.000 44.59 NULL NULL
10 locateme ONLINE SIMPLE 1050.13 591.00 2.94 459.13 6.91 2015-11-06 15:08:34.000 17.25 NULL NULL
8 CL_Documents ONLINE FULL 793.13 334.00 333.69 459.13 12.95 2015-11-06 15:08:31.000 309.22 2015-11-06 13:15:39.000 0.01
1 master ONLINE SIMPLE 554.00 492.06 4.31 61.94 5.20 2015-11-06 15:08:12.000 0.65 NULL NULL
9 Refactoring ONLINE SIMPLE 494.32 366.44 308.88 127.88 34.96 2016-01-05 18:59:10.000 37.53 NULL NULL
3 model ONLINE SIMPLE 349.06 4.06 2.56 345.00 0.97 2015-11-06 15:08:12.000 0.45 NULL NULL
13 sql-format.com ONLINE SIMPLE 216.81 181.38 149.00 35.44 3.06 2015-11-06 15:08:39.000 23.64 NULL NULL
23 users ONLINE FULL 173.25 73.25 3.25 100.00 5.66 2015-11-23 13:15:45.000 0.72 NULL NULL
4 msdb ONLINE SIMPLE 46.44 20.25 19.31 26.19 4.09 2015-11-06 15:08:12.000 2.96 NULL NULL
21 SSISDB ONLINE FULL 45.06 40.00 4.06 5.06 4.84 2014-05-14 18:27:11.000 3.08 NULL NULL
27 tSQLt ONLINE SIMPLE 9.00 5.00 3.06 4.00 0.75 NULL NULL NULL NULL
2 tempdb ONLINE SIMPLE 8.50 8.00 4.50 0.50 1.78 NULL NULL NULL NULL

Worked perfectly for me to calculate **SQL database size** in **SQL Server 2012**

exec sp_spaceused
[![enter image description here][1]][1]


[1]:

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

`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

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

<b>Output</b>
```
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
```

***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;

[![enter image description here][1]][1]

**Method 2 – Using ‘sp_spaceused’ System Stored Procedure**

EXEC sp_spaceused ;

[![enter image description here][2]][2]


[1]:

[2]:

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:

1. right-click on the database
2. go to Reports > Standard Reports > Disk Usage.

It prints a nice report:

[![enter image description here][1]][1]


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][2] view or [sys.database_files][3] 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;


[1]:

[2]:

[To see links please register here]

[3]:

[To see links please register here]

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)][1]


[1]:

[To see links please register here]

try below code. its very good code:

with CteDbSizes
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
dbFileSizes.[name] AS DatabaseName,
(select sum(size) from CteDbSizes where type = 1 and CteDbSizes.database_id = dbFileSizes.database_id) LogFileSizeMB,
(select sum(size) from CteDbSizes where type = 0 and CteDbSizes.database_id = dbFileSizes.database_id) DataFileSizeMB
from sys.databases dbFileSizes ORDER BY DataFileSizeMB desc

[![Result of the above query][1]][1]


[1]: