大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
这篇文章给大家分享的是有关数据库的硬盘空间如何使用的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
创新互联公司主要从事网站建设、成都网站建设、网页设计、企业做网站、公司建网站等业务。立足成都服务龙文,十多年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:18982081108
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' ]
感谢各位的阅读!关于“数据库的硬盘空间如何使用”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!