Hantera fil utrymme för enskilda databaser och databaser i Azure SQL DatabaseManage file space for single and pooled databases in Azure SQL Database

I den här artikeln beskrivs olika typer av lagrings utrymme för enskilda databaser och databaser i Azure SQL Database och steg som kan vidtas när fil utrymmet som allokerats för databaser och elastiska pooler måste hanteras explicit.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.

Anteckning

Den här artikeln gäller inte distributions alternativet hanterad instans i Azure SQL Database.This article does not apply to the managed instance deployment option in Azure SQL Database.

ÖversiktOverview

Med enkla databaser och databaser i Azure SQL Database finns det arbets belastnings mönster där allokeringen av underliggande datafiler för databaser kan bli större än mängden använda data sidor.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. Den här situationen kan uppstå när mängden utnyttjat utrymme ökar och data därefter raderas.This condition can occur when space used increases and data is subsequently deleted. Orsaken till detta beror på att det allokerade fil utrymmet inte frigörs automatiskt när data tas bort.The reason is because file space allocated is not automatically reclaimed when data is deleted.

I följande scenarier kan det vara nödvändigt att övervaka användningen av filutrymmet och att krympa datafiler:Monitoring file space usage and shrinking data files may be necessary in the following scenarios:

  • Tillåt datatillväxt i en elastisk pool när filutrymmet som tilldelats dess databaser når poolens maxstorlek.Allow data growth in an elastic pool when the file space allocated for its databases reaches the pool max size.
  • Tillåt att maxstorleken för en enskild databas eller elastisk pool minskas.Allow decreasing the max size of a single database or elastic pool.
  • Tillåt att en enskild databas eller elastisk pool ändras till en annan tjänstnivå eller prestandanivå med en mindre maxstorlek.Allow changing a single database or elastic pool to a different service tier or performance tier with a lower max size.

Övervaka fil utrymmes användningMonitoring file space usage

De flesta mått för lagrings utrymme som visas i Azure Portal och följande API: er mäter endast storleken på data sidor som används:Most storage space metrics displayed in the Azure portal and the following APIs only measure the size of used data pages:

Följande API: er mäter dock också storleken på utrymmet som allokerats för databaser och elastiska pooler:However, the following APIs also measure the size of space allocated for databases and elastic pools:

Krympa datafilerShrinking data files

SQL Database tjänsten krymper inte automatiskt datafiler för att frigöra oanvänt allokerat utrymme på grund av den potentiella påverkan av databasens prestanda.The SQL Database service does not automatically shrink data files to reclaim unused allocated space due to the potential impact to database performance. Kunder kan dock krympa datafiler via självbetjäning när de väljer genom att följa stegen som beskrivs i frigör oanvänt allokerat utrymme.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.

Anteckning

Till skillnad från datafiler krymper tjänsten SQL Database tjänsten automatiskt loggfiler eftersom åtgärden inte påverkar databasens prestanda.Unlike data files, the SQL Database service automatically shrinks log files since that operation does not impact database performance.

Förstå typer av lagrings utrymme för en databasUnderstanding types of storage space for a database

Det är viktigt att förstå följande lagrings utrymmes mängder för att hantera fil utrymmet för en databas.Understanding the following storage space quantities are important for managing the file space of a database.

Databas antalDatabase quantity DefinitionDefinition KommentarerComments
Använt data utrymmeData space used Mängden utrymme som används för att lagra databas data på 8 KB-sidor.The amount of space used to store database data in 8 KB pages. Normalt ökar utrymmet som används (minskar) vid infogningar (rader).Generally, space used increases (decreases) on inserts (deletes). I vissa fall ändras inte utrymmet som används vid infogningar eller borttagningar beroende på mängden och mönstret för data som ingår i åtgärden och eventuell fragmentering.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. Att till exempel ta bort en rad från alla data sidor minskar inte nödvändigt vis det utrymme som används.For example, deleting one row from every data page does not necessarily decrease the space used.
Allokerat data utrymmeData space allocated Mängden formaterat fil utrymme som är tillgängligt för lagring av databas data.The amount of formatted file space made available for storing database data. Mängden utrymme som allokeras växer automatiskt, men minskar aldrig efter borttagningarna.The amount of space allocated grows automatically, but never decreases after deletes. Det här beteendet säkerställer att framtida infogningar är snabbare eftersom utrymmet inte behöver formateras om.This behavior ensures that future inserts are faster since space does not need to be reformatted.
Allokerat data utrymme men oanväntData space allocated but unused Skillnaden mellan mängden allokerat data utrymme och använt data utrymme.The difference between the amount of data space allocated and data space used. Den här kvantiteten representerar den maximala mängden ledigt utrymme som kan frigöras genom att krympa datafiler för databasen.This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.
Max storlek för dataData max size Den maximala mängd utrymme som kan användas för att lagra databas data.The maximum amount of space that can be used for storing database data. Mängden allokerat data utrymme kan inte växa utöver data Max storleken.The amount of data space allocated cannot grow beyond the data max size.

Följande diagram illustrerar förhållandet mellan de olika typerna av lagrings utrymme för en databas.The following diagram illustrates the relationship between the different types of storage space for a database.

lagrings utrymmes typer och-relationer

Fråga en enskild databas om lagrings utrymmes informationQuery a single database for storage space information

Följande frågor kan användas för att fastställa antalet lagrings utrymmen för en enskild databas.The following queries can be used to determine storage space quantities for a single database.

Använt databas data utrymmeDatabase data space used

Ändra följande fråga för att returnera mängden databas data utrymme som används.Modify the following query to return the amount of database data space used. Enheter i frågeresultatet är i 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

Allokerat och oanvänt utrymme för databas data utrymmeDatabase data space allocated and unused allocated space

Använd följande fråga för att returnera mängden allokerat utrymme för databas data och mängden oanvänt utrymme som allokerats.Use the following query to return the amount of database data space allocated and the amount of unused space allocated. Enheter i frågeresultatet är i 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'

Maximal storlek för databas dataDatabase data max size

Ändra följande fråga för att returnera den maximala storleken för databas data.Modify the following query to return the database data max size. Enheter i frågeresultatet anges i byte.Units of the query result are in bytes.

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

Förstå typer av lagrings utrymme för en elastisk poolUnderstanding types of storage space for an elastic pool

Det är viktigt att förstå följande lagrings utrymmes mängder för att hantera fil utrymmet för en elastisk pool.Understanding the following storage space quantities are important for managing the file space of an elastic pool.

Kvantitet för elastisk poolElastic pool quantity DefinitionDefinition KommentarerComments
Använt data utrymmeData space used En summering av data utrymmet som används av alla databaser i den elastiska poolen.The summation of data space used by all databases in the elastic pool.
Allokerat data utrymmeData space allocated En summering av det data utrymme som allokerats av alla databaser i den elastiska poolen.The summation of data space allocated by all databases in the elastic pool.
Allokerat data utrymme men oanväntData space allocated but unused Skillnaden mellan mängden allokerat data utrymme och data utrymme som används av alla databaser i den elastiska poolen.The difference between the amount of data space allocated and data space used by all databases in the elastic pool. Den här kvantiteten representerar den maximala mängden utrymme som allokerats för den elastiska poolen som kan frigöras genom att krympa datafiler för databasen.This quantity represents the maximum amount of space allocated for the elastic pool that can be reclaimed by shrinking database data files.
Max storlek för dataData max size Den maximala mängden data utrymme som kan användas av den elastiska poolen för alla dess databaser.The maximum amount of data space that can be used by the elastic pool for all of its databases. Utrymmet som allokerats för den elastiska poolen får inte överskrida den högsta tillåtna storleken för elastisk pool.The space allocated for the elastic pool should not exceed the elastic pool max size. Om det här tillståndet inträffar kan allokerat utrymme som inte används frigöras genom att krympa datafiler för databasen.If this condition occurs, then space allocated that is unused can be reclaimed by shrinking database data files.

Fråga en elastisk pool för lagrings utrymmes informationQuery an elastic pool for storage space information

Följande frågor kan användas för att fastställa antalet lagrings utrymmen för en elastisk pool.The following queries can be used to determine storage space quantities for an elastic pool.

Använt data utrymme för elastisk poolElastic pool data space used

Ändra följande fråga för att returnera mängden elastiskt data utrymme för elastisk pool.Modify the following query to return the amount of elastic pool data space used. Enheter i frågeresultatet är i 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

Allokerat data utrymme för elastisk pool och oanvänt allokerat utrymmeElastic pool data space allocated and unused allocated space

Ändra följande exempel för att returnera en tabell som visar utrymmet allokerat och oanvänt allokerat utrymme för varje databas i en elastisk pool.Modify the following examples to return a table listing the space allocated and unused allocated space for each database in an elastic pool. Tabellen beställer databaser från de databaserna med störst mängd oanvänt allokerat utrymme till minsta mängd oanvänt allokerat utrymme.The table orders databases from those databases with the greatest amount of unused allocated space to the least amount of unused allocated space. Enheter i frågeresultatet är i MB.Units of the query result are in MB.

Frågeresultatet för att bestämma hur mycket utrymme som allokeras för varje databas i poolen kan läggas till tillsammans för att fastställa det totala utrymmet som allokerats för den elastiska poolen.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. Allokerat utrymme för elastisk pool får inte överskrida den maximala storleken för elastisk pool.The elastic pool space allocated should not exceed the elastic pool max size.

Viktigt

PowerShell-modulen Azure Resource Manager (RM) stöds fortfarande av Azure SQL Database, men all framtida utveckling är för AZ. SQL-modulen.The PowerShell Azure Resource Manager (RM) module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. AzureRM-modulen kommer att fortsätta att ta emot fel korrigeringar fram till minst december 2020.The AzureRM module will continue to receive bug fixes until at least December 2020. Argumenten för kommandona i AZ-modulen och i AzureRm-modulerna är i stort sett identiska.The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. Mer information om deras kompatibilitet finns i Introduktion till den nya Azure PowerShell AZ-modulen.For more about their compatibility, see Introducing the new Azure PowerShell Az module.

PowerShell-skriptet kräver SQL Server PowerShell-modul – se Ladda ned PowerShell-modulen för att installera.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

Följande skärm bild är ett exempel på utdata från skriptet:The following screenshot is an example of the output of the script:

exempel på allokerat utrymme för elastisk pool och oanvänt allokerat utrymme

Maximal storlek för elastisk poolElastic pool data max size

Ändra följande T-SQL-fråga för att returnera den maximala storleken för elastiska pooler.Modify the following T-SQL query to return the elastic pool data max size. Enheter i frågeresultatet är i 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

Frigör oanvänt allokerat utrymmeReclaim unused allocated space

Anteckning

Det här kommandot kan påverka databasens prestanda när den körs, och om möjligt bör köras under perioder med låg belastning.This command can impact database performance while it is running, and if possible should be run during periods of low usage.

DBCC-krympningDBCC shrink

När databaser har identifierats för att frigöra oanvänt allokerat utrymme ändrar du namnet på databasen i följande kommando för att krympa datafilerna för varje databas.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')

Det här kommandot kan påverka databasens prestanda när den körs, och om möjligt bör köras under perioder med låg belastning.This command can impact database performance while it is running, and if possible should be run during periods of low usage.

Mer information om det här kommandot finns i SHRINKDATABASE.For more information about this command, see SHRINKDATABASE.

Automatisk krympningAuto-shrink

Du kan också aktivera automatisk krympning för en databas.Alternatively, auto shrink can be enabled for a database. Automatisk krympning minskar fil hanterings komplexiteten och är mindre påverkan på databas prestanda än SHRINKDATABASE eller SHRINKFILE.Auto shrink reduces file management complexity and is less impactful to database performance than SHRINKDATABASE or SHRINKFILE. Automatisk krympning kan vara särskilt användbart för att hantera elastiska pooler med många databaser.Auto shrink can be particularly helpful for managing elastic pools with many databases. Den automatiska krympningen kan dock vara mindre effektiv när du frigör fil utrymme än SHRINKDATABASE och SHRINKFILE.However, auto shrink can be less effective in reclaiming file space than SHRINKDATABASE and SHRINKFILE. Om du vill aktivera automatisk krympning ändrar du namnet på databasen i följande kommando.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

Mer information om det här kommandot finns i alternativ för databas uppsättning .For more information about this command, see DATABASE SET options.

Återskapa indexRebuild indexes

När datafilerna i databasen krymps kan index bli fragmenterade och förlora prestanda optimerings effektivitet.After database data files are shrunk, indexes may become fragmented and lose their performance optimization effectiveness. Om prestanda försämringen inträffar bör du överväga att återskapa databas index.If performance degradation occurs, then consider rebuilding database indexes. Mer information om fragmentering och återuppbyggnad av index finns i omorganisera och återskapa index.For more information on fragmentation and rebuilding indexes, see Reorganize and Rebuild Indexes.

Nästa stegNext steps