Opzioni per file e filegroup ALTER DATABASE (Transact-SQL)

* SQL Server *  

Istanza gestita di SQL

 

Sintassi

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 }
}

Argomenti

<add_or_modify_files>::=

Specifica i file da aggiungere, rimuovere o modificare.

database_name è il nome del database da modificare.

ADD FILE aggiunge un file al database.

TO FILEGROUP { filegroup_name } specifica il filegroup in cui aggiungere il file specificato. Per visualizzare i filegroup correnti e determinare il filegroup attualmente predefinito, usare la vista del catalogo sys.filegroups.

ADD LOG FILE aggiunge un file di log al database specificato.

REMOVE FILE logical_file_name rimuove la descrizione del file logico da un'istanza di SQL Server ed elimina il file fisico. Il file può essere rimosso solo se è vuoto.

logical_file_name è il nome logico usato in SQL Server per fare riferimento al file.

MODIFY FILE specifica il file da modificare. È possibile cambiare una sola proprietà <filespec> alla volta. L'opzione NAME deve essere sempre specificata in <filespec> per identificare il file da modificare. Se si specifica l'opzione SIZE, le nuove dimensioni del file devono essere superiori a quelle correnti.

Per modificare il nome logico di un file di dati o di un file di log, specificare il nome del file logico da rinominare nella clausola NAME e specificare il nuovo nome logico per il file nella clausola NEWNAME. Ad esempio:

MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

Per spostare un file di dati o un file di log in una nuova posizione, specificare il nome di file logico corrente nella clausola NAME e specificare il nuovo percorso e il nome del file nel sistema operativo nella clausola FILENAME. Ad esempio:

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

Per lo spostamento di un catalogo full-text, specificare solo il nuovo percorso nella clausola FILENAME, senza indicare il nome del file nel sistema operativo.

Per altre informazioni, vedere Spostare file del database.

Per un filegroup FILESTREAM, NAME può essere modificato online. Sebbene FILENAME possa essere modificato online, la modifica non diventa effettiva fino a quando il contenitore non viene rilocato fisicamente e il server non viene arrestato e successivamente riavviato.

È possibile impostare un file FILESTREAM su OFFLINE. Quando un file FILESTREAM è offline, il filegroup padre sarà contrassegnato internamente come offline. Di conseguenza, ogni accesso a dati FILESTREAM all'interno del filegroup specifico avrà esito negativo.

<filespec>::=

Controlla le proprietà del file.

NAME logical_file_name specifica il nome logico del file.

logical_file_name è il nome logico usato in un'istanza di SQL Server per fare riferimento al file.

NEWNAME new_logical_file_name specifica un nuovo nome logico per il file.

new_logical_file_name è il nome da sostituire al nome del file logico esistente. Il nome deve essere univoco all'interno del database e conforme alle regole per gli identificatori. Il nome può essere costituito da una costante per valori di carattere o Unicode, da un identificatore regolare o da un identificatore delimitato.

FILENAME { 'os_file_name' | 'filestream_path' | 'memory_optimized_data_path'} Specifica il nome del file (fisico) del sistema operativo.

' os_file_name ' specifica, per un filegroup standard (ROWS), il percorso e il nome file usati dal sistema operativo quando si crea il file. Il file deve risiedere nel server in cui è installato SQL Server. Il percorso specificato deve essere esistente prima di eseguire l'istruzione ALTER DATABASE.

I file di dati non dovrebbero essere memorizzati in file system compressi, a meno che tali file non siano file secondari di sola lettura o il database non sia di sola lettura. I file di log non devono mai essere archiviati in file system compressi.

Se il file si trova in una partizione non formattata, nell'argomento os_file_name è necessario specificare solo la lettera dell'unità di una partizione non formattata esistente. In ogni partizione non formattata è possibile inserire un solo file.

' filestream_path ': per un filegroup FILESTREAM, FILENAME fa riferimento a un percorso in cui verranno archiviati i dati di FILESTREAM. È necessario che il percorso fino all'ultima cartella esista già, mentre l'ultima cartella non deve essere presente. Se, ad esempio, si specifica il percorso C:\MyFiles\MyFilestreamData, C:\MyFiles deve esistere già prima di eseguire ALTER DATABASE, mentre la cartella MyFilestreamData non deve essere presente.

' memory_optimized_data_path ': per un filegroup ottimizzato per la memoria, FILENAME fa riferimento a un percorso in cui verranno archiviati i dati ottimizzati per la memoria. È necessario che il percorso fino all'ultima cartella esista già, mentre l'ultima cartella non deve essere presente. Se, ad esempio, si specifica il percorso C:\MyFiles\MyData, C:\MyFiles deve esistere già prima di eseguire ALTER DATABASE, mentre la cartella MyData non deve essere presente.

Il filegroup e il file (<filespec>) devono essere creati nella stessa istruzione.

Per altre informazioni sui filegroup ottimizzati per la memoria, vedere Filegroup ottimizzato per la memoria.

SIZE size specifica le dimensioni del file. SIZE non si applica a filegroup FILESTREAM.

size corrisponde alle dimensioni del file.

Quando si specifica con ADD FILE, size corrisponde alle dimensioni iniziali del file. Se si specifica con MODIFY FILE, size corrisponde alle nuove dimensioni del file, che devono essere superiori a quelle correnti.

Se non si specifica size per il file primario, SQL Server usa le dimensioni del file primario del database model. Se si specifica un file di dati o un file di log secondario senza specificare size, il motore di database crea un file di 1 MB.

È possibile utilizzare i suffissi KB, MB, GB e TB per indicare kilobyte, megabyte, gigabyte e terabyte. Il valore predefinito è MB. Specificare un numero intero, ovvero non includere decimali. Se si desidera specificare una frazione di megabyte, convertire il valore in kilobyte moltiplicando il numero per 1024. Ad esempio, specificare 1536 KB anziché 1,5 MB (1,5 x 1024 = 1536).

MAXSIZE { max_size| UNLIMITED } specifica le dimensioni massime consentite per il file.

max_size corrisponde alle dimensioni massime del file. È possibile utilizzare i suffissi KB, MB, GB e TB per indicare kilobyte, megabyte, gigabyte e terabyte. Il valore predefinito è MB. Specificare un numero intero, ovvero non includere decimali. Se non si specifica max_size, le dimensioni del file aumenteranno fino a quando il disco risulta pieno.

UNLIMITED specifica che le dimensioni del file aumentano fino a quando il disco risulta pieno. In SQL Server un file di log specificato con aumento delle dimensioni illimitato può raggiungere dimensioni massime di 2 TB, mentre le dimensioni massime di un file di dati sono di 16 TB. Non vi sono dimensioni massime se questa opzione viene specificata per un contenitore FILESTREAM, il quale continua a crescere finché il disco non è pieno.

FILEGROWTH growth_increment specifica l'incremento automatico per l'aumento delle dimensioni del file. Il valore impostato per il parametro FILEGROWTH di un file non può essere superiore al valore del parametro MAXSIZE. FILEGROWTH non si applica a filegroup FILESTREAM.

growth_increment è la quantità di spazio aggiunta al file ogni volta che è necessario altro spazio.

È possibile specificare il valore in megabyte (MB), kilobyte (KB), gigabyte (GB) o terabyte (TB) oppure in forma di percentuale (%). Se si specifica un valore senza il suffisso MB, KB o %, il suffisso predefinito è MB. Se si utilizza il suffisso %, l'incremento corrisponde alla percentuale delle dimensioni del file specificata quando si verifica l'incremento. Le dimensioni specificate vengono arrotondate al blocco di 64 KB più prossimo.

Il valore 0 indica che l'aumento automatico delle dimensioni è disattivato e non è consentita l'allocazione di spazio aggiuntivo.

Se FILEGROWTH viene omesso, i valori predefiniti sono i seguenti:

Versione	Valori predefiniti
A partire da SQL Server 2016 (13.x)	Dati 64 MB. File di log 64 MB.
A partire da SQL Server 2005 (9.x)	Dati 1 MB. File di log 10%.
Prima di SQL Server 2005 (9.x)	Dati 10%. File di log 10%.

OFFLINE imposta il file offline e rende inaccessibili tutti gli oggetti nel filegroup.

<add_or_modify_filegroups>::=

Aggiunge, modifica o rimuove un filegroup nel database.

ADD FILEGROUP filegroup_name aggiunge un filegroup al database.

CONTAINS FILESTREAM specifica che il filegroup archivia BLOB FILESTREAM nel file system.

CONTAINS MEMORY_OPTIMIZED_DATA

Si applica a: SQL Server 2014 (12.x) e versioni successive

Specifica che il filegroup archivia i dati con ottimizzazione per la memoria nel file system. Per altre informazioni, vedere OLTP in memoria - Ottimizzazione per la memoria. È consentito un solo filegroup MEMORY_OPTIMIZED_DATA per ogni database. Per la creazione di tabelle con ottimizzazione per la memoria, il filegroup non può essere vuoto. Deve essere presente almeno un file. filegroup_name fa riferimento a un percorso. È necessario che il percorso fino all'ultima cartella esista già, mentre l'ultima cartella non deve essere presente.

REMOVE FILEGROUP filegroup_name rimuove un filegroup dal database. Il filegroup può essere rimosso solo se è vuoto. Rimuove tutti i file a partire dal filegroup. Per altre informazioni, vedere "REMOVE FILE logical_file_name" più indietro in questo argomento.

MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME =new_filegroup_name } Modifica il filegroup impostando lo stato su READ_ONLY o READ_WRITE, impostando il filegroup come predefinito per il database o cambiando il nome del filegroup.

<filegroup_updatability_option> imposta la proprietà di sola lettura o di lettura/scrittura per il filegroup.

DEFAULT cambia il filegroup predefinito del database in filegroup_name. In un database può esistere un solo filegroup predefinito. Per altre informazioni, vedere Database Files and Filegroups.

NAME = new_filegroup_name cambia il nome del filegroup in new_filegroup_name.

AUTOGROW_SINGLE_FILE Si applica a: SQL Server 2016 (13.x) e versioni successive

Quando un file del filegroup raggiunge la soglia dell'aumento automatico delle dimensioni, vengono aumentate le dimensioni solo di quel file. Si tratta dell'impostazione predefinita.

AUTOGROW_ALL_FILES

Si applica a: SQL Server 2016 (13.x) e versioni successive

Quando un file del filegroup raggiunge la soglia dell'aumento automatico delle dimensioni, vengono aumentate le dimensioni di tutti i file del filegroup.

<filegroup_updatability_option>::=

Imposta la proprietà di sola lettura o di lettura/scrittura per il filegroup.

READ_ONLY | READONLY specifica che il filegroup è di sola lettura. Non sono consentiti aggiornamenti degli oggetti nel filegroup. Non è possibile rendere di sola lettura il filegroup primario. Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per altre informazioni, vedere la clausola SINGLE_USER.

I database di sola lettura non consentono modifiche dei dati e pertanto:

• Non viene eseguito il recupero automatico all'avvio del sistema.
• La compattazione del database non è possibile.
• Non vengono attivati blocchi nei database di sola lettura e ciò può portare a migliori prestazioni di esecuzione delle query.

READ_WRITE | READWRITE specifica che il filegroup è di lettura/scrittura. Sono consentiti aggiornamenti degli oggetti contenuti nel filegroup. Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per altre informazioni, vedere la clausola SINGLE_USER.

Osservazioni:

Per ridurre le dimensioni di un database, usare DBCC SHRINKDATABASE.

Non è possibile aggiungere o rimuovere un file durante l'esecuzione di un'istruzione BACKUP.

Per ogni database è possibile specificare un massimo di 32.767 file e 32.767 filegroup.

A partire da SQL Server 2005 (9.x) lo stato di un file di database, ad esempio online o offline, viene mantenuto indipendentemente dallo stato del database. Per altre informazioni, vedere Stati del file.

• Lo stato dei file all'interno di un filegroup determina la disponibilità dell'intero filegroup. Un filegroup è disponibile se tutti i file in esso inclusi sono online.
• Se un filegroup è offline, qualsiasi tentativo di accesso al filegroup da un'istruzione SQL avrà esito negativo con un errore. Per la compilazione di piani delle query per istruzioni SELECT, Query Optimizer evita gli indici non cluster e le viste indicizzate presenti in filegroup offline. Ciò consente la corretta esecuzione di tali istruzioni. Se tuttavia il filegroup offline contiene l'indice cluster o heap della tabella di destinazione, l'istruzione SELECT avrà esito negativo, così come tutte le istruzioni INSERT, UPDATE o DELETE che implicano la modifica di una tabella tramite un indice incluso in un filegroup offline.

Non è possibile impostare i parametri SIZE, MAXSIZE e FILEGROWTH se è specificato un percorso UNC per il file.

I parametri SIZE e FILEGROWTH non possono essere impostati per i filegroup con ottimizzazione per la memoria.

La parola chiave READONLY verrà rimossa in una versione futura di Microsoft SQL Server. Evitare l'utilizzo di READONLY un nuovo progetto di sviluppo e prevedere interventi di modifica per le applicazioni in cui READONLY è utilizzato. Utilizzare invece READ_ONLY.

La parola chiave READWRITE verrà rimossa in una versione futura di Microsoft SQL Server. Evitare l'uso di READWRITE in un nuovo progetto di sviluppo e prevedere interventi di modifica nelle applicazioni in cui è attualmente usata la parola chiave READWRITE. Usare al suo posto la parola chiave READ_WRITE.

Spostamento di file

È possibile spostare file di dati e di log, di sistema o definiti dall'utente, specificando la nuova posizione in FILENAME. Questa procedura può risultare utile nei casi seguenti:

• Recupero da errore. ad esempio quando il database è in modalità sospetta o viene chiuso a causa di un errore hardware.
• Rilocazione pianificata.
• Rilocazione per una manutenzione pianificata del disco.

Per altre informazioni, vedere Spostare file del database.

Inizializzazione dei file

Per impostazione predefinita, i file di dati e di log vengono inizializzati tramite il riempimento con zeri quando si esegue una delle operazioni seguenti:

• Crea un database .
• Aggiunta di file a un database esistente.
• Aumento delle dimensioni di un file esistente.
• Ripristino di un database o un filegroup.

I file di dati possono essere inizializzati immediatamente. Ciò consente l'esecuzione rapida di queste operazioni sui file. Per altre informazioni, vedere Inizializzazione di file di database.

Rimozione di un contenitore FILESTREAM

Anche se il contenitore FILESTREAM potrebbe essere stato svuotato mediante l'operazione "DBCC SHRINKFILE", potrebbe essere ancora necessario mantenere nel database i riferimenti ai file eliminati per vari motivi di manutenzione del sistema. sp_filestream_force_garbage_collection eseguirà il Garbage Collector per FILESTREAM per rimuovere questi file quando è possibile. A meno che il Garbage Collector per FILESTREAM non abbia rimosso tutti i file da un contenitore FILESTREAM, l'operazione ALTER DATABASE REMOVE FILE avrà esito negativo per la rimozione di un contenitore FILESTREAM e verrà restituito un errore. È consigliabile utilizzare il processo seguente per rimuovere un contenitore FILESTREAM.

1. Eseguire DBCC SHRINKFILE con l'opzione EMPTYFILE per spostare il contenuto attivo del contenitore in altri contenitori.
2. Assicurarsi che siano stati eseguiti i backup del log, nel modello di recupero FULL o BULK_LOGGED.
3. Assicurarsi che sia stato eseguito il processo di lettura log repliche, se rilevante.
4. Eseguire sp_filestream_force_garbage_collection per forzare l'eliminazione tramite Garbage Collector di qualsiasi file non più necessario in questo contenitore.
5. Eseguire ALTER DATABASE con l'opzione REMOVE FILE per rimuovere questo contenitore.
6. Ripetere i passaggi da 2 a 4 ancora una volta per completare la Garbage Collection.
7. Utilizzare ALTER Database...REMOVE FILE per rimuovere questo contenitore.

Esempi

R. Aggiunta di un file a un database

Nell'esempio seguente viene aggiunto un file di dati da 5 MB al database AdventureWorks2022.

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. Aggiunta di un filegroup con due file a un database

Nell'esempio seguente viene creato il filegroup Test1FG1 nel database AdventureWorks2022 e vengono aggiunti due file da 5 MB al filegroup.

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. Aggiunta di due file di log a un database

Nell'esempio seguente vengono aggiunti due file di log da 5 MB al database AdventureWorks2022.

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. Rimozione di un file da un database

Nell'esempio seguente viene rimosso uno dei file aggiunti nell'esempio B.

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

E. Modifica di un file

L'esempio seguente aumenta le dimensioni di uno dei file aggiunti nell'esempio B. L'istruzione ALTER DATABASE con il comando MODIFY FILE può soltanto incrementare le dimensioni di un file, quindi se bisogna ridurle è necessario usare DBCC SHRINKFILE.

USE master;
GO

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

In questo esempio le dimensioni di un file di dati vengono ridotte a 100 MB e ne vengono specificate le dimensioni.

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

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

F. Spostamento di un file in un diverso percorso

Nell'esempio seguente il file Test1dat2 creato nell'esempio A viene spostato in una nuova directory.

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

G. Spostamento del database tempdb in un diverso percorso

Nell'esempio seguente viene spostato il database tempdb dal percorso corrente nel disco a un'altro percorso nel disco. Poiché tempdb viene ricreato a ogni avvio del servizio MSSQLSERVER, non è necessario spostare fisicamente i dati e i file di log. I file vengono creati quando il servizio viene riavviato nel passaggio 3. Fino a quando il servizio non viene riavviato, tempdb continua a funzionare nel percorso esistente.

1. Determinare i nomi di file logici del database tempdb e il rispettivo percorso corrente su disco.

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

2. Modificare il percorso di ogni file tramite 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. Arrestare e riavviare l'istanza di SQL Server.

4. Verificare la modifica ai file.

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

5. Eliminare i file tempdb.mdf e templog.ldf dai percorsi originali.

H. Impostazione di un filegroup come predefinito

Nell'esempio seguente il filegroup Test1FG1 creato nell'esempio B viene impostato come filegroup predefinito. Il filegroup PRIMARY viene quindi reimpostato come filegroup predefinito. Si noti che il nome PRIMARY deve essere delimitato da parentesi quadre o virgolette.

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

I. Aggiunta di un filegroup utilizzando ALTER DATABASE

Nell'esempio seguente viene aggiunto un FILEGROUP che contiene la clausola FILESTREAM al database FileStreamPhotoDB.

--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

Nell'esempio seguente viene aggiunto un FILEGROUP che contiene la clausola MEMORY_OPTIMIZED_DATA al database xtp_db. Nel filegroup vengono archiviati i dati ottimizzati per la memoria.

--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. Modificare il filegroup in modo che, quando un file del filegroup raggiunge la soglia dell'aumento automatico delle dimensioni, vengono aumentate le dimensioni di tutti i file del filegroup

Nell'esempio seguente vengono generate le istruzioni ALTER DATABASE necessarie per modificare il filegroup di lettura/scrittura con l'impostazione di AUTOGROW_ALL_FILES.

--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

Vedi anche

• CREATE DATABASE
• DATABASEPROPERTYEX
• DROP DATABASE
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• BLOB
• DBCC SHRINKFIL
• sp_filestream_force_garbage_collection
• Inizializzazione di file di database

SQL Server

* Istanza gestita di SQL *
 

 

Istanza gestita di SQL di Azure

Usare questa istruzione con un database in Istanza gestita di SQL di Azure.

Sintassi per Istanza gestita di SQL di Azure

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

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

<filespec>::=
(
    NAME = logical_file_name
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
)

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name
    | 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 }
}

Argomenti

<add_or_modify_files>::=

Specifica i file da aggiungere, rimuovere o modificare.

database_name è il nome del database da modificare.

ADD FILE aggiunge un file al database.

TO FILEGROUP { filegroup_name } specifica il filegroup in cui aggiungere il file specificato. Per visualizzare i filegroup correnti e determinare il filegroup attualmente predefinito, usare la vista del catalogo sys.filegroups.

REMOVE FILE logical_file_name rimuove la descrizione del file logico da un'istanza di SQL Server ed elimina il file fisico. Il file può essere rimosso solo se è vuoto.

logical_file_name è il nome logico usato in SQL Server per fare riferimento al file.

MODIFY FILE specifica il file da modificare. È possibile cambiare una sola proprietà <filespec> alla volta. L'opzione NAME deve essere sempre specificata in <filespec> per identificare il file da modificare. Se si specifica l'opzione SIZE, le nuove dimensioni del file devono essere superiori a quelle correnti.

<filespec>::=

Controlla le proprietà del file.

NAME logical_file_name specifica il nome logico del file.

logical_file_name è il nome logico usato in un'istanza di SQL Server per fare riferimento al file.

NEWNAME new_logical_file_name specifica un nuovo nome logico per il file.

new_logical_file_name è il nome da sostituire al nome del file logico esistente. Il nome deve essere univoco all'interno del database e conforme alle regole per gli identificatori. Il nome può essere costituito da una costante per valori di carattere o Unicode, da un identificatore regolare o da un identificatore delimitato.

SIZE size specifica le dimensioni del file.

size corrisponde alle dimensioni del file.

Quando si specifica con ADD FILE, size corrisponde alle dimensioni iniziali del file. Se si specifica con MODIFY FILE, size corrisponde alle nuove dimensioni del file, che devono essere superiori a quelle correnti.

Se non si specifica size per il file primario, SQL Server usa le dimensioni del file primario del database model. Se si specifica un file di dati o un file di log secondario senza specificare size, il motore di database crea un file di 1 MB.

È possibile utilizzare i suffissi KB, MB, GB e TB per indicare kilobyte, megabyte, gigabyte e terabyte. Il valore predefinito è MB. Specificare un numero intero, ovvero non includere decimali. Se si desidera specificare una frazione di megabyte, convertire il valore in kilobyte moltiplicando il numero per 1024. Ad esempio, specificare 1536 KB anziché 1,5 MB (1,5 x 1024 = 1536).

MAXSIZE { max_size| UNLIMITED } specifica le dimensioni massime consentite per il file.

max_size corrisponde alle dimensioni massime del file. È possibile utilizzare i suffissi KB, MB, GB e TB per indicare kilobyte, megabyte, gigabyte e terabyte. Il valore predefinito è MB. Specificare un numero intero, ovvero non includere decimali. Se non si specifica max_size, le dimensioni del file aumenteranno fino a quando il disco risulta pieno.

UNLIMITED specifica che le dimensioni del file aumentano fino a quando il disco risulta pieno. In SQL Server un file di log specificato con aumento delle dimensioni illimitato può raggiungere dimensioni massime di 2 TB, mentre le dimensioni massime di un file di dati sono di 16 TB.

FILEGROWTH growth_increment specifica l'incremento automatico per l'aumento delle dimensioni del file. Il valore impostato per il parametro FILEGROWTH di un file non può essere superiore al valore del parametro MAXSIZE.

growth_increment è la quantità di spazio aggiunta al file ogni volta che è necessario altro spazio.

È possibile specificare il valore in megabyte (MB), kilobyte (KB), gigabyte (GB) o terabyte (TB) oppure in forma di percentuale (%). Se si specifica un valore senza il suffisso MB, KB o %, il suffisso predefinito è MB. Se si utilizza il suffisso %, l'incremento corrisponde alla percentuale delle dimensioni del file specificata quando si verifica l'incremento. Le dimensioni specificate vengono arrotondate al blocco di 64 KB più prossimo.

Il valore 0 indica che l'aumento automatico delle dimensioni è disattivato e non è consentita l'allocazione di spazio aggiuntivo.

Se FILEGROWTH viene omesso, i valori predefiniti sono i seguenti:

• Dati 16 MB
• File di log 16 MB

<add_or_modify_filegroups>::=

Aggiunge, modifica o rimuove un filegroup nel database.

ADD FILEGROUP filegroup_name aggiunge un filegroup al database.

Nell'esempio seguente viene creato un filegroup che viene aggiunto a un database denominato sql_db_mi, quindi viene aggiunto un file al filegroup.

ALTER DATABASE sql_db_mi ADD FILEGROUP sql_db_mi_fg;
GO
ALTER DATABASE sql_db_mi ADD FILE (NAME='sql_db_mi_mod') TO FILEGROUP sql_db_mi_fg;

REMOVE FILEGROUP filegroup_name rimuove un filegroup dal database. Il filegroup può essere rimosso solo se è vuoto. Rimuove tutti i file a partire dal filegroup. Per altre informazioni, vedere "REMOVE FILE logical_file_name" più indietro in questo argomento.

MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME =new_filegroup_name } Modifica il filegroup impostando lo stato su READ_ONLY o READ_WRITE, impostando il filegroup come predefinito per il database o cambiando il nome del filegroup.

<filegroup_updatability_option> imposta la proprietà di sola lettura o di lettura/scrittura per il filegroup.

DEFAULT cambia il filegroup predefinito del database in filegroup_name. In un database può esistere un solo filegroup predefinito. Per altre informazioni, vedere Database Files and Filegroups.

NAME = new_filegroup_name cambia il nome del filegroup in new_filegroup_name.

AUTOGROW_SINGLE_FILE

Quando un file del filegroup raggiunge la soglia dell'aumento automatico delle dimensioni, vengono aumentate le dimensioni solo di quel file. Si tratta dell'impostazione predefinita.

AUTOGROW_ALL_FILES

Quando un file del filegroup raggiunge la soglia dell'aumento automatico delle dimensioni, vengono aumentate le dimensioni di tutti i file del filegroup.

<filegroup_updatability_option>::=

Imposta la proprietà di sola lettura o di lettura/scrittura per il filegroup.

READ_ONLY | READONLY specifica che il filegroup è di sola lettura. Non sono consentiti aggiornamenti degli oggetti nel filegroup. Non è possibile rendere di sola lettura il filegroup primario. Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per altre informazioni, vedere la clausola SINGLE_USER.

I database di sola lettura non consentono modifiche dei dati e pertanto:

• Non viene eseguito il recupero automatico all'avvio del sistema.
• La compattazione del database non è possibile.
• Non vengono attivati blocchi nei database di sola lettura e ciò può portare a migliori prestazioni di esecuzione delle query.

READ_WRITE | READWRITE specifica che il filegroup è di lettura/scrittura. Sono consentiti aggiornamenti degli oggetti contenuti nel filegroup. Per modificare questo stato, è necessario disporre dell'accesso esclusivo al database. Per altre informazioni, vedere la clausola SINGLE_USER.

Per determinare lo stato di queste opzioni, è possibile esaminare la colonna is_read_only nella vista del catalogo sys.databases oppure la proprietà Updateability della funzione DATABASEPROPERTYEX.

Osservazioni:

Per ridurre le dimensioni di un database, usare DBCC SHRINKDATABASE.

Non è possibile aggiungere o rimuovere un file durante l'esecuzione di un'istruzione BACKUP.

Per ogni database è possibile specificare un massimo di 32.767 file e 32.767 filegroup.

Esempi

R. Aggiunta di un file a un database

Nell'esempio seguente viene aggiunto un file di dati da 5 MB al database AdventureWorks2022.

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
  NAME = Test1dat2,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B. Aggiunta di un filegroup con due file a un database

Nell'esempio seguente viene creato il filegroup Test1FG1 nel database AdventureWorks2022 e vengono aggiunti due file da 5 MB al filegroup.

USE master
GO
ALTER DATABASE AdventureWorks2022
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = test1dat3,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

C. Rimozione di un file da un database

Nell'esempio seguente viene rimosso uno dei file aggiunti nell'esempio B.

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

D. Modifica di un file

L'esempio seguente aumenta le dimensioni di uno dei file aggiunti nell'esempio B. L'istruzione ALTER DATABASE con il comando MODIFY FILE può soltanto incrementare le dimensioni di un file, quindi se bisogna ridurle è necessario usare DBCC SHRINKFILE.

USE master;
GO

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

In questo esempio le dimensioni di un file di dati vengono ridotte a 100 MB e ne vengono specificate le dimensioni.

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

USE master;
GO

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

E. Impostazione di un filegroup come predefinito

Nell'esempio seguente il filegroup Test1FG1 creato nell'esempio B viene impostato come filegroup predefinito. Il filegroup PRIMARY viene quindi reimpostato come filegroup predefinito. Si noti che il nome PRIMARY deve essere delimitato da parentesi quadre o virgolette.

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

F. Aggiunta di un filegroup utilizzando ALTER DATABASE

Nell'esempio seguente viene aggiunto un FILEGROUP al database MyDB.

--Create and add a FILEGROUP
ALTER DATABASE MyDB
ADD FILEGROUP NewFG;
GO

--Add a file to FILEGROUP
ALTER DATABASE MyDB
ADD FILE
(
    NAME= 'MyFile',
)
TO FILEGROUP NewFG;
GO

G. Modificare il filegroup in modo che, quando un file del filegroup raggiunge la soglia dell'aumento automatico delle dimensioni, vengono aumentate le dimensioni di tutti i file del filegroup

Nell'esempio seguente vengono generate le istruzioni ALTER DATABASE necessarie per modificare il filegroup di lettura/scrittura con l'impostazione di AUTOGROW_ALL_FILES.

--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

Vedi anche

• CREATE DATABASE
• DATABASEPROPERTYEX
• DROP DATABASE
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• DBCC SHRINKFILE
• Filegroup con ottimizzazione per la memoria