De tempdb-database verkleinen

van toepassing op:SQL ServerAzure SQL Managed Instance

In dit artikel worden verschillende methoden besproken die u kunt gebruiken om de tempdb-database in SQL Server te verkleinen.

U kunt een van de volgende methoden gebruiken om de grootte van tempdbte wijzigen. De eerste drie opties worden beschreven in dit artikel. Als u SQL Server Management Studio (SSMS) wilt gebruiken, volgt u de instructies in Een database verkleinen.

Opmerkingen

Standaard is de tempdb-database zo nodig geconfigureerd om automatisch te groeien. Daarom kan deze database onverwacht toenemen tot een grootte die groter is dan de gewenste grootte. Grotere tempdb databasegrootten hebben geen negatieve invloed op de prestaties van SQL Server.

Wanneer SQL Server wordt gestart, wordt tempdb opnieuw gemaakt met behulp van een kopie van de model-database en wordt tempdb opnieuw ingesteld op de laatst geconfigureerde grootte. De geconfigureerde grootte is de laatste expliciete grootte die is ingesteld met behulp van een bewerking voor het wijzigen van de bestandsgrootte, zoals ALTER DATABASE die gebruikmaakt van de optie MODIFY FILE of de DBCC SHRINKFILE- of DBCC SHRINKDATABASE-instructies. Dus, tenzij u verschillende waarden moet gebruiken of onmiddellijke oplossing voor een grote tempdb-database moet verkrijgen, kunt u wachten totdat de SQL Server-service opnieuw wordt opgestart om de grootte te verkleinen.

U kunt tempdb verkleinen terwijl tempdb activiteit wordt uitgevoerd. U kunt echter andere fouten tegenkomen, zoals blokkeren, impasses, enzovoort, waardoor het niet kan worden voltooid. Daarom raden we u aan om dit tempdb te doen om ervoor te zorgen dat het succesvol is, terwijl de server zich in de modus voor één gebruiker bevindt of wanneer u alle tempdb activiteiten stopt.

SQL Server registreert slechts voldoende informatie in het tempdb transactielogboek om een transactie terug te draaien, maar niet om transacties opnieuw uit te voeren tijdens het herstel van de database. Deze functie verbetert de prestaties van INSERT statements in tempdb. Bovendien hoeft u geen gegevens te registreren om transacties opnieuw uit te voeren, omdat tempdb telkens opnieuw wordt gemaakt wanneer u SQL Server opnieuw start. Daarom heeft het geen transacties om vooruit te draaien of om terug te draaien.

Voor meer informatie over het beheren en bewaken van tempdb, zie Capaciteitsplanning en Tempdb bewaken.

Gebruik de opdracht ALTER DATABASE

Voor deze methode moet u SQL Server opnieuw starten.

1. SQL Server stoppen.

2. Start bij een opdrachtprompt het exemplaar in de minimale configuratiemodus. Voer hiervoor de volgende stappen uit:

   1. Ga bij een opdrachtprompt naar de map waarin SQL Server is geïnstalleerd (vervang <VersionNumber> en <InstanceName> in het volgende voorbeeld):

      cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn
      

   2. Als het exemplaar een benoemd exemplaar van SQL Server is, voert u de volgende opdracht uit (vervang <InstanceName> in het volgende voorbeeld):

      sqlservr.exe -s <InstanceName> -c -f -mSQLCMD
      

   3. Als het exemplaar het standaardexemplaar van SQL Server is, voert u het volgende commando uit:

      sqlservr -c -f -mSQLCMD
      

      Notitie

      De parameters -c en -f zorgen ervoor dat SQL Server in een minimale configuratiemodus wordt gestart met een tempdb grootte van 1 MB voor het gegevensbestand en 0,5 MB voor het logboekbestand. De parameter -mSQLCMD voorkomt dat een andere toepassing dan sqlcmd de verbinding met één gebruiker overneemt.

3. Maak verbinding met SQL Server met sqlcmd-en voer de volgende Transact-SQL opdrachten uit. Vervang <target_size_in_MB> door de gewenste grootte:

   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'tempdev', SIZE = <target_size_in_MB>);
   
   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'templog', SIZE = <target_size_in_MB>);
   

4. SQL Server stoppen. Als u dit wilt doen, drukt u op Ctrl+C in het opdrachtpromptvenster, start u SQL Server als een service opnieuw op en controleert u de grootte van de tempdb.mdf en daarna de templog.ldf bestanden.

De opdracht DBCC SHRINKDATABASE gebruiken

DBCC SHRINKDATABASE ontvangt de parameter target_percent. Dit is het gewenste percentage vrije ruimte in het databasebestand nadat de database is verkleind. Als u DBCC SHRINKDATABASEgebruikt, moet u SQL Server mogelijk opnieuw opstarten.

1. Bepaal de ruimte die momenteel wordt gebruikt in tempdb door de opgeslagen procedure sp_spaceused te gebruiken. Bereken vervolgens het percentage vrije ruimte dat overblijft voor gebruik als parameter voor DBCC SHRINKDATABASE. Deze berekening is gebaseerd op de gewenste databasegrootte.

   Notitie

   In sommige gevallen moet u mogelijk uitvoeren sp_spaceused @updateusage = true om de gebruikte ruimte opnieuw te berekenen en om een bijgewerkt rapport te verkrijgen. Zie sp_spaceusedvoor meer informatie.

   Bekijk het volgende voorbeeld:

   Stel dat tempdb twee bestanden heeft: het primaire gegevensbestand (tempdb.mdf) dat 1024 MB is en het logboekbestand (tempdb.ldf) dat 360 MB is. Stel dat sp_spaceused rapporteert dat het primaire gegevensbestand 600 MB aan gegevens bevat. Stel ook dat u het primaire gegevensbestand wilt verkleinen tot 800 MB. Bereken het gewenste percentage vrije ruimte na de verkleining: 800 MB - 600 MB = 200 MB. Deel nu 200 MB met 800 MB = 25 procent, en dat is uw target_percent. Het transactielogboekbestand wordt dienovereenkomstig verkleind, waardoor er 25 procent of 200 MB ruimte vrij is nadat de database is verkleind.

2. Maak verbinding met SQL Server met SSMS, Azure Data Studio of sqlcmden voer vervolgens de volgende Transact-SQL opdracht uit. Vervang <target_percent> door het gewenste percentage:

   DBCC SHRINKDATABASE (tempdb, '<target_percent>');
   

Er gelden beperkingen met de opdracht DBCC SHRINKDATABASE op tempdb. De doelgrootte voor gegevens en logboekbestanden kan niet kleiner zijn dan de grootte die is opgegeven toen de database werd gemaakt of kleiner is dan de laatste grootte die expliciet is ingesteld met behulp van een bewerking voor het wijzigen van de bestandsgrootte, zoals ALTER DATABASE die gebruikmaakt van de optie MODIFY FILE. Een andere beperking van DBCC SHRINKDATABASE is de berekening van de parameter target_percentage en de afhankelijkheid van de ruimte die momenteel in gebruik is.

De DBCC SHRINKFILE-opdracht gebruiken

Gebruik de opdracht DBCC SHRINKFILE om de afzonderlijke tempdb bestanden te verkleinen. DBCC SHRINKFILE biedt meer flexibiliteit dan DBCC SHRINKDATABASE omdat u het kunt gebruiken voor één databasebestand zonder dat dit van invloed is op andere bestanden die deel uitmaken van dezelfde database. DBCC SHRINKFILE de parameter target_size ontvangt. Dit is de gewenste uiteindelijke grootte voor het databasebestand.

1. Bepaal de gewenste grootte voor het primaire gegevensbestand (tempdb.mdf), het logboekbestand (templog.ldf) en extra bestanden die aan tempdbworden toegevoegd. Zorg ervoor dat de ruimte die wordt gebruikt in de bestanden kleiner is dan of gelijk is aan de gewenste doelgrootte.

2. Maak verbinding met SQL Server met SSMS, Azure Data Studio of sqlcmden voer vervolgens de volgende Transact-SQL opdrachten uit voor de specifieke databasebestanden die u wilt verkleinen. Vervang <target_size_in_MB> door de gewenste grootte:

   USE tempdb;
   GO
   
   -- This command shrinks the primary data file
   DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
   GO
   
   -- This command shrinks the log file, examine the last paragraph.
   DBCC SHRINKFILE (templog, '<target_size_in_MB>');
   GO
   

Een voordeel van DBCC SHRINKFILE is dat het de grootte van een bestand kan verkleinen tot een grootte die kleiner is dan de oorspronkelijke grootte. U kunt DBCC SHRINKFILE toepassen op een van de gegevens- of logboekbestanden. U kunt de database niet kleiner maken dan de grootte van de model database.

Fout 8909 bij het uitvoeren van verkleiningsbewerkingen

Als tempdb wordt gebruikt en als u deze probeert te verkleinen met behulp van de opdrachten DBCC SHRINKDATABASE of DBCC SHRINKFILE, ontvangt u mogelijk berichten die er ongeveer als volgt uitzien, afhankelijk van de versie van SQL Server die u gebruikt:

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Deze fout geeft geen echte beschadiging aan in tempdb. Er kunnen echter andere redenen zijn voor fouten met beschadigde fysieke gegevens, zoals fout 8909, en dat deze redenen problemen met het I/O-subsysteem omvatten. Als de fout zich buiten verkleiningsbewerkingen voordoet, moet u daarom verder onderzoeken.

Hoewel een 8909-bericht wordt geretourneerd naar de toepassing of naar de gebruiker die de verkleiningsbewerking uitvoert, mislukken de verkleiningsbewerkingen niet.

Verwante inhoud

• Overwegingen voor de instellingen automatisch vergroten en automatisch verkleinen in SQL Server
• Databasebestanden en bestandsgroepen
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• een database verkleinen
• DBCC SHRINKDATABASE (Transact-SQL)
• DBCC SHRINKFILE (Transact-SQL)
• gegevens of logboekbestanden uit een database verwijderen
• een bestand verkleinen