Select SQL Server database size
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
Get actual database size in SQL Server 2012 in GB?
You need to divide by 1024 again.
select d.name, m.size * 8 / 1024 / 1024
from sys.master_files m JOIN sys.databases d ON d.database_id = m.database_id and m.type =0
However this will round to the nearest whole GB (i.e. integer) You will need to cast as a (numeric, float, decimal, double, etc.)
If you run:
SELECT physical_name, size * 8 / 1024 / 1024 FROM sys.database_files WHERE TYPE = 0
That will give you the information for the database you are connected to, not all databases on the instance.
How to get database size in SQL Server Enterprise edition
instead of MF.size * 8
use MF.size * 8.0
SQL Server 2008: How to query all databases sizes?
with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
SQL Server: how can I get the correct DB size from sys.master_files?
If you're just looking to convert the size column to match the values in the properties, you just need to convert from pages to MB - so multiply by 8 to get KB and then divide by 1024 to get MB.
WITH lastrestores AS
(
SELECT
DatabaseName = [d].[name],
[r].[restore_date],
[size] = CAST([f].[size] * 8 / 1024.0 AS DECIMAL(10,2)) ,
[r].[user_name],
RowNum = ROW_NUMBER() OVER (PARTITION BY d.NAME ORDER BY r.[restore_date] DESC)
FROM
master.sys.databases d
LEFT OUTER JOIN
msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.NAME
LEFT JOIN
master.sys.master_files f ON d.database_id = f.database_id
)
SELECT *
FROM [lastrestores]
WHERE [rownum] = 1
ORDER BY restore_date DESC
This Size value here could be slightly more accurate than what you see in properties because SSMS rounds up.
My concern would be that if you have more than one data file, this won't accurately display the non-log size of your database.
I'd probably do something like this to include all the data files.
SELECT
DatabaseName = DB_NAME(f.database_id)
,r.restore_date
,CAST(SUM(f.size * 8 / 1024.0) AS DECIMAL(10,2))
,r.user_name
FROM sys.master_files f
OUTER APPLY
(SELECT TOP 1 * FROM msdb.dbo.[restorehistory] r WHERE r.[destination_database_name] = DB_NAME(f.database_id) ORDER BY restore_date desc) r
WHERE f.type = 0
GROUP BY f.database_id, r.restore_date, r.user_name
ORDER BY r.restore_date desc
Determine SQL Server Database Size
sp_spaceused
Can you query the maximum database size based on the SQL Server license?
Try this one -
SELECT
d.server_name
, d.sversion_name
, d.edition
, max_db_size_in_gb =
CASE WHEN engine_edition = 4
THEN
CASE
WHEN d.sversion_name LIKE '%2012%' THEN 10
WHEN d.sversion_name LIKE '%2008 R2%' THEN 10
WHEN d.sversion_name LIKE '%2008%' THEN 4
WHEN d.sversion_name LIKE '%2005%' THEN 4
END
ELSE -1
END
FROM (
SELECT
sversion_name = SUBSTRING(v.ver, 0, CHARINDEX('-', v.ver) - 1)
, engine_edition = SERVERPROPERTY('EngineEdition')
, edition = SERVERPROPERTY('Edition')
, server_name = SERVERPROPERTY('ServerName')
FROM (SELECT ver = @@VERSION) v
) d
Also, try this query to get extended info about DB -
IF EXISTS(
SELECT 1
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID('tempdb.dbo.#DBObj')
) DROP TABLE #DBObj
CREATE TABLE #DBObj
(
[DB] SYSNAME
, reservedpages INT
, usedpages INT
, pages INT
)
EXEC sys.sp_MSforeachdb '
USE [?]
INSERT INTO #DBObj
(
[DB]
, reservedpages
, usedpages
, pages
)
SELECT
DB_NAME()
, pg.reservedpages
, pg.usedpages
, pg.pages
FROM (
SELECT
reservedpages = SUM(a.total_pages)
, usedpages = SUM(a.used_pages)
, pages = SUM(
CASE
WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0
WHEN a.[type] != 1 AND p.index_id < 2 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages ELSE 0
END
)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id]
) pg'
SELECT
d.name
, total_size_mb = data.row_size_mb + data.log_size_mb
, data.log_size_mb
, data.row_size_mb
, reserved_space_mb = CAST(do.reservedpages * 8. / 1024 AS DECIMAL(10,2))
, data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(10,2))
, index_size_mb = CAST((do.usedpages - do.pages) * 8. / 1024 AS DECIMAL(10,2))
, unused_size_mb = CAST((do.reservedpages - do.usedpages) * 8. / 1024 AS DECIMAL(10,2))
, unallocated_space_mb =
CAST(CASE WHEN data.row_size >= do.reservedpages
THEN (data.row_size - do.reservedpages) * 8. / 1024
ELSE 0
END AS DECIMAL(10,2))
FROM (
SELECT
df2.database_id
, log_size_mb = CAST(df2.log_size * 8. / 1024 AS DECIMAL(10,2))
, row_size_mb = CAST(df2.row_size * 8. / 1024 AS DECIMAL(10,2))
, df2.log_size
, df2.row_size
FROM (
SELECT
df.database_id
, log_size = SUM(CASE WHEN df.type_desc = 'LOG' THEN df.size END)
, row_size = SUM(CASE WHEN df.type_desc = 'ROWS' THEN df.size END)
FROM sys.master_files df
GROUP BY df.database_id
) df2
) data
JOIN sys.databases d ON data.database_id = d.database_id
JOIN #DBObj do ON do.DB = d.name
ORDER BY data.row_size_mb + data.log_size_mb DESC
Get size of all tables in database
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name
Related Topics
Selecting Top N Rows for Each Group in a Table
How to Convert Postgresql 9.4's JSONb Type to Float
How to Create a Pivot Query in SQL Server Without Aggregate Function
Is There a Difference Between Select * and Select [List Each Col]
Table or Column Name Cannot Start with Numeric
When to Use Except as Opposed to Not Exists in Transact SQL
Sql: How to Select Only Groups That Do Not Contain a Certain Value
How to Remove Extended Ascii Characters from a String in T-Sql
Why Does Varchar Need Length Specification
Split String and Take Last Element
Selecting All Corresponding Fields Using Max and Group By
How to Separate String into Different Columns
Sql: Order by Using a Substring Within a Specific Column... Possible
SQL Query That Gives Distinct Results That Match Multiple Columns
Aggregating (X,Y) Coordinate Point Clouds in Postgresql