试试这个-
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
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
602392714
清零编程群