数据库的硬盘空间使用
来源:互联网 发布:慈溪行知职高分数线 编辑:程序博客网 时间:2024/05/16 02:07
SQL Server占用的存储空间,包含数据库file占用的存储空间,数据库对象占用的存储空间。
一,数据库file占用的存储空间
1,使用 sys.master_files 查看数据库中各个file占用的存储空间
select db.name as database_name, db.is_auto_shrink_on, db.recovery_model_desc, mf.file_id, mf.type_desc, mf.name as logic_file_name, mf.size*8/1024/1024 as size_gb, mf.physical_name, --mf.max_size, mf.growth, mf.is_percent_growth, mf.state_descfrom sys.databases db inner join sys.master_files mf on db.database_id=mf.database_idwhere mf.size*8/1024/1024>1 -- GBorder by size_gb desc
2,使用sp_spaceused 查看当前DB的空间使用量
use DB_Studygoexec sys.sp_spaceused
database_size:database_size includes both data and log files.
数据文件的空间利用信息:
- unallocated space :Space in the database that has not been reserved for database objects.
- reserved:Total amount of space allocated by objects in the database.
- data:Total amount of space used by data.
- index_size:Total amount of space used by indexes.
- unused :Total amount of space reserved for objects in the database, but not yet used.
- database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.
3, 按照extent统计data file的disk space usage
从系统page:GAM 和 SGAM 上读取Extent allocate 信息,计算data file有多少extent allocated 或 unallocated。
计算公式:1Extent=8Pages,1Page=8KB
dbcc showfilestats
4, 统计SQL Server 实例中所有数据库的日志文件的disk space usage
dbcc sqlperf(logspace) 返回的结果总是准确的,语句的执行不会对sql server增加负担
dbcc sqlperf(logspace)
二,查看数据库中,各个table 或index 所占用的disk space
1,查看数据库所有table 或index 所占用的disk space
select t.name, sum(case when ps.index_id<2 then ps.row_count else 0 end) as row_count, sum(ps.reserved_page_count)*8/1024/1024 as reserved_gb, sum(ps.used_page_count)*8/1024 as used_mb, sum( case when ps.index_id<2 then ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count else 0 end )*8/1024 as data_used_mb, sum(case when ps.index_id>=2 then ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count else 0 end )*8/1024 as index_used_mbfrom sys.dm_db_partition_stats psinner join sys.tables t on ps.object_id=t.object_idgroup by t.object_id, t.nameorder by reserved_gb desc
2,在当前DB中,查看某一个Table object 空间使用信息
exec sp_spaceused 'dbo.dt_study'
rows:Number of rows existing in the table.
reserved:Total amount of reserved space for objname.
data:Total amount of space used by data in objname.
index_size:Total amount of space used by indexes in objname.
unused:Total amount of space reserved for objname but not yet used.
三,使用Standard Reports 查看disk space usage
四,查看服务器各个逻辑盘符剩余的disk space
Exec master.sys.xp_fixeddrives
Appendix:
查看数据库中table,indexed等对象的disk空间使用量,但是返回的结果并不十分精确。
sp_spaceused Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
Syntax
sp_spaceused [[ @objname = ] 'objname' ] [,[ @updateusage = ] 'updateusage' ]
Arguments
Remarks
When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.
Note:When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.
- 数据库的硬盘空间使用
- centos下启用未使用的硬盘空间
- VMvare的使用----调整vmware虚拟机硬盘空间的方法
- 用delete删除数据使数据库占用硬盘空间增大的原因
- Windows XP的硬盘空间优化
- 如何获得可用的硬盘空间?
- 扩展Ubuntu虚拟机的硬盘空间
- 云主机 安装 自己的系统 免费使用更大的硬盘空间
- 获取linux系统的各服务进程与CPU、内存、硬盘空间使用状态
- 调整vmware虚拟机硬盘空间 的方法
- 如何扩大你的虚拟机硬盘空间!
- 如何扩大你的虚拟机硬盘空间!
- 调整vmware虚拟机硬盘空间的方法
- 调整vmware虚拟机硬盘空间的方法
- 调整vmware虚拟机硬盘空间的方法
- 基于VMware的Linux虚拟机硬盘空间扩大
- 如何扩大你的虚拟机硬盘空间!
- 调整vmware虚拟机硬盘空间的方法
- 在支持Markdown的编辑器里添加Latex公式
- 映射文件中用表达式
- 使用keepalived实现双机热备
- RxJava 1 到RxJava2
- Failed to find style 'buttonstyle' in current theme
- 数据库的硬盘空间使用
- lightoj-1221-Travel Company(spfa找负环)
- 小米华为某些机型存在, Home 键让 APP 后台运行, 重新打开 APP 导致重启的解决方案
- JSP_7th_动作指令jsp:useBean
- C++ STL之 vector的capacity和size属性区别
- iOS 使用蓝牙技术MultipeerConnectivity、CoreBluetooth
- linux基础学习14
- BufferedInputStream和BufferedReader文本读取
- Maven项目中使用自己下载的jar包,Maven私服里添加自己下载的jar包。