Delen via

DBCC SHRINKDATABASE (Transact-SQL)

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics-

Hiermee verkleint u de grootte van de gegevens en logboekbestanden in de opgegeven database.

Notitie

Verkleiningsbewerkingen mogen niet worden beschouwd als een normale onderhoudsbewerking. Voor gegevens- en logboekbestanden die groeien als gevolg van regelmatige, terugkerende bedrijfsbewerkingen zijn geen verkleiningsbewerkingen vereist.

Transact-SQL syntaxisconventies

Syntaxis

Syntaxis voor SQL Server:

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH 

    {     
         [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                  <wait_at_low_priority_option_list>
             )] 
         ] 
         
         [ , NO_INFOMSGS]
    }
]

< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>

< wait_at_low_priority_option > ::=
  ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Syntaxis voor Azure Synapse Analytics:

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

Argumenten

database_name | database_id | 0

De databasenaam of -id die moet worden verkleind. 0 geeft aan dat de huidige database wordt gebruikt.

target_percent

Het percentage vrije ruimte dat u in het databasebestand wilt laten liggen nadat de verkleiningsbewerking is voltooid.

NOTRUNCATE

Hiermee verplaatst u toegewezen pagina's van het einde van het bestand naar niet-toegewezen pagina's aan de voorkant van het bestand. Met deze actie worden de gegevens in het bestand gecomprimeerd. target_percent is optioneel. Azure Synapse Analytics biedt geen ondersteuning voor deze optie.

De vrije ruimte aan het einde van het bestand wordt niet teruggezet naar het besturingssysteem en de fysieke grootte van het bestand verandert niet. De database lijkt dus niet te krimpen wanneer u NOTRUNCATEopgeeft.

NOTRUNCATE is alleen van toepassing op gegevensbestanden. NOTRUNCATE heeft geen invloed op het logboekbestand.

TRUNCATEONLY

Alle vrije ruimte aan het einde van het bestand wordt vrijgegeven aan het besturingssysteem. Verplaatst geen pagina's in het bestand. Het gegevensbestand wordt alleen verkleind tot de laatst toegewezen omvang. Negeert target_percent indien opgegeven met TRUNCATEONLY. Azure Synapse Analytics biedt geen ondersteuning voor deze optie.

DBCC SHRINKDATABASE met de optie TRUNCATEONLY is alleen van invloed op het transactielogboekbestand van de database. Als u het gegevensbestand wilt afkappen, gebruikt u in plaats daarvan DBCC SHRINKFILE. Zie DBCC SHRINKFILEvoor meer informatie.

MET NO_INFOMSGS

Onderdrukt alle informatieve berichten met ernstniveaus van 0 tot en met 10.

WAIT_AT_LOW_PRIORITY met verkleiningsbewerkingen

Van toepassing op: SQL Server 2022 (16.x) en latere versies, Azure SQL Database, Azure SQL Managed Instance

De wachtfunctie met lage prioriteit vermindert vergrendelingsconflicten. Voor meer informatie, zie Inzicht in gelijktijdigheidsproblemen met DBCC SHRINKDATABASE.

Deze functie is vergelijkbaar met de WAIT_AT_LOW_PRIORITY met online indexbewerkingen, met enkele verschillen.

  • U kunt ABORT_AFTER_WAIT optie NONEniet opgeven.

WACHTEN_OP_LAGE_PRIORITEIT

Wanneer een opdracht verkleinen wordt uitgevoerd in WAIT_AT_LOW_PRIORITY modus, worden nieuwe query's waarvoor schemastabiliteit (Sch-S) is vereist, niet geblokkeerd door de wachtbewerking voor verkleinen totdat de bewerking voor verkleinen stopt en wordt uitgevoerd. De verkleiningsbewerking wordt uitgevoerd wanneer een schemawijzigingsvergrendeling (Sch-M) kan worden verkregen. Als een nieuwe verkleiningsbewerking in WAIT_AT_LOW_PRIORITY modus geen vergrendeling kan verkrijgen vanwege een langlopende query, treedt er na 1 minuut standaard een time-out op voor de verkleiningsbewerking en wordt deze zonder fout afgesloten.

Als een nieuwe verkleiningsbewerking in WAIT_AT_LOW_PRIORITY modus geen vergrendeling kan verkrijgen vanwege een langlopende query, treedt er na 1 minuut standaard een time-out op voor de verkleiningsbewerking en wordt deze zonder fout afgesloten. Dit gebeurt als de verkleiningsbewerking de Sch-M-vergrendeling niet kan verwerven vanwege een gelijktijdige query of query's met Sch-S-vergrendelingen. Wanneer er een time-out optreedt, wordt fout 49516 verzonden naar het SQL Server-foutenlogboek, bijvoorbeeld: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. Voer de verkleinbewerking opnieuw uit in WAIT_AT_LOW_PRIORITY-modus.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

  • ZELF

    SELF is de standaardoptie. Sluit de verkleiningsdatabasebewerking af die momenteel wordt uitgevoerd zonder actie te ondernemen.

  • BLOKKEERDERS

    Alle gebruikerstransacties beëindigen die de bewerking voor het verkleinen van de database blokkeren, zodat de bewerking kan worden voortgezet. De optie BLOCKERS vereist dat het inloggen ALTER ANY CONNECTION machtiging heeft.

Resultaatset

In de volgende tabel worden de kolommen in de resultatenset beschreven.

Kolomnaam Beschrijving
DbId Databaseidentificatienummer van het bestand dat de database-engine probeerde te verkleinen.
FileId Bestandsidentificatienummer van het bestand dat de database-engine probeerde te verkleinen.
CurrentSize Het aantal pagina's van 8 kB dat het bestand momenteel in beslag neemt.
MinimumSize Minimaal aantal 8-kB pagina's dat het bestand kan innemen. Deze waarde komt overeen met de minimale grootte of oorspronkelijk gemaakte grootte van een bestand.
UsedPages Het aantal pagina's van 8 kB dat momenteel door het bestand wordt gebruikt.
EstimatedPages Het aantal 8-KB-pagina's waarnaar de database-engine het bestand kan verkleinen volgens zijn schatting.

Notitie

De database-engine geeft geen rijen weer voor die bestanden die niet zijn verkleind.

Opmerkingen

Als u alle gegevens en logboekbestanden voor een specifieke database wilt verkleinen, voert u de opdracht DBCC SHRINKDATABASE uit. Als u één gegevens- of logboekbestand tegelijk voor een specifieke database wilt verkleinen, voert u de opdracht DBCC SHRINKFILE uit.

Als u de huidige hoeveelheid vrije (niet-toegewezen) ruimte in de database wilt weergeven, voert u sp_spaceuseduit.

DBCC SHRINKDATABASE bewerkingen kunnen op elk moment in het proces worden gestopt en wordt voltooid werk bewaard.

De database kan niet kleiner zijn dan de geconfigureerde minimale grootte van de database. U geeft de minimale grootte op wanneer de database oorspronkelijk is gemaakt. Of de minimale grootte kan de laatste grootte zijn die expliciet is ingesteld met behulp van een bewerking voor het wijzigen van de bestandsgrootte. Bewerkingen zoals DBCC SHRINKFILE of ALTER DATABASE zijn voorbeelden van wijzigingen in de bestandsgrootte.

Overweeg dat een database oorspronkelijk is gemaakt met een grootte van 10 MB. Vervolgens groeit het tot 100 MB. De kleinste database kan worden verkleind tot 10 MB, zelfs als alle gegevens in de database zijn verwijderd.

Geef de optie NOTRUNCATE of de optie TRUNCATEONLY op wanneer u DBCC SHRINKDATABASEuitvoert. Als u dat niet doet, is het resultaat hetzelfde als wanneer u een DBCC SHRINKDATABASE bewerking uitvoert met NOTRUNCATE gevolgd door een DBCC SHRINKDATABASE bewerking uit te voeren met TRUNCATEONLY.

De shrunk-database hoeft zich niet in de modus voor één gebruiker te bevinden. Andere gebruikers kunnen in de database werken wanneer deze is verkleind, inclusief systeemdatabases.

U kunt een database niet verkleinen terwijl er een back-up van de database wordt gemaakt. U kunt daarentegen geen back-ups maken van een database terwijl een verkleiningsbewerking voor de database wordt uitgevoerd.

Wanneer deze is opgegeven met WAIT_AT_LOW_PRIORITY, wacht de Sch-M-vergrendelingsaanvraag met lage prioriteit gedurende één minuut wanneer de opdracht wordt uitgevoerd. Als de bewerking voor de duur wordt geblokkeerd, wordt de opgegeven ABORT_AFTER_WAIT actie uitgevoerd.

In Azure Synapse SQL-pools wordt het uitvoeren van een opdracht voor verkleinen niet aanbevolen omdat dit een I/O-intensieve bewerking is en uw toegewezen SQL-pool (voorheen SQL DW) offline kan halen. Daarnaast zijn er kosten verbonden aan uw datawarehouse-momentopnamen nadat u deze opdracht hebt uitgevoerd.

Bekende problemen

van toepassing op: SQL Server, Azure SQL Database, Azure SQL Managed Instance, toegewezen SQL-pool van Azure Synapse Analytics

  • Momenteel worden kolommen met LOB-gegevenstypen (varbinary(max), varchar(max)en nvarchar(max)) in gecomprimeerde columnstore-segmenten niet beïnvloed door DBCC SHRINKDATABASE en DBCC SHRINKFILE.

Hoe DBCC SHRINKDATABASE werkt

DBCC SHRINKDATABASE verkleint gegevensbestanden per bestand, maar verkleint logboekbestanden alsof alle logboekbestanden in één aaneengesloten logboekgroep bestaan. Bestanden worden altijd vanaf het einde verkleind.

Stel dat u een aantal logboekbestanden, een gegevensbestand en een database met de naam mydbhebt. De gegevens en logboekbestanden zijn elk 10 MB en het gegevensbestand bevat 6 MB aan gegevens. De database-engine berekent een doelgrootte voor elk bestand. Deze waarde is de grootte waarop het bestand moet worden verkleind. Wanneer DBCC SHRINKDATABASE is opgegeven met target_percent, berekent de database-engine de doelgrootte als de target_percent hoeveelheid ruimte vrij in het bestand nadat deze is verkleind.

Als u bijvoorbeeld een target_percent van 25 opgeeft voor het verkleinen van mydb, berekent de database-engine de doelgrootte voor het gegevensbestand als 8 MB (6 MB aan gegevens plus 2 MB vrije ruimte). Als zodanig verplaatst de database-engine alle gegevens van het laatste 2 MB van het gegevensbestand naar vrije ruimte in de eerste 8 MB van het gegevensbestand en verkleint het bestand.

Stel dat het gegevensbestand van mydb 7 MB aan gegevens bevat. Als u een target_percent van 30 opgeeft, kan dit gegevensbestand worden verkleind tot het gratis percentage van 30. Als u echter een target_percent van 40 opgeeft, wordt het gegevensbestand niet verkleind omdat er onvoldoende vrije ruimte kan worden gemaakt in de huidige totale grootte van het gegevensbestand.

U kunt dit probleem op een andere manier beschouwen: 40 procent vrije ruimte + 70 procent volledig gegevensbestand (7 MB van 10 MB) is meer dan 100 procent. Elke target_percent groter dan 30 verkleint het gegevensbestand niet. Het wordt niet verkleind omdat het percentage gratis dat u wilt plus het huidige percentage dat het gegevensbestand in beslag neemt, hoger is dan 100 procent.

Voor logboekbestanden gebruikt de database-engine target_percent om de doelgrootte voor het hele logboek te berekenen. Daarom is target_percent de hoeveelheid vrije ruimte in het logboek na de verkleiningsbewerking. De doelgrootte voor het hele logboek wordt vervolgens omgezet in een doelgrootte voor elk logboekbestand.

DBCC SHRINKDATABASE probeert elk fysiek logboekbestand onmiddellijk te verkleinen tot de doelgrootte. Stel dat er geen deel van het logische logboek in de virtuele logboeken blijft, buiten de doelgrootte van het logboekbestand. Vervolgens wordt het bestand succesvol afgekapt en DBCC SHRINKDATABASE wordt zonder berichten voltooid. Als een deel van het logische logboek echter binnen de virtuele logboeken blijft en daarmee buiten de doelgrootte, maakt de databasemotor zoveel mogelijk ruimte vrij en geeft vervolgens een informatief bericht. In het bericht wordt beschreven welke acties nodig zijn om het logische logboek uit de virtuele logboeken aan het einde van het bestand te verplaatsen. Nadat de acties zijn uitgevoerd, kunt DBCC SHRINKDATABASE worden gebruikt om de resterende ruimte vrij te maken.

Een logboekbestand kan alleen worden verkleind naar een grens van een virtueel logboekbestand. Daarom is het verkleinen van een logboekbestand tot een grootte kleiner dan de grootte van een virtueel logboekbestand mogelijk niet mogelijk. Het is misschien niet mogelijk, zelfs niet als het niet wordt gebruikt. De grootte van het virtuele logboekbestand wordt dynamisch gekozen door de database-engine wanneer logboekbestanden worden gemaakt of uitgebreid.

Gelijktijdigheidsproblemen met DBCC SHRINKDATABASE begrijpen

De opdrachten voor het verkleinen van de database en het verkleinen van bestanden kunnen leiden tot gelijktijdigheidsproblemen, met name bij actief onderhoud, zoals het opnieuw opbouwen van indexen of in drukke OLTP-omgevingen. Wanneer uw toepassing query's uitvoert op databasetabellen, verkrijgen en onderhouden deze query's een stabiliteitsvergrendeling voor schema's (Sch-S) totdat de query's hun bewerkingen voltooien. Bij pogingen om tijdens normaal gebruik ruimte vrij te maken, vereisen de bewerkingen voor het verkleinen van de database en verkleiningsbestanden momenteel een vergrendeling voor schemawijziging (Sch-M) bij het verplaatsen of verwijderen van IAM-pagina's (Index Allocation Map), waardoor de Sch-S-vergrendelingen die nodig zijn voor gebruikersquery's worden geblokkeerd. Als gevolg hiervan blokkeren langlopende query's een verkleiningsbewerking totdat de query's zijn voltooid. Dit betekent dat nieuwe query's waarvoor Sch-S-slotvergrendelingen nodig zijn, ook in de wachtrij worden geplaatst achter de wachtende verkleiningsbewerking en eveneens worden geblokkeerd, waardoor het concurrentieprobleem verder wordt verergerd. Dit kan aanzienlijk van invloed zijn op de prestaties van de toepassingsquery en kan ook problemen veroorzaken bij het voltooien van het benodigde onderhoud om databasebestanden te verkleinen. Geïntroduceerd in SQL Server 2022 (16.x), de functie verkleinen met lage prioriteit (WLP) lost dit probleem op door een schemawijzigingsvergrendeling in WAIT_AT_LOW_PRIORITY modus te nemen. Zie WAIT_AT_LOW_PRIORITY met verkleiningsbewerkingenvoor meer informatie.

Zie voor meer informatie over de vergrendelingen Sch-S en Sch-M de handleiding voor transactievergrendeling en het versiebeheer van rijen: .

Beste praktijken

Houd rekening met de volgende informatie wanneer u van plan bent om een database te verkleinen:

  • Een verkleiningsbewerking is het meest effectief na een bewerking die ongebruikte ruimte creëert, zoals een trunceren van een tabel of een drop table-operatie.
  • De meeste databases vereisen dat er vrije ruimte beschikbaar is voor normale dagelijkse bewerkingen. Als u een databasebestand herhaaldelijk verkleint en merkt dat de databasegrootte opnieuw toeneemt, geeft dit aan dat de vrije ruimte vereist is voor normale bewerkingen. In deze gevallen is het herhaaldelijk verkleinen van het databasebestand een verspilde bewerking. Autogrow-gebeurtenissen die nodig zijn om het databasebestand te vergroten, belemmeren de prestaties.
  • Bij een verkleiningsbewerking blijft de fragmentatiestatus van indexen in de database niet behouden en neemt de fragmentatie over het algemeen tot een bepaalde mate toe. Dit resultaat is een andere reden om de database niet herhaaldelijk te verkleinen.
  • Tenzij u een specifieke vereiste hebt, stelt u de optie AUTO_SHRINK database niet in op AAN.

Problemen oplossen

Het is mogelijk om verkleiningsbewerkingen te blokkeren door een transactie die wordt uitgevoerd onder een isolatieniveau op basis van rijversies. Een grote verwijderbewerking die wordt uitgevoerd onder een isolatieniveau op basis van rijversiebeheer, wordt bijvoorbeeld uitgevoerd wanneer een DBCC SHRINKDATABASE bewerking wordt uitgevoerd. Wanneer deze situatie zich voordoet, wacht de verkleiningsbewerking totdat de verwijderbewerking is voltooid voordat de bestanden worden verkleind. Wanneer de verkleiningsbewerking wacht, wordt een informatief bericht afgedrukt voor de bewerkingen DBCC SHRINKFILE en DBCC SHRINKDATABASE (5202 voor SHRINKDATABASE en 5203 voor SHRINKFILE). Dit bericht wordt elke vijf minuten in het SQL Server-foutenlogboek afgedrukt in het eerste uur en vervolgens elk volgend uur. Als het foutenlogboek bijvoorbeeld het volgende foutbericht bevat:

DBCC SHRINKDATABASE for database ID 9 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

Deze fout betekent dat momentopnametransacties met tijdstempels ouder dan 109 de verkleiningsbewerking blokkeren. Deze transactie is de laatste transactie die de verkleiningsbewerking heeft voltooid. Het geeft ook de kolommen transaction_sequence_num of first_snapshot_sequence_num in de sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) aan dynamische beheerweergave een waarde van 15 bevatten. De kolom transaction_sequence_num of first_snapshot_sequence_num in de weergave kan een getal bevatten dat kleiner is dan de laatste transactie die is voltooid door een verkleiningsbewerking (109). Zo ja, dan wacht de verkleiningsbewerking totdat deze transacties zijn voltooid.

U kunt een van de volgende taken uitvoeren om het probleem op te lossen:

  • Beëindig de transactie die de verkleiningsbewerking blokkeert.
  • De verkleiningsbewerking beëindigen. Voltooid werk wordt bewaard.
  • Doe niets en laat de verkleiningsbewerking wachten totdat de blokkeringstransactie is voltooid.

Machtigingen

Vereist lidmaatschap van de sysadmin vaste serverfunctie of de db_owner vaste databaserol.

Voorbeelden

Een. Een database verkleinen en een percentage vrije ruimte opgeven

In het volgende voorbeeld wordt de grootte van de gegevens en logboekbestanden in de UserDB gebruikersdatabase verkleind om 10 procent vrije ruimte in de database mogelijk te maken.

DBCC SHRINKDATABASE (UserDB, 10);
GO

B. Een database afkappen

In het volgende voorbeeld worden de gegevens en logboekbestanden in de AdventureWorks2022 voorbeelddatabase verkleind tot aan het laatst toegewezen gedeelte.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Een Azure Synapse Analytics-database verkleinen

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

D. Een database verkleinen met WAIT_AT_LOW_PRIORITY

In het volgende voorbeeld wordt geprobeerd om de grootte van de gegevens en logboekbestanden in de AdventureWorks2022-database te verkleinen om 20% vrije ruimte in de database mogelijk te maken. Als een vergrendeling niet binnen één minuut kan worden verkregen, wordt de verkleiningsbewerking afgebroken.

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Verwante inhoud