SQL Server查询数据库文件分配大小和数据库真实大小的脚本分享
SQL Server查询数据库文件分配大小和数据库真实大小的脚本分享
AiDBA宝典
发布于 2023-04-27 13:34:23
发布于 2023-04-27 13:34:23
文章被收录于专栏：小麦苗的DB宝专栏
查询真实大小（非数据文件分配大小）
-- 当前数据库真实大小
SELECT CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
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
;
/***********************************************************/
/***************************** 数据库真实大小 **********************/
/***********************************************************/
DECLARE @database_name VARCHAR(50)
DECLARE @SQL_STR VARCHAR(2000)
IF OBJECT_ID('tempdb..#TB_DB_SIZES') IS NOT NULL
DROP TABLE #TB_DB_SIZES
CREATE TABLE #TB_DB_SIZES(db_name nvarchar(200),totalspacemb numeric(36, 2),usedspacemb numeric(36, 2),unusedspacemb numeric(36, 2))
DECLARE DATEBASE_INFO_CURSOR CURSOR FOR
SELECT name FROM sys.databases
where state=0
-- where name not in ('master','model','msdb','tempdb')
-- and state=0
ORDER BY Name
OPEN DATEBASE_INFO_CURSOR
FETCH NEXT FROM DATEBASE_INFO_CURSOR INTO @database_name
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL_STR='INSERT INTO #TB_DB_SIZES
SELECT '''+@database_name+''',
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS totalspacemb,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS usedspacemb,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS unusedspacemb
FROM ['+@database_name+'].sys.tables t
INNER JOIN ['+@database_name+'].sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN ['+@database_name+'].sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN ['+@database_name+'].sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN ['+@database_name+'].sys.schemas s ON t.schema_id = s.schema_id
'
-- print (@SQL_STR)
EXEC (@SQL_STR)
FETCH NEXT FROM DATEBASE_INFO_CURSOR INTO @database_name
END
CLOSE DATEBASE_INFO_CURSOR
DEALLOCATE DATEBASE_INFO_CURSOR
select * from #TB_DB_SIZES order by totalspacemb desc ;
SQL Server查询数据库文件分配大小
以下脚本可以用于SQL Server 2005版本：
-- exec sp_helpdb; -- 数据库大小包含日志，sql2000可用
-- dbcc sqlperf(logspace); -- 查询日志大小
SELECT cast(A.database_id as varchar(10)) AS database_id,
a.name AS name,
convert(varchar(20),a.create_date,120) AS create_date,
a.recovery_model_desc AS recovery_model_desc,
ISNULL(a.collation_name,' ') AS collation_name,
a.user_access_desc AS user_access_desc,
a.state_desc AS state_desc,
a.is_auto_create_stats_on AS is_auto_create_stats_on,
a.is_auto_update_stats_on AS is_auto_update_stats_on,
a.is_auto_close_on AS is_auto_close_on,
a.is_auto_shrink_on AS is_auto_shrink_on,
a.is_auto_update_stats_async_on AS is_auto_update_stats_async_on,
a.compatibility_level AS compatibility_level,
a.log_reuse_wait_desc AS log_reuse_wait_desc,
a.page_verify_option_desc AS page_verify_option_desc,
a.is_cdc_enabled as is_cdc_enabled,
(SELECT 'is_replication' =
CASE
WHEN b.category = 1 THEN 'Published'
WHEN b.category = 2 THEN 'subscribed'
WHEN b.category = 4 THEN 'Merge published'
WHEN b.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS is_replication,
ISNULL(c.mirroring_state,' ') as mirroring_state ,
(select cast(round(sum(size), 2) as numeric(15, 2))
from (select database_id, type, size * 8.0 / 1024 size from sys.master_files) fs
where type = 0
and fs.database_id = a.database_id) AS '数据文件大小(MB)',
(select cast(round(sum(size), 2) as numeric(15, 2))
from (select database_id, type, size * 8.0 / 1024 size from sys.master_files) fs
where type = 1
and fs.database_id = a.database_id) AS '日志大小(MB)',
(select cast(round(sum(size), 2) as numeric(15, 2))
from (select database_id, type, size * 8.0 / 1024 size from sys.master_files) fs
where fs.database_id = a.database_id) AS '数据库大小(MB)' -- Exec sp_spaceused
from sys.databases as a
LEFT JOIN sys.sysdatabases b
ON a.database_id=b.dbid
LEFT JOIN sys.database_mirroring c
ON a.database_id=c.database_id
where a.name!='tempdb'
UNION ALL
SELECT '总计','','','','','','','','','','','','','','','','','',
(select cast(round(sum(size * 8.0 / 1024), 2) as numeric(15, 2)) size from sys.master_files WHERE type=0) ,
(select cast(round(sum(size * 8.0 / 1024), 2) as numeric(15, 2)) size from sys.master_files WHERE type=1) ,
(select cast(round(sum(size * 8.0 / 1024), 2) as numeric(15, 2)) size from sys.master_files)
;
go
C:\Users\Administrator>sqlcmd -S localhost -U SA
密码:
56> go
database_id name create_date recovery_model_desc collation_name state_desc is_auto_create_stats_on is_auto_update_stats_on is_auto_close_on is_auto_shrink_on is_auto_update_stats_async_on compatibility_level log_reuse_wait_desc page_verify_option_desc is_cdc_enabled is_replication mirroring_state 数据文件大小(MB) 日志大小(MB) 数据库大小(MB)
----------- ---------------------- -------------------- ----------------------- ---------------------------- ------------ ----------------------- ----------------------- ---------------- ----------------- ----------------------------- ------------------- ------------------------------------------------------------ ------------------------------------------------------------ -------------- ---------------- --------------- ----------------- ----------------- -----------------
1 master 2003-04-08 09:13:36 SIMPLE Chinese_PRC_CI_AS ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 NO replication 0 4.00 1.25 5.25
3 model 2003-04-08 09:13:36 FULL Chinese_PRC_CI_AS ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 NO replication 0 2.25 .75 3.00
4 msdb 2010-04-02 17:35:08 SIMPLE Chinese_PRC_CI_AS ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 NO replication 0 14.75 3.06 17.81
5 ReportServer 2020-08-21 14:39:51 FULL Latin1_General_CI_AS_KS_WS ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 NO replication 0 4.25 6.25 10.50
6 ReportServerTempDB 2020-08-21 14:39:53 SIMPLE Latin1_General_CI_AS_KS_WS ONLINE 1 1 0 0 0 100 CHECKPOINT CHECKSUM 0 NO replication 0 2.25 .81 3.06
7 lhrdb 2020-11-27 17:23:22 FULL Chinese_PRC_CI_AS ONLINE 1 1 0 0 0 100 LOG_BACKUP CHECKSUM 1 NO replication 0 301.00 1585.44 1886.44
8 tpcc 2020-12-17 18:17:20 FULL Chinese_PRC_CI_AS ONLINE 1 1 0 0 0 100 NOTHING CHECKSUM 0 NO replication 0 646.00 235.56 881.56
总计 0 0 0 0 0 0 0 0 982.50 1833.63 2816.13
(8 行受影响)
MSSQL 2000数据库文件大小
-- Exec sp_spaceused; -- 单个库的大小 ，MSSQL 2000可用
-- exec sp_helpdb; -- 数据库大小包含日志，MSSQL 2000可用
-- MSSQL 2000 查看所有数据库大小、恢复模式等信息
SELECT Q1.DBID,DatabaseName AS DatabaseName,Q3.CRDATE,
DataSize DataSize_MB,
LogSize LogSize_MB,
DataSize + LogSize AS TotalSize_MB,
Collation,
RecoveryType,
AutoClose,
AutoShrink,
CMPTLEVEL,
FILENAME
FROM (SELECT DBID,
CASE SUM(size * 8 / 1024) WHEN 0 THEN 1 ELSE SUM(size * 8 / 1024) END AS DataSize
FROM master..sysaltfiles
WHERE GroupID <> 0
GROUP BY DBID) q1
INNER JOIN (SELECT DBID,
CASE SUM(size * 8 / 1024) WHEN 0 THEN 1 ELSE SUM(size * 8 / 1024) END AS LogSize
FROM master..sysaltfiles
WHERE GroupID = 0
GROUP BY DBID) q2
ON q1.DBID = q2.DBID
INNER JOIN (SELECT DBID,
[NAME] AS DatabaseName,
CONVERT(VARCHAR(100), DATABASEPROPERTYEX([NAME], 'Collation')) AS Collation,
CONVERT(VARCHAR(100), DATABASEPROPERTYEX([NAME], 'Recovery')) AS RecoveryType,
CASE CONVERT(VARCHAR(10), DATABASEPROPERTYEX([NAME], 'IsAutoClose'))
WHEN 0 THEN '-'
WHEN 1 THEN 'Yes'
END AS AutoClose,
CASE
CONVERT(VARCHAR(10), DATABASEPROPERTYEX([NAME], 'IsAutoShrink'))
WHEN 0 THEN '-'
WHEN 1 THEN 'Yes'
END AS AutoShrink,
NB.CRDATE,
NB.CMPTLEVEL,
NB.FILENAME
FROM master.dbo.sysdatabases NB ) q3
ON q1.DBID = q3.dbid
ORDER BY q1.DBID;
MSSQL 2000数据文件
-- MSSQL 2000数据文件
USE lhrdb;
SELECT NAME, size FROM sysfiles;
SELECT dbid, DB_NAME(dbid) dbname,fileid,groupid,size,maxsize,growth,status,filename
FROM master..sysaltfiles AS mf WITH (NOLOCK)
;
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2023-02-26，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
