Shulou(Shulou.com)05/31 Report--

This article is about how to use the hard disk space of the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The storage space occupied by SQL Server, including the storage space occupied by database file and the storage space occupied by database objects.

First, the storage space occupied by the database file

1. Use sys.master_files to view the storage space occupied by each file in the database

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. Use sp_spaceused to view the space usage of the current DB

Use DB_Studygoexec sys.sp_spaceused

Database_size:database_size includes both data and log files.

Space utilization information of data 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. Count the disk space usage of data file according to extent

Read the Extent allocate information from the system page:GAM and SGAM and calculate how many extent allocated or unallocated the data file has.

Calculation formula: 1Extentcards 8Pagesstatement 1Pageboxes 8KB

Dbcc showfilestats

4. Count the disk space usage of log files of all databases in the SQL Server instance

The result returned by dbcc sqlperf (logspace) is always accurate, and the execution of the statement will not increase the burden on sql server.

Dbcc sqlperf (logspace)

Second, check the disk space occupied by each table or index in the database

1. View the disk space occupied by all table or index in the database

Select t.name, sum (case when ps.index_id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.