在 Azure SQL Database 中管理資料庫的檔案空間Manage file space for databases in Azure SQL Database

適用於: Azure SQL Database

本文說明 Azure SQL Database 中資料庫的不同儲存空間類型,以及當設定檔案空間需要明確管理時可採取的步驟。This article describes different types of storage space for databases in Azure SQL Database, and steps that can be taken when the file space allocated needs to be explicitly managed.

注意

本文「不」適用於 Azure SQL Database 受控執行個體。This article does not apply to Azure SQL Managed Instance.

總覽Overview

使用 Azure SQL Database 時,會有工作負載模式,而資料庫的基礎資料檔案配置可能會變得大於使用的資料頁量。With Azure SQL Database, there are workload patterns where the allocation of underlying data files for databases can become larger than the amount of used data pages. 當使用的空間增加,隨後卻將資料刪除時,就會發生這種狀況。This condition can occur when space used increases and data is subsequently deleted. 原因是因為在資料刪除後,並不會自動回收已配置的檔案空間。The reason is because file space allocated is not automatically reclaimed when data is deleted.

在下列情況中,可能需要監視檔案空間使用量和壓縮資料檔案:Monitoring file space usage and shrinking data files may be necessary in the following scenarios:

  • 當配置給檔案資料庫的檔案空間達到集區大小上限時,允許彈性集區中的資料成長。Allow data growth in an elastic pool when the file space allocated for its databases reaches the pool max size.
  • 允許減少單一資料庫或彈性集區的大小上限。Allow decreasing the max size of a single database or elastic pool.
  • 允許將單一資料庫或彈性集區變更為大小上限較低的不同服務層級或效能層級。Allow changing a single database or elastic pool to a different service tier or performance tier with a lower max size.

監視檔案空間使用量Monitoring file space usage

在 Azure 入口網站和下列 API 中顯示的大部分儲存體空間計量只會測量已使用資料頁面的大小:Most storage space metrics displayed in the Azure portal and the following APIs only measure the size of used data pages:

不過,下列 API 也會測量配置給資料庫和彈性集區的空間大小:However, the following APIs also measure the size of space allocated for databases and elastic pools:

壓縮資料檔案Shrinking data files

Azure SQL Database 不會自動壓縮資料檔案,以回收未使用的配置空間,因為可能會對資料庫效能造成影響。Azure SQL Database does not automatically shrink data files to reclaim unused allocated space due to the potential impact to database performance. 不過,客戶可以依照「 回收未使用的配置空間」中所述的步驟,在其選擇時,透過自助服務來壓縮資料檔案。However, customers may shrink data files via self-service at a time of their choosing by following the steps described in reclaim unused allocated space.

注意

不同于資料檔案,Azure SQL Database 會自動壓縮記錄檔,因為該作業不會影響資料庫效能。Unlike data files, Azure SQL Database automatically shrinks log files since that operation does not impact database performance.

了解資料庫的儲存體空間類型Understanding types of storage space for a database

要管理資料庫的檔案空間,務必要了解下列儲存體空間數量。Understanding the following storage space quantities are important for managing the file space of a database.

資料庫數量Database quantity 定義Definition 註解Comments
已使用的資料空間Data space used 以 8 KB 分頁用來儲存資料庫資料的空間量。The amount of space used to store database data in 8 KB pages. 一般而言,使用的空間會在插入 (刪除) 時增加 (減少)。Generally, space used increases (decreases) on inserts (deletes). 在某些情況下,根據作業與任何分割中涉及的資料量和模式而定,使用的空間並不會隨插入或刪除而變更。In some cases, the space used does not change on inserts or deletes depending on the amount and pattern of data involved in the operation and any fragmentation. 例如,從每個資料頁刪除一個資料列,不見得會減少使用的空間。For example, deleting one row from every data page does not necessarily decrease the space used.
已配置的資料空間Data space allocated 可用以儲存資料庫資料的格式化檔案空間量。The amount of formatted file space made available for storing database data. 配置的空間量會自動成長,但絕不會在刪除後減少。The amount of space allocated grows automatically, but never decreases after deletes. 此行為可確保未來能更快地插入,因為不需要重新格式化空間。This behavior ensures that future inserts are faster since space does not need to be reformatted.
已配置但未使用的資料空間Data space allocated but unused 已配置的資料空間量與已使用的資料空間之間的差異。The difference between the amount of data space allocated and data space used. 此數量代表可藉由壓縮資料檔案而回收的可用空間量上限。This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.
資料大小上限Data max size 可用來儲存資料庫資料的空間量上限。The maximum amount of space that can be used for storing database data. 配置的資料空間量不得成長超過資料大小上限。The amount of data space allocated cannot grow beyond the data max size.

下圖說明資料庫的不同儲存體空間類型之間的關聯性。The following diagram illustrates the relationship between the different types of storage space for a database.

儲存體空間類型和關聯性

查詢資料庫來取得儲存體空間資訊Query a single database for storage space information

下列查詢可用來判斷單一資料庫的儲存體空間數量。The following queries can be used to determine storage space quantities for a single database.

使用的資料庫資料空間Database data space used

修改下列查詢,以傳回資料庫已使用的資料空間量。Modify the following query to return the amount of database data space used. 查詢結果以 MB 為單位。Units of the query result are in MB.

-- Connect to master
-- Database data space used in MB
SELECT TOP 1 storage_in_megabytes AS DatabaseDataSpaceUsedInMB
FROM sys.resource_stats
WHERE database_name = 'db1'
ORDER BY end_time DESC;

配置的資料庫資料空間與已配置但未使用的空間Database data space allocated and unused allocated space

使用下列查詢,以傳回已配置的資料庫資料空間量與已配置但未使用的空間量。Use the following query to return the amount of database data space allocated and the amount of unused space allocated. 查詢結果以 MB 為單位。Units of the query result are in MB.

-- Connect to database
-- Database data space allocated in MB and database data space allocated unused in MB
SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB,
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB
FROM sys.database_files
GROUP BY type_desc
HAVING type_desc = 'ROWS';

資料庫資料大小上限Database data max size

修改下列查詢,以傳回資料庫資料大小上限。Modify the following query to return the database data max size. 查詢結果以位元組為單位。Units of the query result are in bytes.

-- Connect to database
-- Database data max size in bytes
SELECT DATABASEPROPERTYEX('db1', 'MaxSizeInBytes') AS DatabaseDataMaxSizeInBytes;

了解彈性集區的儲存體空間類型Understanding types of storage space for an elastic pool

要管理彈性集區的檔案空間,務必要了解下列儲存體空間數量。Understanding the following storage space quantities are important for managing the file space of an elastic pool.

彈性集區數量Elastic pool quantity 定義Definition 註解Comments
已使用的資料空間Data space used 彈性集區中的所有資料庫所使用的資料空間總和。The summation of data space used by all databases in the elastic pool.
已配置的資料空間Data space allocated 彈性集區中的所有資料庫所配置的資料空間總和。The summation of data space allocated by all databases in the elastic pool.
已配置但未使用的資料空間Data space allocated but unused 已配置的資料空間量與彈性集區中的所有資料庫已使用的資料空間之間的差異。The difference between the amount of data space allocated and data space used by all databases in the elastic pool. 此數量代表已為彈性集區配置、且可藉由壓縮資料檔案而回收的空間量上限。This quantity represents the maximum amount of space allocated for the elastic pool that can be reclaimed by shrinking database data files.
資料大小上限Data max size 彈性集區可用於其所有資料庫的資料空間量上限。The maximum amount of data space that can be used by the elastic pool for all of its databases. 為彈性集區配置的空間不應超過彈性集區大小上限。The space allocated for the elastic pool should not exceed the elastic pool max size. 如果發生此狀況,則可藉由壓縮資料庫資料檔案來回收已配置但未使用的空間。If this condition occurs, then space allocated that is unused can be reclaimed by shrinking database data files.

注意

[彈性集區已達到儲存空間限制] 錯誤訊息表示已配置足夠的空間來符合彈性集區儲存體限制,但是資料空間配置中可能有未使用的空間。The error message "The elastic pool has reached its storage limit" indicates that the database objects have been allocated enough space to meet the elastic pool storage limit, but there may be unused space in the data space allocation. 請考慮增加彈性集區的儲存空間限制,或使用下面的「 回收未使用 的配置空間」一節,以較短期的解決方案來釋出資料空間。Consider increasing the elastic pool's storage limit, or as a short-term solution, freeing up data space using the Reclaim unused allocated space section below. 您也應該留意壓縮資料庫檔案的潛在負面效能影響,請參閱下面的 重建索引 一節。You should also be aware of the potential negative performance impact of shrinking database files, see Rebuild indexes section below.

查詢彈性集區,以取得儲存體空間資訊Query an elastic pool for storage space information

下列查詢可用來判斷彈性集區的儲存體空間數量。The following queries can be used to determine storage space quantities for an elastic pool.

使用的彈性集區資料空間Elastic pool data space used

修改下列查詢,以傳回已使用的彈性集區資料空間量。Modify the following query to return the amount of elastic pool data space used. 查詢結果以 MB 為單位。Units of the query result are in MB.

-- Connect to master
-- Elastic pool data space used in MB  
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

配置的彈性集區資料空間與已配置但未使用的空間Elastic pool data space allocated and unused allocated space

修改下列範例,以傳回資料表,其中列出彈性集區中每個資料庫所配置的空間和未使用的配置空間。Modify the following examples to return a table listing the space allocated and unused allocated space for each database in an elastic pool. 此資料表會根據資料庫已配置但未使用的空間量,從最大至最小來排序資料庫。The table orders databases from those databases with the greatest amount of unused allocated space to the least amount of unused allocated space. 查詢結果以 MB 為單位。Units of the query result are in MB.

以查詢判斷為集區中的每個資料庫配置的空間所產生的結果,可在加總後用來判斷為彈性集區配置的總空間。The query results for determining the space allocated for each database in the pool can be added together to determine the total space allocated for the elastic pool. 配置的彈性集區空間不應超過彈性集區大小上限。The elastic pool space allocated should not exceed the elastic pool max size.

重要

Azure SQL Database 仍然支援 PowerShell Azure Resource Manager 模組,但所有未來的開發都是針對 Az.Sql 模組。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. AzureRM 模組在至少 2020 年 12 月之前都還會持續收到 Bug 修正。The AzureRM module will continue to receive bug fixes until at least December 2020. Az 模組和 AzureRm 模組中命令的引數本質上完全相同。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. 如需其相容性的詳細資訊,請參閱新的 Azure PowerShell Az 模組簡介For more about their compatibility, see Introducing the new Azure PowerShell Az module.

PowerShell 指令碼需要 SQL Server PowerShell 模組 – 請參閱下載 PowerShell 模組以便安裝。The PowerShell script requires SQL Server PowerShell module – see Download PowerShell module to install.

$resourceGroupName = "<resourceGroupName>"
$serverName = "<serverName>"
$poolName = "<poolName>"
$userName = "<userName>"
$password = "<password>"

# get list of databases in elastic pool
$databasesInPool = Get-AzSqlElasticPoolDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -ElasticPoolName $poolName
$databaseStorageMetrics = @()

# for each database in the elastic pool, get space allocated in MB and space allocated unused in MB
foreach ($database in $databasesInPool) {
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
    FROM sys.database_files `
    GROUP BY type_desc `
    HAVING type_desc = 'ROWS'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
        (Invoke-Sqlcmd -ServerInstance $serverFqdn -Database $database.DatabaseName `
            -Username $userName -Password $password -Query $sqlCommand)
}

# display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort -Property DatabaseDataSpaceAllocatedUnusedInMB -Descending | Format-Table

下列螢幕擷取畫面是指令碼輸出的其中一個範例:The following screenshot is an example of the output of the script:

彈性集區的配置空間和未使用配置空間範例

彈性集區資料大小上限Elastic pool data max size

修改下列 T-SQL 查詢,以傳回上次記錄的彈性集區資料大小上限。Modify the following T-SQL query to return the last recorded elastic pool data max size. 查詢結果以 MB 為單位。Units of the query result are in MB.

-- Connect to master
-- Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

回收未使用的配置空間Reclaim unused allocated space

注意

壓縮命令會影響資料庫在執行時的效能,並盡可能在低使用量期間執行。Shrink commands impact database performance while running, and if possible should be run during periods of low usage.

DBCC 壓縮DBCC shrink

在識別哪些資料庫要回收已配置但未使用的空間後,請修改下列命令中的資料庫名稱,以壓縮每個資料庫的資料檔案。Once databases have been identified for reclaiming unused allocated space, modify the name of the database in the following command to shrink the data files for each database.

-- Shrink database data space allocated.
DBCC SHRINKDATABASE (N'db1');

壓縮命令會影響資料庫在執行時的效能,並盡可能在低使用量期間執行。Shrink commands impact database performance while running, and if possible should be run during periods of low usage.

您也應該留意壓縮資料庫檔案的潛在負面效能影響,請參閱下面的 重建索引 一節。You should also be aware of the potential negative performance impact of shrinking database files, see Rebuild indexes section below.

如需有關此命令的詳細資訊,請參閱 SHRINKDATABASEFor more information about this command, see SHRINKDATABASE.

自動壓縮Auto-shrink

或者,可以啟用資料庫自動壓縮。Alternatively, auto shrink can be enabled for a database. 自動壓縮會減少檔案管理複雜度,且比起 SHRINKDATABASESHRINKFILE 較不影響資料庫效能。Auto shrink reduces file management complexity and is less impactful to database performance than SHRINKDATABASE or SHRINKFILE. 自動壓縮對於管理包含許多資料庫的彈性集區可能特別有用。Auto shrink can be particularly helpful for managing elastic pools with many databases. 不過,自動壓縮比起 SHRINKDATABASESHRINKFILE 在回收檔案空間上可能較沒效率。However, auto shrink can be less effective in reclaiming file space than SHRINKDATABASE and SHRINKFILE. 根據預設,針對大部分的資料庫,預設會停用自動壓縮。By default, Auto Shrink is disabled as recommended for most databases. 如需詳細資訊,請參閱 AUTO_SHRINK 的考慮For more information, see Considerations for AUTO_SHRINK.

若要啟用自動壓縮,請修改下列命令中的資料庫名稱。To enable auto shrink, modify the name of the database in the following command.

-- Enable auto-shrink for the database.
ALTER DATABASE [db1] SET AUTO_SHRINK ON;

如需有關此命令的詳細資訊,請參閱 DATABASE SET 選項。For more information about this command, see DATABASE SET options.

重建索引Rebuild indexes

在資料庫資料檔案壓縮後,索引可能會變得分散而失去效能最佳化效益。After database data files are shrunk, indexes may become fragmented and lose their performance optimization effectiveness. 如果發生效能降低的情形,請考慮重建資料庫索引。If performance degradation occurs, then consider rebuilding database indexes. 如需索引分散和如何重建索引的詳細資訊,請參閱重新組織與重建索引For more information on fragmentation and rebuilding indexes, see Reorganize and Rebuild Indexes.

後續步驟Next steps