查询真实大小（非数据文件分配大小）
1-- 当前数据库真实大小
2SELECT CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
3 CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
4 CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
5FROM sys.tables t
6INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
7INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
8INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
9LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
10;
11
12
13
14/***********************************************************/
15/***************************** 数据库真实大小 **********************/
16/***********************************************************/
17DECLARE @database_name VARCHAR(50)
18DECLARE @SQL_STR VARCHAR(2000)
19
20IF OBJECT_ID('tempdb..#TB_DB_SIZES') IS NOT NULL
21DROP TABLE #TB_DB_SIZES
22CREATE TABLE #TB_DB_SIZES(db_name nvarchar(200),totalspacemb numeric(36, 2),usedspacemb numeric(36, 2),unusedspacemb numeric(36, 2))
23
24DECLARE DATEBASE_INFO_CURSOR CURSOR FOR
25SELECT name FROM sys.databases
26where state=0
27-- where name not in ('master','model','msdb','tempdb')
28-- and state=0
29ORDER BY Name
30
31
32OPEN DATEBASE_INFO_CURSOR
33FETCH NEXT FROM DATEBASE_INFO_CURSOR INTO @database_name
34WHILE @@FETCH_STATUS=0
35BEGIN
36
37 SET @SQL_STR='INSERT INTO #TB_DB_SIZES
38SELECT '''+@database_name+''',
39 CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS totalspacemb,
40 CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS usedspacemb,
41 CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS unusedspacemb
42FROM ['+@database_name+'].sys.tables t
43INNER JOIN ['+@database_name+'].sys.indexes i ON t.OBJECT_ID = i.object_id
44INNER JOIN ['+@database_name+'].sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
45INNER JOIN ['+@database_name+'].sys.allocation_units a ON p.partition_id = a.container_id
46LEFT OUTER JOIN ['+@database_name+'].sys.schemas s ON t.schema_id = s.schema_id
47'
48
49 -- print (@SQL_STR)
50 EXEC (@SQL_STR)
51 FETCH NEXT FROM DATEBASE_INFO_CURSOR INTO @database_name
52
53END
54CLOSE DATEBASE_INFO_CURSOR
55DEALLOCATE DATEBASE_INFO_CURSOR
56
57
58select * from #TB_DB_SIZES order by totalspacemb desc ;
SQL Server查询数据库文件分配大小
以下脚本可以用于SQL Server 2005版本：
1-- exec sp_helpdb; -- 数据库大小包含日志，sql2000可用
2-- dbcc sqlperf(logspace); -- 查询日志大小
3
4SELECT cast(A.database_id as varchar(10)) AS database_id,
5 a.name AS name,
6 convert(varchar(20),a.create_date,120) AS create_date,
7 a.recovery_model_desc AS recovery_model_desc,
8 ISNULL(a.collation_name,' ') AS collation_name,
9 a.user_access_desc AS user_access_desc,
10 a.state_desc AS state_desc,
11 a.is_auto_create_stats_on AS is_auto_create_stats_on,
12 a.is_auto_update_stats_on AS is_auto_update_stats_on,
13 a.is_auto_close_on AS is_auto_close_on,
14 a.is_auto_shrink_on AS is_auto_shrink_on,
15 a.is_auto_update_stats_async_on AS is_auto_update_stats_async_on,
16 a.compatibility_level AS compatibility_level,
17 a.log_reuse_wait_desc AS log_reuse_wait_desc,
18 a.page_verify_option_desc AS page_verify_option_desc,
19 a.is_cdc_enabled as is_cdc_enabled,
20 (SELECT 'is_replication' =
21 CASE
22 WHEN b.category = 1 THEN 'Published'
23 WHEN b.category = 2 THEN 'subscribed'
24 WHEN b.category = 4 THEN 'Merge published'
25 WHEN b.category = 8 THEN 'merge subscribed'
26 Else 'NO replication'
27 END) AS is_replication,
28 ISNULL(c.mirroring_state,' ') as mirroring_state ,
29 (select cast(round(sum(size), 2) as numeric(15, 2))
30 from (select database_id, type, size * 8.0 / 1024 size from sys.master_files) fs
31 where type = 0
32 and fs.database_id = a.database_id) AS '数据文件大小(MB)',
33 (select cast(round(sum(size), 2) as numeric(15, 2))
34 from (select database_id, type, size * 8.0 / 1024 size from sys.master_files) fs
35 where type = 1
36 and fs.database_id = a.database_id) AS '日志大小(MB)',
37 (select cast(round(sum(size), 2) as numeric(15, 2))
38 from (select database_id, type, size * 8.0 / 1024 size from sys.master_files) fs
39 where fs.database_id = a.database_id) AS '数据库大小(MB)' -- Exec sp_spaceused
40 from sys.databases as a
41 LEFT JOIN sys.sysdatabases b
42 ON a.database_id=b.dbid
43 LEFT JOIN sys.database_mirroring c
44 ON a.database_id=c.database_id
45 where a.name!='tempdb'
46 UNION ALL
47SELECT '总计','','','','','','','','','','','','','','','','','',
48(select cast(round(sum(size * 8.0 / 1024), 2) as numeric(15, 2)) size from sys.master_files WHERE type=0) ,
49(select cast(round(sum(size * 8.0 / 1024), 2) as numeric(15, 2)) size from sys.master_files WHERE type=1) ,
50(select cast(round(sum(size * 8.0 / 1024), 2) as numeric(15, 2)) size from sys.master_files)
51;
52
53go
54
55
56
57C:\Users\Administrator>sqlcmd -S localhost -U SA
58密码:
59
6056> go
61database_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)
62----------- ---------------------- -------------------- ----------------------- ---------------------------- ------------ ----------------------- ----------------------- ---------------- ----------------- ----------------------------- ------------------- ------------------------------------------------------------ ------------------------------------------------------------ -------------- ---------------- --------------- ----------------- ----------------- -----------------
631 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
643 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
654 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
665 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
676 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
687 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
698 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
70总计 0 0 0 0 0 0 0 0 982.50 1833.63 2816.13
71
72(8 行受影响)
MSSQL 2000数据库文件大小
1-- Exec sp_spaceused; -- 单个库的大小 ，MSSQL 2000可用
2-- exec sp_helpdb; -- 数据库大小包含日志，MSSQL 2000可用
3-- MSSQL 2000 查看所有数据库大小、恢复模式等信息
4SELECT Q1.DBID,DatabaseName AS DatabaseName,Q3.CRDATE,
5 DataSize DataSize_MB,
6 LogSize LogSize_MB,
7 DataSize + LogSize AS TotalSize_MB,
8 Collation,
9 RecoveryType,
10 AutoClose,
11 AutoShrink,
12 CMPTLEVEL,
13 FILENAME
14FROM (SELECT DBID,
15 CASE SUM(size * 8 / 1024) WHEN 0 THEN 1 ELSE SUM(size * 8 / 1024) END AS DataSize
16 FROM master..sysaltfiles
17 WHERE GroupID <> 0
18 GROUP BY DBID) q1
19INNER JOIN (SELECT DBID,
20 CASE SUM(size * 8 / 1024) WHEN 0 THEN 1 ELSE SUM(size * 8 / 1024) END AS LogSize
21 FROM master..sysaltfiles
22 WHERE GroupID = 0
23 GROUP BY DBID) q2
24ON q1.DBID = q2.DBID
25INNER JOIN (SELECT DBID,
26 [NAME] AS DatabaseName,
27 CONVERT(VARCHAR(100), DATABASEPROPERTYEX([NAME], 'Collation')) AS Collation,
28 CONVERT(VARCHAR(100), DATABASEPROPERTYEX([NAME], 'Recovery')) AS RecoveryType,
29 CASE CONVERT(VARCHAR(10), DATABASEPROPERTYEX([NAME], 'IsAutoClose'))
30 WHEN 0 THEN '-'
31 WHEN 1 THEN 'Yes'
32 END AS AutoClose,
33 CASE
34 CONVERT(VARCHAR(10), DATABASEPROPERTYEX([NAME], 'IsAutoShrink'))
35 WHEN 0 THEN '-'
36 WHEN 1 THEN 'Yes'
37 END AS AutoShrink,
38 NB.CRDATE,
39 NB.CMPTLEVEL,
40 NB.FILENAME
41 FROM master.dbo.sysdatabases NB ) q3
42ON q1.DBID = q3.dbid
43ORDER BY q1.DBID;
MSSQL 2000数据文件
1-- MSSQL 2000数据文件
2USE lhrdb;
3SELECT NAME, size FROM sysfiles;
4
5SELECT dbid, DB_NAME(dbid) dbname,fileid,groupid,size,maxsize,growth,status,filename
6FROM master..sysaltfiles AS mf WITH (NOLOCK)
7;
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
