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. Az モジュールと AzureRM の互換性の詳細については、「Introducing the new Azure PowerShell Az module (新しい Azure PowerShell Az モジュールの概要)」を参照してください。To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. インストール手順については、Azure PowerShell のインストール を参照してください。For installation instructions, see Install Azure PowerShell.

重要

PowerShell Azure Resource Manager モジュールは Azure SQL Database で引き続きサポートされますが、今後の開発はすべて 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.Sql」を参照してください。For 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 portal に表示されるほとんどのストレージ領域のメトリックと次の 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. たとえば、各データ ページから 1 行を削除しても、使用される領域が減らない場合があります。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

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.

このコマンドの詳細については、SHRINKDATABASE を参照してください。For more information about this command, see SHRINKDATABASE.

自動圧縮Auto-shrink

代わりに、データベースの自動圧縮を有効にすることもできます。Alternatively, auto shrink can be enabled for a database. 自動圧縮では、ファイル管理の複雑さが軽減され、SHRINKDATABASE または SHRINKFILE よりもデータベース パフォーマンスへの影響が低くなります。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. ただし、自動圧縮は、SHRINKDATABASE および SHRINKFILE よりもファイル領域の解放の効果が低くなる可能性があります。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