分析数据库占用大空间的记录

最新推荐文章于 2020-02-13 20:50:36 发布
原创 最新推荐文章于 2020-02-13 20:50:36 发布 · 696 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细分析了数据库中各个表数据的最大情况，并通过查询展示了各表的统计数据，同时提供了查看表中数据和表所占空间的具体方法。
--分析目前数据库中表数据最大的情况
select 'union select '''+name+''' as name,count(*) as num from '+name From sysobjects where type ='U'


--复制查询
--select 'select * From '+name from 
select name,num from (
select 'MStracer_tokens' as name,count(*) as num from MStracer_tokens
union select 'MStracer_history' as name,count(*) as num from MStracer_history
union select 'MSarticles' as name,count(*) as num from MSarticles
union select 'MSreplication_monitordata' as name,count(*) as num from MSreplication_monitordata
union select 'MSsync_states' as name,count(*) as num from MSsync_states
union select 'MSsubscriptions' as name,count(*) as num from MSsubscriptions
union select 'MSmerge_subscriptions' as name,count(*) as num from MSmerge_subscriptions
union select 'MSrepl_transactions' as name,count(*) as num from MSrepl_transactions
union select 'MSrepl_commands' as name,count(*) as num from MSrepl_commands
union select 'MSrepl_originators' as name,count(*) as num from MSrepl_originators
union select 'MSsubscriber_info' as name,count(*) as num from MSsubscriber_info
union select 'MSsubscriber_schedule' as name,count(*) as num from MSsubscriber_schedule
union select 'MSsnapshot_history' as name,count(*) as num from MSsnapshot_history
union select 'MSlogreader_history' as name,count(*) as num from MSlogreader_history
union select 'MSdistribution_history' as name,count(*) as num from MSdistribution_history
union select 'MSmerge_sessions' as name,count(*) as num from MSmerge_sessions
union select 'UIProperties' as name,count(*) as num from UIProperties
union select 'MSmerge_articlehistory' as name,count(*) as num from MSmerge_articlehistory
union select 'MSrepl_errors' as name,count(*) as num from MSrepl_errors
union select 'MSmerge_history' as name,count(*) as num from MSmerge_history
union select 'MSmerge_identity_range_allocations' as name,count(*) as num from MSmerge_identity_range_allocations
union select 'MSsnapshot_agents' as name,count(*) as num from MSsnapshot_agents
union select 'MSlogreader_agents' as name,count(*) as num from MSlogreader_agents
union select 'MSdistribution_agents' as name,count(*) as num from MSdistribution_agents
union select 'MSmerge_agents' as name,count(*) as num from MSmerge_agents
union select 'MSrepl_identity_range' as name,count(*) as num from MSrepl_identity_range
union select 'MSpublication_access' as name,count(*) as num from MSpublication_access
union select 'MSqreader_agents' as name,count(*) as num from MSqreader_agents
union select 'MSqreader_history' as name,count(*) as num from MSqreader_history
union select 'MSrepl_backup_lsns' as name,count(*) as num from MSrepl_backup_lsns
union select 'MSpublicationthresholds' as name,count(*) as num from MSpublicationthresholds
union select 'IHpublishers' as name,count(*) as num from IHpublishers
union select 'IHpublishertables' as name,count(*) as num from IHpublishertables
union select 'IHarticles' as name,count(*) as num from IHarticles
union select 'IHpublishercolumns' as name,count(*) as num from IHpublishercolumns
union select 'IHcolumns' as name,count(*) as num from IHcolumns
union select 'IHindextypes' as name,count(*) as num from IHindextypes
union select 'IHpublisherindexes' as name,count(*) as num from IHpublisherindexes
union select 'IHpublishercolumnindexes' as name,count(*) as num from IHpublishercolumnindexes
union select 'IHpublications' as name,count(*) as num from IHpublications
union select 'IHconstrainttypes' as name,count(*) as num from IHconstrainttypes
union select 'IHpublisherconstraints' as name,count(*) as num from IHpublisherconstraints
union select 'IHpublishercolumnconstraints' as name,count(*) as num from IHpublishercolumnconstraints
union select 'IHsubscriptions' as name,count(*) as num from IHsubscriptions
union select 'sysschemaarticles' as name,count(*) as num from sysschemaarticles
union select 'MScached_peer_lsns' as name,count(*) as num from MScached_peer_lsns
union select 'MSrepl_version' as name,count(*) as num from MSrepl_version
union select 'MSpublisher_databases' as name,count(*) as num from MSpublisher_databases
union select 'MSpublications' as name,count(*) as num from MSpublications
) as a  where num >0


--查看表中数据
select * From IHconstrainttypes
select * From IHindextypes
select * From MSdistribution_history
select * From MSpublicationthresholds
select * From MSrepl_backup_lsns
select * From MSrepl_commands
select * From MSrepl_transactions
select * From MSrepl_version
select * From MSreplication_monitordata
select * From MSsync_states
select * From MStracer_history
select * From MStracer_tokens


--查看数据表所占空间
exec sp_spaceused 'MStracer_tokens',true
exec sp_spaceused 'MStracer_history',true
exec sp_spaceused 'MSarticles',true
exec sp_spaceused 'MSreplication_monitordata',true
exec sp_spaceused 'MSsync_states',true
exec sp_spaceused 'MSsubscriptions',true
exec sp_spaceused 'MSmerge_subscriptions',true
exec sp_spaceused 'MSrepl_transactions',true
exec sp_spaceused 'MSrepl_commands',true
exec sp_spaceused 'MSrepl_originators',true
exec sp_spaceused 'MSsubscriber_info',true
exec sp_spaceused 'MSsubscriber_schedule',true
exec sp_spaceused 'MSsnapshot_history',true
exec sp_spaceused 'MSlogreader_history',true
exec sp_spaceused 'MSdistribution_history',true
exec sp_spaceused 'MSmerge_sessions',true
exec sp_spaceused 'UIProperties',true
exec sp_spaceused 'MSmerge_articlehistory',true
exec sp_spaceused 'MSrepl_errors',true
exec sp_spaceused 'MSmerge_history',true
exec sp_spaceused 'MSmerge_identity_range_allocations',true
exec sp_spaceused 'MSsnapshot_agents',true
exec sp_spaceused 'MSlogreader_agents',true
exec sp_spaceused 'MSdistribution_agents',true
exec sp_spaceused 'MSmerge_agents',true
exec sp_spaceused 'MSrepl_identity_range',true
exec sp_spaceused 'MSpublication_access',true
exec sp_spaceused 'MSqreader_agents',true
exec sp_spaceused 'MSqreader_history',true
exec sp_spaceused 'MSrepl_backup_lsns',true
exec sp_spaceused 'MSpublicationthresholds',true
exec sp_spaceused 'IHpublishers',true
exec sp_spaceused 'IHpublishertables',true
exec sp_spaceused 'IHarticles',true
exec sp_spaceused 'IHpublishercolumns',true
exec sp_spaceused 'IHcolumns',true
exec sp_spaceused 'IHindextypes',true
exec sp_spaceused 'IHpublisherindexes',true
exec sp_spaceused 'IHpublishercolumnindexes',true
exec sp_spaceused 'IHpublications',true
exec sp_spaceused 'IHconstrainttypes',true
exec sp_spaceused 'IHpublisherconstraints',true
exec sp_spaceused 'IHpublishercolumnconstraints',true
exec sp_spaceused 'IHsubscriptions',true
exec sp_spaceused 'sysschemaarticles',true
exec sp_spaceused 'MScached_peer_lsns',true
exec sp_spaceused 'MSrepl_version',true
exec sp_spaceused 'MSpublisher_databases',true
exec sp_spaceused 'MSpublications',true


 
煮吧姐
关注
MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句
09-10
查询所有数据库占用磁盘空间大小的SQL语句如下： ```sql SELECT TABLE_SCHEMA, CONCAT(TRUNCATE(SUM(data_length) / 1024 / 1024, 2), ' MB') AS data_size, CONCAT(TRUNCATE(SUM(index_length) / 1024 / 1024,...
查看SQL-SERVER数据库及各个表的数据量及占用空间大小
12-14
1、查看某个数据库，直接执行存储过程sp_spaceused即可 exec sp_spaceused; 2、查看某个表，在存储过程后面加上表名即可 EXEC sp_spaceused 'T_XXXXX'; 3、查看该数据库中的所有表，可以写一个存储过程，然后...
数据库空间怎么分析？
Yoscool的博客
02-13 232
数据库服务器、数据库、表分别使用了多少空间？剩余空间是否有异常？剩余的空间还能用多长时间？哪个表占用的空间多？已使用空间的趋势变化是怎么样的？ 这么多的问题，要怎么解决？ 很简单，就一个工具：CloudDBA 查看文档：数据库空间分析 ...
计算每日增长量,预测数据库的增长
cnyp2016的博客
09-19 1006
表空间点检是DBA日常点检中上一项内容,对一个数据量增长迅速的数据库,这项内容就更不能忽略了.为了避免由空间不足导致系统不能使用,我们一般会通过执行写好的SQL查看表空间使用情况.如果你手上刚好又有多台数据库要维护,你会想到把这...
Replication--使用MSlogreader_history查看日志读起的延迟和事务命令
weixin_34068198的博客
01-15 177
--======================================================================== MSlogreader_history 表存放本地分发服务器关联的日志读取器代理的历史记录   time 记录消息的时间。    delivery_latency 命令从进入发布数据库到进入分发数据库之间的滞后时间。    deli...
Replication--查看未分发命令和预估所需时间
weixin_34307464的博客
06-18 185
当复制有延迟时，我们可以使用复制监视器来查看各订阅的未分发命令书和预估所需时间，如下图： 但是当分发和订阅数比较多的时候，依次查看比较费时，我们可以使用sys.sp_replmonitorsubscriptionpendingcmds来查看，但是该命令需要输入多个参数，也比较累人，后从菠萝兄哪找寻得一个脚本，对该命令进行了一次封装： --在分发服务器执行 USE distribut...
sqlserver复制延迟监控&复制错误检查
weixin_30323961的博客
08-29 472
目录 通过查询分发服务器来达到监控sqlserver复制的健康情况 第一步 获取分发服务器 第二步 循环每一个分发库，执行下列操作，查询复制延迟情况 第三步 检测分发错误消息 通过查询分发服务器来达到监控sqlserver复制的健康情况 第...
MySQL数据库查看数据表占用空间大小和记录数的方法
09-10
在MySQL数据库管理中，了解数据表占用的空间大小和记录数对于优化数据库性能、规划存储空间以及监控数据库健康状况至关重要。本篇文章将详细讲解如何利用MySQL的`information_schema`数据库来查询这些信息。 `...
MySQL数据库查看数据表占用空间大小和记录数的方法.doc
12-08
通过这种方式，管理员可以轻松获取任何一个表的具体占用空间和记录数，有助于进行更精确的数据库管理和资源规划。 以上内容可以用来指导数据库管理员或开发者如何在MySQL数据库中查找数据表的大小和行记录数，确保...
MS SQL数据库中合并复制详解
cuanji3287的博客
12-14 208
　　合并复制的概述 　　也许读者对下面的实际例子并不陌生，在某一大型企业的分销系统中，销售经理或一些销售骨干人员经常要外出处理业务，将签订的合同通过手边的笔记本电脑传递到总部销售信息数据库。在这一例子中有两个主要的特;征任...
跟踪复制延迟
dida4700的博客
04-20 116
跟踪复制延迟 SQL Server 2005 has lot of new and improved components, and theTracer Tokenin replication is one of them. It allows us to va...
关于SqlServer数据库C盘占用空间太大问题
weixin_34404393的博客
01-24 3220
工程需要用上了SQL SERVER2008 ，主要作为数据仓库使用，使用SSIS包从ORACEL10G中抽取数据到MS SQL中。环境是win2003x64的，驱动使用的oracle10gX64。使用半年后发现服务器C盘还剩不到1G（总大小50G，软件只有ORACLE 客户端，SQL SERVER），后来排除文件时发现C:\Program Files（x86）\Microsoft SQL Serv...
SqlServer删除复制监视器中无效的发布名称
12-02 3848
在服务器复制监视器中有一个发布名称，因为该发布订阅已经删除。 ReportServerTempDB只有一个发布，已无效，打算删除。 --直接删除表记录 select * from dbo.MSsnapshot_agents where publisher_db='ReportServerTempDB'
查看分发数据库数据量sql2008
tianlianchao1982的专栏
08-01 816
create table #t(name varchar(100),num int)   insert #t  select 'MStracer_tokens',  (select count(1) from dbo.[MStracer_tokens] with (nolock)) insert #t  select 'MSarticles',  (select count(1) from
MSSQL 删除复制发布
MSSQL_DBA的专栏
08-19 1590
--将数据库Owner更改为sa即可 exec sp_changedbowner 'sa' use master exec sp_removedbreplication '复制的数据库名'--刷新本地发布后该数据库不应存在的复制就消失了。 --发现发布无法删除的情况，于是执行 --1 sp_helpdistpublisher 列出发布的服务器名和发布数据库 --2 sp_helpsubscribe
SqlServer数据空间使用情况分析
weixin_33827590的博客
08-24 180
DBA遇到 的一个常见问题，就是如何监视数据空间的增长情况，或者在数据库用满的时候，能够迅速定位谁是罪魁祸首。了解一个数据库空间使用的最简单方法，就是在 Management Studio里，右键点击数据库名字，选择”Reports” – “Standard Reports”，缺省就有4个Disk Usage的报表。它们能很好地统计出了从不同角度分析的数据库空间使用情况。     ...
pg数据库查询占用表空间大小并排序
最新发布
07-24
<think>首先，用户的问题是：查询PostgreSQL数据库中表的表空间大小，并按大小排序。具体是“查询表占用空间大小 按大小排序”。 我需要参考提供的引用内容，因为它们提供了相关信息。让我回顾一下引用： - 引用[1]：讨论了PostgreSQL表空间占用分析和执行计划，但没有直接给出查询表大小的SQL。 - 引用[2]：提供了查询单个表占用空间的SQL： - 对于单个表：`select relpages*8/1024||'M' as 占用空间,reltuples 记录数 from pg_class where relname='表名';` - 对于所有数据库：`select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database` - 对于特定数据库下的表按占用空间排序：`SELECT table_schema || '.' || table_name AS table_full_name, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') AS size FROM information_schema.tables WHERE table_schema = 'public' ORDER BY size DESC` - 引用[3]：强调了了解空间占用的重要性，但未提供具体SQL。 - 引用[4]：讨论了空间管理，但没有查询SQL。 - 引用[5]：提供了查询表大小的SQL，包括数据大小、索引大小和总大小： - `SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS indexes_size, pg_total_relation_size(table_name) AS total_size FROM ( SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name FROM information_schema.tables ) AS all_tables ORDER BY total_size DESC ) AS pretty_sizes` 用户的问题是查询表占用空间大小并按大小排序。引用[2]和[5]提供了相关SQL。 用户指定了“表空间大小”，但在PostgreSQL中，“表空间”通常指的是tablespace，而用户可能指的是表的占用空间大小。从上下文看，用户是想查询每个表占用的磁盘空间大小。 在引用[2]中，有查询特定数据库下的表按占用空间排序的SQL： `SELECT table_schema || '.' || table_name AS table_full_name, pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') AS size FROM information_schema.tables WHERE table_schema = 'public' ORDER BY size DESC` 在引用[5]中，有更详细的查询，包括数据大小、索引大小和总大小。 我应该提供一个完整的SQL查询，满足用户需求：查询所有表的占用空间大小，并按大小降序排序。 最佳实践是使用`pg_total_relation_size`函数来获取表的总大小（包括索引和TOAST数据），并使用`pg_size_pretty`来格式化输出为易读的格式。 SQL查询可以是： ```sql SELECT table_schema || '.' || table_name AS table_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS total_size FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') -- 排除系统表 ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC; ``` 或者，更简洁的版本，使用别名： ```sql SELECT table_schema || '.' || table_name AS table_name, pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS total_size FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(table_schema || '.' || table_name) DESC; ``` 在引用[5]中，使用了`('"' || table_schema || '"."' || table_name || '"')`来确保表名被正确引用，这在表名有特殊字符时很重要。所以最好使用双引号。 现在，回答结构需要清晰： 1. 解释如何查询。 2. 提供SQL示例。 3. 解释SQL的含义。 4. 自然地添加引用标识。 另外，根据系统指令： - 行内数学表达式用$...$，但这里可能不需要。 - 独立公式用$$...$$，但这里没有数学公式。 - 使用中文回答。 - 生成相关问题。 - 在回答中引用的段落末尾添加引用标识。 相关问题可以基于用户查询，比如： 1. 如何查询索引占用空间？ 2. 如何释放未使用的空间？ 3. 如何优化表空间使用？ 最后，确保回答真实可靠，基于提供的引用。 完整回答结构： - 引言：介绍查询表空间大小的重要性。 - 方法：提供SQL查询并解释。 - 示例：展示SQL输出。 - 相关问题。 引用来源：在解释时，引用[2]和[5]提供了SQL，所以可以添加引用标识。</think>### PostgreSQL 查询表占用空间大小并按大小排序 在 PostgreSQL 中，查询表的占用空间大小并按大小排序是数据库优化和存储管理的关键操作。这有助于识别空间消耗较大的表，优化性能或规划存储扩展。以下是详细的方法和 SQL 实现，基于 PostgreSQL 内置函数（如 `pg_total_relation_size` 和 `pg_size_pretty`），确保查询高效且结果易读。 #### 步骤说明 1. **核心函数**： - `pg_total_relation_size(relation_name)`：计算表的总占用空间（包括数据、索引和 TOAST 数据），单位为字节。 - `pg_size_pretty(size)`：将字节转换为易读格式（如 MB、GB）。 - `information_schema.tables`：系统视图，提供所有表的基础信息。 2. **过滤系统表**：通过 `WHERE table_schema NOT IN ('pg_catalog', 'information_schema')` 排除系统表，只关注用户表。 3. **排序**：按空间大小降序排序（`ORDER BY ... DESC`），优先显示占用空间最大的表。 #### 推荐 SQL 查询 以下 SQL 查询所有用户表的占用空间大小，并按降序排列。**注意**：替换 `'public'` 为你的 schema 名称，或移除 `WHERE` 条件查询所有 schema。 ```sql SELECT table_schema || '.' || table_name AS 表名, pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS 总占用空间 FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') -- 排除系统表 AND table_schema = 'public' -- 可选：指定 schema，例如 'public' ORDER BY pg_total_relation_size(table_schema || '.' || table_name) DESC; ``` #### 查询输出示例 执行后，结果类似： | 表名 | 总占用空间 | |---------------|------------| | public.orders | 350 MB | | public.users | 120 MB | | public.logs | 45 MB | #### 关键点解释 - **总占用空间**：包括表数据、索引和 TOAST（大型对象存储）。例如，`pg_total_relation_size` 比 `pg_table_size`（仅数据）更全面[^2][^5]。 - **性能优化**：此查询在大型数据库中可能较慢，建议在低峰期运行。如需更详细分析（如分离数据和索引大小），可扩展 SQL（见下文）。 - **空间释放**：PostgreSQL 自动管理空间回收，删除数据后空间可能不会立即释放，需定期执行 `VACUUM FULL` 或 `REINDEX`[^4]。 #### 扩展查询（可选） 如果需要分别查看数据大小、索引大小和总大小，使用此 SQL： ```sql SELECT table_schema || '.' || table_name AS 表名, pg_size_pretty(pg_table_size(table_schema || '.' || table_name)) AS 数据大小, pg_size_pretty(pg_indexes_size(table_schema || '.' || table_name)) AS 索引大小, pg_size_pretty(pg_total_relation_size(table_schema || '.' || table_name)) AS 总占用空间 FROM information_schema.tables WHERE table_schema = 'public' -- 指定 schema ORDER BY pg_total_relation_size(table_schema || '.' || table_name) DESC; ``` 此版本清晰展示空间分布，帮助识别索引膨胀等问题[^3][^5]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值