這篇文章給大家分享的是有關數據庫的硬盤空間如何使用的內容。小編覺得挺實用的,因此分享給大家做個參考,一起跟隨小編過來看看吧。
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' ]
感謝各位的閱讀!關于“數據庫的硬盤空間如何使用”這篇文章就分享到這里了,希望以上內容可以對大家有一定的幫助,讓大家可以學到更多知識,如果覺得文章不錯,可以把它分享出去讓更多的人看到吧!
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。