原 MSSQL查询数据库大小
Tags： 原创MSSQLSQL Server脚本小麦苗常用数据文件数据库大小MSSQL2000真实大小
👉 本文共约251个字，系统预计阅读时间或需1分钟。
查询真实大小（非数据文件分配大小）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | -- 当前数据库真实大小 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版本：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | -- 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 , case when a.name='tempdb' then (SELECT SUM(size)/128 FROM tempdb.sys.database_files where type=0) else (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) end AS '数据文件大小(MB)', case when a.name='tempdb' then (SELECT SUM(size)/128 FROM tempdb.sys.database_files where type=1) else (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) end AS '日志大小(MB)', case when a.name='tempdb' then (SELECT SUM(size)/128 FROM tempdb.sys.database_files) else (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) end 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 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数据库文件大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | -- 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; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
