ALTER DATABASE (Transact-SQL) Bestands- en bestandsgroepopties

Wijzigt de bestanden en bestandsgroepen die aan de database zijn gekoppeld. Voegt bestanden en bestandsgroepen toe of verwijdert uit een database, en wijzigt de attributen van een database of haar bestanden en bestandsgroepen. Voor andere opties voor ALTER DATABASE, zie ALTER DATABASE.

Zie Transact-SQL syntaxisconventiesvoor meer informatie over de syntaxisconventies.

Een product selecteren

Selecteer in de volgende rij de productnaam waarin u geïnteresseerd bent en alleen de informatie van dat product wordt weergegeven.

* SQL Server *  

sql Managed Instance-

 

Syntaxis

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ]
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ]
  | REMOVE FILE logical_file_name
  | MODIFY FILE <filespec>
}

<filespec>::=
(
    NAME = logical_file_name
    [ , NEWNAME = new_logical_name ]
    [ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
    [ , OFFLINE ]
)

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option>
        | DEFAULT
        | NAME = new_filegroup_name
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE }
    | { READ_ONLY | READ_WRITE }
}

Arguments

<add_or_modify_files>::=

Specificeert het bestand dat toegevoegd, verwijderd of aangepast moet worden.

database_name Is de naam van de database die aangepast moet worden.

ADD FILE Voeg een bestand toe aan de database.

AAN BESTANDSGROEP { filegroup_name } Specificeert de bestandsgroep waaraan het opgegeven bestand moet worden toegevoegd. Om de huidige bestandsgroepen weer te geven en welke bestandsgroep de huidige standaard is, gebruik je de katalogweergave sys.filegroups .

LOG FILE TOEVOEGEN Voeg een logbestand toe dat aan de gespecificeerde database wordt toegevoegd.

VERWIJDER BESTAND logical_file_name Verwijdert de logische bestandsbeschrijving uit een instantie van SQL Server en verwijdert het fysieke bestand. Het bestand kan niet worden verwijderd tenzij het leeg is.

logical_file_name Is de logische naam die in SQL Server wordt gebruikt bij het verwijzen naar het bestand.

Waarschuwing

Het verwijderen van een databasebestand met FILE_SNAPSHOT back-ups eraan gekoppeld zal slagen, maar eventuele bijbehorende snapshots worden niet verwijderd om te voorkomen dat de back-ups die naar het databasebestand verwijzen ongeldig worden gemaakt. Het bestand wordt ingekort, maar wordt niet fysiek verwijderd om de FILE_SNAPSHOT back-ups intact te houden. Zie BACK-up en herstel van SQL Server met Microsoft Azure Blob Storage voor meer informatie. Van toepassing op: SQL Server (SQL Server 2016 (13.x) en later).

WIJZIGEN BESTAND Specificeert welk bestand moet worden gewijzigd. Er kan slechts één <filespec-eigenschap> tegelijk worden gewijzigd. NAME moet altijd in de <bestandsspecificatie> worden gespecificeerd om het te wijzigen bestand te identificeren. Als SIZE is opgegeven, moet de nieuwe grootte groter zijn dan de huidige bestandsgrootte.

Om de logische naam van een databestand of logbestand te wijzigen, specificeer je de logische bestandsnaam die in de NAME clausule moet worden hernoemd, en geef je de nieuwe logische naam voor het bestand in de NEWNAME clausule op. Voorbeeld:

MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

Om een databestand of logbestand naar een nieuwe locatie te verplaatsen, specificeer je de huidige logische bestandsnaam in de NAME clausule en geef je het nieuwe pad en de bestandsnaam van het besturingssysteem in de FILENAME clausule op. Voorbeeld:

MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

Wanneer je een full-text catalogus verplaatst, specificeer dan alleen het nieuwe pad in de FILENAME-clausule. Specificeer niet de bestandsnaam van het besturingssysteem.

Voor meer informatie, zie Databasebestanden verplaatsen.

Voor een FILESTREAM-bestandsgroep kan NAME online worden aangepast. FILENAME kan online worden gewijzigd; De wijziging treedt echter pas in nadat de container fysiek is verplaatst en de server is uitgeschakeld en vervolgens opnieuw is opgestart.

Je kunt een FILESTREAM-bestand op OFFLINE zetten. Wanneer een FILESTREAM-bestand offline is, wordt de ouderbestandgroep intern als offline gemarkeerd; daarom zal alle toegang tot FILESTREAM-gegevens binnen die bestandsgroep mislukken.

Opmerking

<add_or_modify_files> opties zijn niet beschikbaar in een Gesloten Database.

<filespec>::=

Hiermee bepaalt u de bestandseigenschappen.

NAAM logical_file_name Specificeert de logische naam van het bestand.

logical_file_name Is de logische naam die wordt gebruikt in een instantie van SQL Server bij het refereren naar het bestand.

NEWNAME new_logical_file_name Specificeert een nieuwe logische naam voor het bestand.

new_logical_file_name Is de naam om de bestaande logische bestandsnaam te vervangen. De naam moet uniek zijn binnen de database en voldoen aan de regels voor identificaties. De naam kan een karakter- of Unicode-constante zijn, een reguliere identificatie, of een gescheiden identificatie.

BESTANDSNAAM { 'os_file_name' | 'filestream_path' | 'memory_optimized_data_path'} Specificeert de bestandsnaam van het besturingssysteem (fysieke).

' os_file_name ' Voor een standaard (ROWS) bestandsgroep is dit het pad en de bestandsnaam die het besturingssysteem gebruikt wanneer je het bestand aanmaakt. Het bestand moet zich bevinden op de server waarop SQL Server is geïnstalleerd. Het opgegeven pad moet bestaan voordat de ALTER DATABASE-instructie wordt uitgevoerd.

Opmerking

SIZE, MAXSIZE, en FILEGROWTH parameters kunnen niet worden ingesteld wanneer een UNC-pad voor het bestand is gespecificeerd.

Systeemdatabases kunnen niet in UNC-sharedirectories staan.

Databestanden mogen niet op gecomprimeerde bestandssystemen worden geplaatst, tenzij de bestanden secundaire bestanden zijn, of als de database alleen-lezen is. Logboekbestanden mogen nooit op gecomprimeerde bestandssystemen worden geplaatst.

Als het bestand zich op een onbewerkte partitie bevindt, moet os_file_name alleen de stationsletter van een bestaande onbewerkte partitie opgeven. Er kan slechts één bestand op elke ruwe partitie worden gezet.

'filestream_path' Voor een FILESTREAM-bestandsgroep verwijst FILENAME naar een pad waar FILESTREAM-gegevens worden opgeslagen. Het pad naar de laatste map moet bestaan en de laatste map mag niet bestaan. Als je bijvoorbeeld het pad C:\MyFiles\MyFilestreamDataspecificeert, moet er C:\MyFiles bestaan voordat je ALTER DATABASE uitvoert, maar de MyFilestreamData map mag niet bestaan.

Opmerking

De eigenschappen SIZE en FILEGROWTH zijn niet van toepassing op een FILESTREAM-bestandsgroep.

'memory_optimized_data_path' Voor een geheugengeoptimaliseerde bestandsgroep verwijst FILENAME naar een pad waarop geheugengeoptimaliseerde gegevens worden opgeslagen. Het pad naar de laatste map moet bestaan en de laatste map mag niet bestaan. Als je bijvoorbeeld het pad C:\MyFiles\MyDataspecificeert, moet er C:\MyFiles bestaan voordat je ALTER DATABASE uitvoert, maar de MyData map mag niet bestaan.

De bestandsgroep en het bestand (<filespec>) moeten in dezelfde instructie worden gemaakt.

Opmerking

De eigenschappen SIZE en FILEGROWTH zijn niet van toepassing op een MEMORY_OPTIMIZED_DATA bestandsgroep.

Voor meer informatie over geheugen-geoptimaliseerde bestandsgroepen, zie The Memory Optimized File group.

GROOTTE Specificeert de bestandsgrootte. SIZE is niet van toepassing op FILESTREAM-bestandsgroepen.

grootte Is de grootte van het bestand.

Wanneer opgegeven met ADD FILE, is de grootte de begingrootte van het bestand. Wanneer opgegeven met MODIFY FILE, is de grootte de nieuwe grootte voor het bestand en moet groter zijn dan de huidige bestandsgrootte.

Wanneer de grootte niet wordt opgegeven voor het primaire bestand, gebruikt de SQL Server de grootte van het primaire bestand in de modeldatabase . Wanneer een secundair databestand of logbestand wordt gespecificeerd maar de grootte niet is opgegeven, maakt de Database Engine het bestand 1 MB.

De achtervoegsels KB, MB, GB en TB kunnen worden gebruikt om kilobytes, megabytes, gigabytes of terabytes te specificeren. De standaardwaarde is MB. Geef een heel getal op en voeg geen decimaal toe. Om een fractie van een megabyte te specificeren, converteer je de waarde naar kilobytes door het getal te vermenigvuldigen met 1024. Geef bijvoorbeeld 1536 KB aan in plaats van 1,5 MB (1,5 x 1024 = 1536).

Opmerking

SIZE kan niet worden ingesteld:

• Wanneer een UNC-pad wordt gespecificeerd voor het bestand
• Voor FILESTREAM en MEMORY_OPTIMIZED_DATA bestandsgroepen

MAXSIZE { max_size| UNLIMITED } Specificeert de maximale bestandsgrootte waartoe het bestand kan groeien.

max_size Is de maximale bestandsgrootte. De achtervoegsels KB, MB, GB en TB kunnen worden gebruikt om kilobytes, megabytes, gigabytes of terabytes te specificeren. De standaardwaarde is MB. Geef een heel getal op en voeg geen decimaal toe. Als max_size niet wordt gespecificeerd, neemt de bestandsgrootte toe totdat de schijf vol is.

UNLIMITED Geeft aan dat het bestand groeit totdat de schijf vol is. In SQL Server heeft een logboekbestand dat is opgegeven met onbeperkte groei een maximale grootte van 2 TB en een gegevensbestand een maximale grootte van 16 TB heeft. Er is geen maximale grootte wanneer deze optie is gespecificeerd voor een FILESTREAM-container. Het blijft groeien totdat de schijf vol is.

Opmerking

MAXSIZE kan niet worden ingesteld wanneer een UNC-pad voor het bestand is opgegeven.

FILEGROWTH growth_increment Specificeert de automatische groeiverhoging van het bestand. De FILEGROWTH-instelling voor een bestand mag de MAXSIZE-instelling niet overschrijden. FILEGROWTH is niet van toepassing op FILESTREAM-bestandsgroepen.

growth_increment Is de hoeveelheid ruimte die aan het bestand wordt toegevoegd telkens wanneer er nieuwe ruimte nodig is.

De waarde kan worden opgegeven in MB, KB, GB, TB of procent (%). Als een nummer wordt opgegeven zonder MB, KB of % achtervoegsel, is de standaard MB. Wanneer % wordt gespecificeerd, is de groei-incrementgrootte het gespecificeerde percentage van de bestandsgrootte op het moment dat de increment plaatsvindt. De opgegeven grootte wordt afgerond op de dichtstbijzijnde 64 KB.

Een waarde van 0 geeft aan dat automatische groei uit staat en dat er geen extra ruimte is toegestaan.

Als FILEGROWTH niet is gespecificeerd, zijn de standaardwaarden:

Versie	Standaardwaarden
Vanaf SQL Server 2016 (13.x)	Gegevens 64 MB. Logboekbestanden van 64 MB.
Vanaf SQL Server 2005 (9.x)	Gegevens 1 MB. Logboekbestanden 10%.
Vóór SQL Server 2005 (9.x)	Gegevens 10%. Logboekbestanden 10%.

Opmerking

FILEGROWTH kan niet worden ingesteld:

• Wanneer een UNC-pad wordt gespecificeerd voor het bestand
• Voor FILESTREAM en MEMORY_OPTIMIZED_DATA bestandsgroepen

OFFLINE Zet het bestand offline en maakt alle objecten in de bestandsgroep ontoegankelijk.

Waarschuwing

Gebruik deze optie alleen wanneer het bestand beschadigd is en hersteld kan worden. Een bestand dat op OFFLINE is gezet, kan alleen online worden gezet door het bestand uit de back-up te herstellen. Voor meer informatie over het herstellen van een enkel bestand, zie RESTORE.

<filespec-opties> zijn niet beschikbaar in een Contained Database.

<add_or_modify_filegroups>::=

Voeg een bestandsgroep toe, verander of verwijder een bestandsgroep uit de database.

FILEGROUP TOEVOEGEN filegroup_name Voegt een filegroup toe aan de database.

CONTAINS FILESTREAM Specificeert dat de bestandsgroep FILESTREAM binary large objects (BLOBs) opslaat in het bestandssysteem.

BEVAT MEMORY_OPTIMIZED_DATA

Van toepassing op: SQL Server (SQL Server 2014 (12.x) en later)

Specificeert dat de bestandsgroep geheugengeoptimaliseerde gegevens opslaat in het bestandssysteem. Voor meer informatie, zie In-Memory OLTP - In-Memory Optimization. Er is slechts één MEMORY_OPTIMIZED_DATA bestandsgroep per database toegestaan. Voor het maken van geheugengeoptimaliseerde tabellen kan de bestandsgroep niet leeg zijn. Er moet minstens één bestand zijn. filegroup_name verwijst naar een pad. Het pad naar de laatste map moet bestaan en de laatste map mag niet bestaan.

VERWIJDER BESTANDSGROEP filegroup_name Verwijdert een bestandsgroep uit de database. De bestandsgroep kan niet worden verwijderd tenzij deze leeg is. Verwijder eerst alle bestanden uit de bestandsgroep. Voor meer informatie, zie "REMOVE FILE logical_file_name," eerder in dit onderwerp.

Opmerking

Tenzij de FILESTREAM Garbage Collector alle bestanden uit een FILESTREAM-container heeft verwijderd, zal de ALTER DATABASE REMOVE FILE bewerking om een FILESTREAM-container te verwijderen falen en een foutmelding geven. Zie later in dit onderwerp de sectie 'Een FILESTREAM Container verwijderen '.

WIJZIGEN FILEGROUP filegroup_name { <filegroup_updatability_option> | STANDAARD | NAAM =new_filegroup_name } Wijzigt de bestandsgroep door de status in te stellen op READ_ONLY of READ_WRITE, waardoor de bestandsgroep de standaard bestandsgroep voor de database wordt, of de bestandsgroepnaam wordt gewijzigd.

<filegroup_updatability_option> Stelt de read-only of read/write-eigenschap in op de filegroup.

DEFAULT Verandert de standaard databasebestandsgroep naar filegroup_name. Slechts één bestandsgroep in de database kan de standaard bestandsgroep zijn. Voor meer informatie, zie Database Files and Filegroups.

NAAM = new_filegroup_name Verandert de bestandsgroepnaam naar de new_filegroup_name.

AUTOGROW_SINGLE_FILE Van toepassing op: SQL Server (SQL Server 2016 (13.x) en later)

Wanneer een bestand in de bestandsgroep de autogrow-drempel bereikt, groeit alleen dat bestand. Dit is de standaardwaarde.

AUTOGROW_ALL_FILES

Van toepassing op: SQL Server (SQL Server 2016 (13.x) en later)

Wanneer een bestand in de bestandsgroep voldoet aan de drempelwaarde voor automatisch groeien, worden alle bestanden in de bestandsgroep groter.

Opmerking

Dit is de standaardwaarde voor TempDB.

<filegroup_updatability_option>::=

Stelt de read-only of read/write-eigenschap in op de filegroup.

READ_ONLY | READONLY Geeft aan dat de bestandsgroep alleen-lezen is. Updates aan objecten daarin zijn niet toegestaan. De primaire bestandsgroep kan niet alleen-lezen worden gemaakt. Als u deze status wilt wijzigen, moet u exclusieve toegang tot de database hebben. Zie de SINGLE_USER-component voor meer informatie.

Omdat een alleen-lezen database geen datawijzigingen toestaat:

• Automatisch herstel wordt overgeslagen bij het opstarten van het systeem.
• Het verkleinen van de database is niet mogelijk.
• Er treedt geen vergrendeling op in alleen-lezen databases. Dit kan zorgen voor snellere queryprestaties.

Opmerking

Het trefwoord READONLY zal worden verwijderd in een toekomstige versie van Microsoft SQL Server. Vermijd gebruik READONLY in nieuw ontwikkelingswerk en plan om applicaties aan te passen die momenteel .READONLY Gebruik in plaats daarvan READ_ONLY.

READ_WRITE | READWRITE Specificeert dat de groep READ_WRITE is. Updates zijn ingeschakeld voor de objecten in de bestandsgroep. Als u deze status wilt wijzigen, moet u exclusieve toegang tot de database hebben. Zie de SINGLE_USER-component voor meer informatie.

Opmerking

Het trefwoord READWRITE zal worden verwijderd in een toekomstige versie van Microsoft SQL Server. Vermijd gebruik READWRITE in nieuw ontwikkelingswerk en plan om applicaties die nu gebruikt READWRITE worden aan te READ_WRITE passen.

Aanbeveling

De status van deze opties kan worden bepaald door de kolom is_read_only in de sys.databases-catalogusweergave of de Updateability-eigenschap van de DATABASEPROPERTYEX functie te bekijken.

Opmerkingen

Als u de grootte van een database wilt verkleinen, gebruikt u DBCC SHRINKDATABASE.

Je kunt een bestand niet toevoegen of verwijderen terwijl een BACKUP statement draait.

Voor elke database kunnen maximaal 32.767 bestanden en 32.767 bestandsgroepen worden opgegeven.

Vanaf SQL Server 2005 (9.x) wordt de status van een databasebestand (bijvoorbeeld online of offline) onafhankelijk onderhouden van de status van de database. Zie Bestandsstatussenvoor meer informatie.

• De status van de bestanden in een bestandsgroep bepaalt de beschikbaarheid van de hele bestandsgroep. Als een bestandsgroep beschikbaar is, moeten alle bestanden in de bestandsgroep online zijn.
• Als een bestandsgroep offline is, zal elke poging om de bestandsgroep te benaderen via een SQL-instructie mislukken met een foutmelding. Wanneer je queryplannen voor SELECT statements bouwt, vermijdt de queryoptimizer niet-geclusterde indexen en geïndexeerde weergaven die zich in offline bestandsgroepen bevinden. Hierdoor kunnen deze instructies slagen. Als de offline bestandsgroep echter de heap of geclusterde index van de doeltabel bevat, falen de SELECT instructies. Daarnaast zal elke INSERT, UPDATE, of DELETE instructie die een tabel met een index in een offline bestandsgroep wijzigt, falen.

SIZE, MAXSIZE en FILEGROWTH-parameters kunnen niet worden ingesteld wanneer een UNC-pad voor het bestand is gespecificeerd.

SIZE- en FILEGROWTH-parameters kunnen niet worden ingesteld voor geheugengeoptimaliseerde bestandsgroepen.

Het trefwoord READONLY zal worden verwijderd in een toekomstige versie van Microsoft SQL Server. Vermijd gebruik READONLY in nieuw ontwikkelingswerk en plan om applicaties aan te passen die momenteel READONLY gebruiken. Gebruik in plaats daarvan READ_ONLY.

Het trefwoord READWRITE zal worden verwijderd in een toekomstige versie van Microsoft SQL Server. Vermijd gebruik READWRITE in nieuw ontwikkelingswerk en plan om applicaties die nu gebruikt READWRITE worden aan te READ_WRITE passen.

Bestanden verplaatsen

Je kunt systeem- of door de gebruiker gedefinieerde gegevens en logbestanden verplaatsen door de nieuwe locatie in FILENAME op te geven. Dit kan nuttig zijn in de volgende scenario's:

• Herstel van mislukking. Bijvoorbeeld, de database staat in verdachte modus of wordt uitgeschakeld door hardwarefouten.
• Geplande verhuizing.
• Verplaatsing voor gepland schijfonderhoud.

Voor meer informatie, zie Databasebestanden verplaatsen.

Bestanden initialiseren

Standaard worden gegevens en logbestanden geïnitialiseerd door de bestanden met nullen te vullen wanneer je een van de volgende bewerkingen uitvoert:

• Maak een database.
• Voeg bestanden toe aan een bestaande database.
• Vergroot de grootte van een bestaand bestand.
• Herstel een database of bestandsgroep.

Databestanden kunnen onmiddellijk worden geïnitialiseerd. Dit maakt snelle uitvoering van deze bestandsbewerkingen mogelijk. Zie Initialisatie van databasebestand voor meer informatie.

Verwijderen van een FILESTREAM-container

Hoewel de FILESTREAM-container mogelijk is geleegd met de "DBCC SHRINKFILE"-operatie, moet de database mogelijk nog steeds verwijzingen naar de verwijderde bestanden behouden vanwege verschillende systeemonderhoudsredenen. sp_filestream_force_garbage_collection zal de FILESTREAM Garbage Collector uitvoeren om deze bestanden te verwijderen wanneer het veilig is. Tenzij de FILESTREAM Garbage Collector alle bestanden uit een FILESTREAM-container heeft verwijderd, zal de ALTER DATABASE REMOVE FILE-operatie falen in het verwijderen van een FILESTREAM-container en een foutmelding geven. Het volgende proces wordt aanbevolen om een FILESTREAM-container te verwijderen.

1. Voer DBCC SHRINKFILE uit met de optie EMPTYFILE om de actieve inhoud van deze container naar andere containers te verplaatsen.
2. Zorg ervoor dat er logback-ups zijn gemaakt, in het VOLLEDIGE of BULK_LOGGED herstelmodel.
3. Zorg ervoor dat de replicatieloglezer-taak is uitgevoerd, indien relevant.
4. Voer sp_filestream_force_garbage_collection uit om de garbage collector te dwingen bestanden te verwijderen die niet meer nodig zijn in deze container.
5. Voer ALTER DATABASE uit met de optie REMOVE FILE om deze container te verwijderen.
6. Herhaal stappen 2 tot en met 4 nog een keer om de afvalverzameling te voltooien.
7. Gebruik de ALTER-database... REMOVE FILE om deze container te verwijderen.

Voorbeelden

Eén. Een bestand toevoegen aan een database

Het volgende voorbeeld voegt een databestand van 5 MB toe aan de AdventureWorks2025-database.

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B. Een bestandsgroep met twee bestanden toevoegen aan een database

Het volgende voorbeeld maakt de bestandsgroep Test1FG1 aan in de AdventureWorks2025-database en voegt twee bestanden van 5 MB toe aan de bestandsgroep.

USE master
GO
ALTER DATABASE AdventureWorks2022
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),  
(  
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

C. Twee logbestanden toevoegen aan een database

Het volgende voorbeeld voegt twee logbestanden van 5 MB toe aan de AdventureWorks2025-database.

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD LOG FILE
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D. Een bestand uit een database verwijderen

Het volgende voorbeeld verwijdert een van de bestanden die in voorbeeld B zijn toegevoegd.

USE master;
GO
ALTER DATABASE AdventureWorks2022
REMOVE FILE test1dat4;
GO

E. Een bestand aanpassen

Het volgende voorbeeld vergroot de grootte van een van de bestanden die in voorbeeld B zijn toegevoegd. De ALTER DATABASE met het commando MODIFY FILE kan alleen een bestandsgrootte vergroten, dus als je de bestandsgrootte moet verkleinen moet je DBCC SHRINKFILE gebruiken.

USE master;
GO

ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

Dit voorbeeld verkleint de grootte van een databestand tot 100 MB en specificeert vervolgens de grootte op dat bedrag.

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

USE master;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

F. Een bestand naar een nieuwe locatie verplaatsen

Het volgende voorbeeld verplaatst het Test1dat2 bestand dat in voorbeeld A is aangemaakt naar een nieuwe map.

Opmerking

Je moet het bestand fysiek naar de nieuwe map verplaatsen voordat je dit voorbeeld uitvoert. Daarna stop en start je de instantie van SQL Server of zet je de AdventureWorks2025 database OFFLINE en daarna ONLINE om de wijziging te implementeren.

USE master;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. Tempdb verplaatsen naar een nieuwe locatie

Het volgende voorbeeld verplaatst tempdb zich van de huidige locatie op de schijf naar een andere schijflocatie. Omdat tempdb het elke keer opnieuw wordt aangemaakt wanneer de MSSQLSERVER-service wordt gestart, hoef je de data en logbestanden niet fysiek te verplaatsen. De bestanden worden aangemaakt wanneer de dienst wordt herstart in stap 3. Totdat de dienst wordt hervat tempdb , blijft deze functioneren op de huidige locatie.

1. Bepaal de logische bestandsnamen van de database en hun huidige locatie op de tempdb schijf.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   GO
   

2. Verander de locatie van elk bestand door gebruik te maken van ALTER DATABASE.

   USE master;
   GO
   ALTER DATABASE tempdb
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE tempdb
   MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
   GO
   

3. Stop en herstart de instantie van SQL Server.

4. Controleer de bestandswijziging.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   

5. Verwijder de bestanden tempdb.mdf en templog.ldf van hun oorspronkelijke locatie.

H. Een bestandsgroep als standaard maken

Het volgende voorbeeld maakt de Test1FG1 bestandsgroep die in voorbeeld B is aangemaakt tot de standaard bestandsgroep. Vervolgens wordt de standaard bestandsgroep teruggezet naar de PRIMARY bestandsgroep. Let op dat PRIMARY deze tussen haakjes of aanhalingstekens moet worden afgebakend.

USE master;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. Een bestandsgroep toevoegen met behulp van ALTER DATABASE

Het volgende voorbeeld voegt een FILEGROUP toe die de FILESTREAM clausule bevat aan de FileStreamPhotoDB database.

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause.
ALTER DATABASE FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM;
GO

--Add a file for storing database photos to FILEGROUP
ALTER DATABASE FileStreamPhotoDB
ADD FILE
(
  NAME= 'PhotoShoot1',
  FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot;
GO

Het volgende voorbeeld voegt een FILEGROUP toe die de MEMORY_OPTIMIZED_DATA clausule bevat aan de xtp_db database. De bestandsgroep slaat geheugengeoptimaliseerde gegevens op.

--Create and add a FILEGROUP that CONTAINS the MEMORY_OPTIMIZED_DATA clause.
ALTER DATABASE xtp_db
ADD FILEGROUP xtp_fg
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

--Add a file for storing memory optimized data to FILEGROUP
ALTER DATABASE xtp_db
ADD FILE
(
  NAME='xtp_mod',
  FILENAME='d:\data\xtp_mod'
)
TO FILEGROUP xtp_fg;
GO

J. Verander de bestandsgroep zodat wanneer een bestand in de bestandsgroep de autogrow-drempel bereikt, alle bestanden in de bestandsgroep groeien

Het volgende voorbeeld genereert de benodigde ALTER DATABASE statements om read-write-bestandsgroepen met de AUTOGROW_ALL_FILES instelling te wijzigen.

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements
--so that all read-write filegroups grow at the same time.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, isdone BIT);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, fgname sysname, isdone BIT);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid INT, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
  SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

  SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
  INSERT INTO #tmpfgs
  EXEC (@query)

  UPDATE #tmpdbs
  SET isdone = 1
  WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
  WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
  BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

    SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

    PRINT @query

    UPDATE #tmpfgs
    SET isdone = 1
    WHERE [dbid] = @dbid AND fgname = @fgname
  END
END;
GO

Zie ook

• Een DATABASE maken
• DATABASEPROPERTYEX-
• DROP DATABASE-
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• Binaire Grote Objecten
• DBCC SHRINKFIL
• sp_filestream_force_garbage_collection
• Databasebestand-initialisatie