在 Azure SQL Database 中管理單一和集區資料庫的檔案空間Manage file space for single and pooled databases in Azure SQL Database

本文描述 Azure SQL Database 中適用於單一和集區資料庫的不同類型儲存空間,以及需要明確管理為資料庫與彈性集區配置的檔案空間時所能採取的步驟。This article describes different types of storage space for single and pooled databases in Azure SQL Database, and steps that can be taken when the file space allocated for databases and elastic pools needs to be explicitly managed.

注意

本文不適用於 Azure SQL Database 中的受控執行個體部署選項。This article does not apply to the managed instance deployment option in Azure SQL Database.

總覽Overview

注意

本文已更新為使用新的 Azure PowerShell Az 模組。This article has been updated to use the new Azure PowerShell Az module. AzureRM 模組在至少 2020 年 12 月之前都還會持續收到錯誤 (Bug) 修正,因此您仍然可以持續使用。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要深入了解新的 Az 模組和 AzureRM 的相容性,請參閱新的 Azure PowerShell Az 模組簡介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 如需 Az 模組安裝指示,請參閱安裝 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

重要

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.SqlFor these cmdlets, see AzureRM.Sql. 在 Az 模組和 AzureRm 模組中命令的引數是本質上相同的。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

使用單一和集區資料庫 Azure SQL Database 時,某些工作負載模式的資料庫基礎資料檔案的配置可能會大於已使用資料頁數。With single and pooled databases in 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

由於可能會對資料庫效能造成影響,因此 SQL Database 服務不會自動壓縮資料檔案,以回收未使用的配置空間。The SQL Database service 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.

注意

不同於資料檔案,SQL Database 服務會自動壓縮記錄檔,因為該作業並不會影響資料庫效能。Unlike data files, the SQL Database service 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.

查詢彈性集區,以取得儲存體空間資訊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

修改下列 PowerShell 指令碼以傳回資料表,列出為彈性集區中的每個資料庫配置的空間與未使用的配置空間。Modify the following PowerShell script 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.

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

# Resource group name
$resourceGroupName = "rg1" 
# Server name
$serverName = "ls2" 
# Elastic pool name
$poolName = "ep1"
# User name for server
$userName = "name"
# Password for server
$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 its 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 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

注意

此命令可能會在資料庫執行時影響其效能,如果可能,應該在低使用量期間執行。This command can impact database performance while it is 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')

此命令可能會在資料庫執行時影響其效能,如果可能,應該在低使用量期間執行。This command can impact database performance while it is running, and if possible should be run during periods of low usage.

如需有關此命令的詳細資訊,請參閱 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. 若要啟用自動壓縮,請修改下列命令中的資料庫名稱。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