ALTERAR BASE DE DADOS (Transact-SQL) Opções de ficheiros e grupos de ficheiros

Modifica os ficheiros e grupos de ficheiros associados à base de dados. Adiciona ou remove ficheiros e grupos de ficheiros de uma base de dados, e altera os atributos da base de dados ou dos seus ficheiros e grupos de ficheiros. Para outras opções de ALTER DATABASE, veja ALTER DATABASE.

Para obter mais informações sobre as convenções de sintaxe, consulte Transact-SQL convenções de sintaxe.

Selecione um produto

Na linha seguinte, selecione o nome do produto em que está interessado e apenas as informações desse produto são apresentadas.

* Servidor SQL *  

Instância gerenciada SQL

 

Sintaxe

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

Especifica o ficheiro a ser adicionado, removido ou modificado.

database_name É o nome da base de dados a ser modificada.

ADICIONAR FICHEIRO Adiciona um ficheiro à base de dados.

TO FILEGROUP { filegroup_name } Especifica o grupo de ficheiros ao qual adicionar o ficheiro especificado. Para mostrar os grupos de ficheiros atuais e qual o grupo de ficheiros que é o padrão atual, use a vista de catálogo sys.filegroups .

ADICIONAR FICHEIRO DE REGISTO Adiciona um ficheiro de registo a ser adicionado à base de dados especificada.

REMOVE FILE logical_file_name Remove a descrição lógica do ficheiro de uma instância do SQL Server e elimina o ficheiro físico. O ficheiro não pode ser removido a menos que esteja vazio.

logical_file_name É o nome lógico usado no SQL Server ao referenciar o ficheiro.

Advertência

Remover um ficheiro de base de dados que tenha FILE_SNAPSHOT backups associados terá sucesso, mas quaisquer snapshots associados não serão eliminados para evitar invalidar os backups que se referem ao ficheiro da base de dados. O ficheiro será truncado, mas não será fisicamente apagado para manter as FILE_SNAPSHOT cópias de segurança intactas. Para mais informações, consulte Backup e Restauração do SQL Server com Armazenamento de Blobs do Microsoft Azure. Aplica-se a: SQL Server (SQL Server 2016 (13.x) e posteriores).

MODIFY FILE Especifica o ficheiro que deve ser modificado. Apenas uma <propriedade filespec> pode ser alterada de cada vez. NAME deve estar sempre especificado na <especificação> dos ficheiros para identificar o ficheiro a ser modificado. Se for especificado SIZE, o novo tamanho deve ser maior do que o tamanho atual do ficheiro.

Para modificar o nome lógico de um ficheiro de dados ou ficheiro de registo, especifique o nome lógico do ficheiro a ser renomeado na NAME cláusula e especifique o novo nome lógico do ficheiro na NEWNAME cláusula. Por exemplo:

MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

Para mover um ficheiro de dados ou ficheiro de registo para uma nova localização, especifique o nome atual do ficheiro lógico na NAME cláusula e o novo caminho e o nome do ficheiro do sistema operativo na FILENAME cláusula. Por exemplo:

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

Ao mover um catálogo de texto completo, especifique apenas o novo caminho na cláusula FILENAME. Não especifique o nome do ficheiro do sistema operativo.

Para mais informações, consulte Mover Ficheiros de Base de Dados.

Para um grupo de ficheiros FILESTREAM, o NAME pode ser modificado online. FILENAME pode ser modificado online; no entanto, a alteração só entra em vigor depois de o contentor ser fisicamente realocado, o servidor ser desligado e depois reiniciado.

Podes definir um ficheiro FILESTREAM para OFFLINE. Quando um ficheiro FILESTREAM está offline, o seu grupo de ficheiros pai será internamente marcado como offline; portanto, todo o acesso aos dados do FILESTREAM dentro desse grupo de ficheiros falhará.

Observação

<add_or_modify_files> opções não estão disponíveis numa Base de Dados Contida.

<filespec>::=

Controla as propriedades do arquivo.

NAME logical_file_name Especifica o nome lógico do ficheiro.

logical_file_name É o nome lógico usado numa instância do SQL Server ao referenciar o ficheiro.

NEWNAME new_logical_file_name Especifica um novo nome lógico para o ficheiro.

new_logical_file_name É o nome que substitui o nome do ficheiro lógico existente. O nome deve ser único dentro da base de dados e cumprir as regras para identificadores. O nome pode ser um carácter ou constante Unicode, um identificador regular ou um identificador delimitado.

FILENAME { ' | ' | 'memory_optimized_data_path''} Especifica o nome do ficheiro (físico) do sistema operativo.

' os_file_name ' Para um grupo de ficheiros padrão (ROWS), este é o caminho e o nome do ficheiro usados pelo sistema operativo quando se cria o ficheiro. O ficheiro deve residir no servidor onde o SQL Server está instalado. O caminho especificado deve existir antes de executar a instrução ALTER DATABASE.

Observação

SIZE, MAXSIZE, e FILEGROWTH os parâmetros não podem ser definidos quando um caminho UNC é especificado para o ficheiro.

As bases de dados do sistema não podem residir em diretórios de partilha UNC.

Os ficheiros de dados não devem ser colocados em sistemas de ficheiros comprimidos, a menos que sejam ficheiros secundários de apenas leitura, ou se a base de dados for apenas de leitura. Os arquivos de log nunca devem ser colocados em sistemas de arquivos compactados.

Se o arquivo estiver em uma partição bruta, os_file_name deverá especificar apenas a letra da unidade de uma partição bruta existente. Apenas um ficheiro pode ser colocado em cada partição bruta.

'filestream_path' Para um grupo de ficheiros FILESTREAM, FILENAME refere-se a um caminho onde os dados FILESTREAM serão armazenados. O caminho até a última pasta deve existir e a última pasta não deve existir. Por exemplo, se especificar o caminho C:\MyFiles\MyFilestreamData, então C:\MyFiles deve existir antes de executar ALTER DATABASE, mas a MyFilestreamData pasta não deve existir.

Observação

As propriedades SIZE e FILEGROWTH não se aplicam a um grupo de ficheiros FILESTREAM.

'memory_optimized_data_path' Para um grupo de ficheiros otimizado para memória, FILENAME refere-se a um caminho onde os dados otimizados para memória serão armazenados. O caminho até a última pasta deve existir e a última pasta não deve existir. Por exemplo, se especificar o caminho C:\MyFiles\MyData, então C:\MyFiles deve existir antes de executar ALTER DATABASE, mas a MyData pasta não deve existir.

O grupo de arquivos e o arquivo (<filespec>) devem ser criados na mesma instrução.

Observação

As propriedades SIZE e FILEGROWTH não se aplicam a um grupo de ficheiros MEMORY_OPTIMIZED_DATA.

Para mais informações sobre grupos de ficheiros otimizados para memória, consulte O Grupo de Ficheiros Otimizado para Memória.

SIZE size Especifica o tamanho do ficheiro. SIZE não se aplica aos grupos de ficheiros FILESTREAM.

Tamanho É o tamanho do ficheiro.

Quando especificado com ADD FILE, o tamanho é o tamanho inicial do ficheiro. Quando especificado com MODIFY FILE, o tamanho é o novo tamanho do ficheiro e deve ser maior do que o tamanho atual do ficheiro.

Quando o tamanho não é fornecido para o ficheiro primário, o SQL Server utiliza o tamanho do ficheiro primário na base de dados do modelo . Quando um ficheiro de dados secundário ou de registo é especificado mas o tamanho não é especificado para o ficheiro, o Motor de Base de Dados faz com que o ficheiro tenha 1 MB.

Os sufixos KB, MB, GB e TB podem ser usados para especificar kilobytes, megabytes, gigabytes ou terabytes. O padrão é MB. Especifique um número inteiro e não inclua um decimal. Para especificar uma fração de megabyte, converte o valor em kilobytes multiplicando o número por 1024. Por exemplo, especifique 1536 KB em vez de 1,5 MB (1,5 x 1024 = 1536).

Observação

SIZE não pode ser definido:

• Quando um caminho UNC é especificado para o ficheiro
• Para FILESTREAM e MEMORY_OPTIMIZED_DATA grupos de ficheiros

MAXSIZE { max_size| UNLIMITED } Especifica o tamanho máximo do ficheiro a que o ficheiro pode crescer.

max_size É o tamanho máximo do ficheiro. Os sufixos KB, MB, GB e TB podem ser usados para especificar kilobytes, megabytes, gigabytes ou terabytes. O padrão é MB. Especifique um número inteiro e não inclua um decimal. Se max_size não for especificado, o tamanho do ficheiro aumenta até o disco estar cheio.

UNLIMITED Especifica que o ficheiro cresce até o disco estar cheio. No SQL Server, um arquivo de log especificado com crescimento ilimitado tem um tamanho máximo de 2 TB e um arquivo de dados tem um tamanho máximo de 16 TB. Não existe um tamanho máximo quando esta opção é especificada para um contentor FILESTREAM. Ele continua a crescer até que o disco esteja cheio.

Observação

MAXSIZE não pode ser definido quando um caminho UNC é especificado para o ficheiro.

FILEGROWTH growth_increment Especifica o incremento automático de crescimento do ficheiro. A definição FILEGROWTH para um ficheiro não pode exceder a definição MAXSIZE. O FILEGROWTH não se aplica aos grupos de ficheiros FILESTREAM.

growth_increment É a quantidade de espaço adicionada ao ficheiro sempre que é necessário novo espaço.

O valor pode ser especificado em MB, KB, GB, TB ou percentagem (%). Se um número for especificado sem um sufixo MB, KB ou %, o padrão é MB. Quando % é especificado, o tamanho do incremento de crescimento é a percentagem especificada do tamanho do ficheiro no momento em que o incremento ocorre. O tamanho especificado é arredondado para os 64 KB mais próximos.

Um valor de 0 indica que o crescimento automático está definido como desligado e não é permitido espaço adicional.

Se o FILEGROWTH não for especificado, os valores padrão são:

Versão	Valores padrão
Começando com o SQL Server 2016 (13.x)	Dados 64 MB. Ficheiros de registo 64 MB.
Começando com o SQL Server 2005 (9.x)	Dados 1 MB. Arquivos de log 10%.
Antes do SQL Server 2005 (9.x)	Dados 10%. Arquivos de log 10%.

Observação

FILEGROWTH não pode ser definido:

• Quando um caminho UNC é especificado para o ficheiro
• Para FILESTREAM e MEMORY_OPTIMIZED_DATA grupos de ficheiros

OFFLINE Define o ficheiro offline e torna todos os objetos do grupo de ficheiros inacessíveis.

Atenção

Use esta opção apenas quando o ficheiro estiver corrompido e puder ser restaurado. Um ficheiro configurado para OFFLINE só pode ser definido online restaurando o ficheiro a partir do backup. Para mais informações sobre restauração de um único ficheiro, consulte RESTORE.

<as opções filespec> não estão disponíveis numa Base de Dados Contida.

<add_or_modify_filegroups>::=

Adicione, modifique ou remova um grupo de ficheiros da base de dados.

ADICIONAR FILEGROUP filegroup_name Adiciona um grupo de ficheiros à base de dados.

CONTAINS FILESTREAM Especifica que o grupo de ficheiros armazena objetos grandes binários FILESTREAM (BLOBs) no sistema de ficheiros.

CONTÉM MEMORY_OPTIMIZED_DATA

Aplica-se a: SQL Server (SQL Server 2014 (12.x) e posteriores)

Especifica que o grupo de ficheiros armazena dados otimizados para memória no sistema de ficheiros. Para mais informações, consulte In-Memory OLTP - In-Memory Otimização. Apenas um MEMORY_OPTIMIZED_DATA grupo de arquivos é permitido por banco de dados. Para criar tabelas otimizadas para memória, o grupo de ficheiros não pode estar vazio. Tem de haver pelo menos um ficheiro. filegroup_name refere-se a um caminho. O caminho até a última pasta deve existir e a última pasta não deve existir.

REMOVER FILEGROUP filegroup_name Remove um file group da base de dados. O grupo de ficheiros não pode ser removido a menos que esteja vazio. Remova todos os ficheiros do grupo de ficheiros primeiro. Para mais informações, consulte "REMOVER FICHEIRO logical_file_name", anteriormente neste tópico.

Observação

A menos que o Coletor de Lixo FILESTREAM tenha removido todos os ficheiros de um contentor FILESTREAM, a ALTER DATABASE REMOVE FILE operação para remover um contentor FILESTREAM falhará e devolverá um erro. Consulte a secção Remoção de um Contentor FILESTREAM mais adiante neste tópico.

MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | PADRÃO | NAME =new_filegroup_name } Modifica o grupo de ficheiros definindo o estado para READ_ONLY ou READ_WRITE, tornando o grupo de ficheiros o grupo de ficheiros predefinido da base de dados ou alterando o nome do grupo de ficheiros.

<filegroup_updatability_option> Define a propriedade de só leitura ou leitura/escrita para o grupo de ficheiros.

DEFAULT Altera o grupo de ficheiros da base de dados predefinido para filegroup_name. Apenas um grupo de ficheiros na base de dados pode ser o grupo de ficheiros predefinido. Para mais informações, consulte Ficheiros e Grupos de Ficheiros da Base de Dados.

NAME = new_filegroup_name Altera o nome do grupo de ficheiros para o new_filegroup_name.

AUTOGROW_SINGLE_FILE Aplica-se a: SQL Server (SQL Server 2016 (13.x) e posteriores)

Quando um ficheiro no grupo atinge o limiar de autocrescimento, apenas esse ficheiro cresce. Este é o padrão.

AUTOGROW_ALL_FILES

Aplica-se a: SQL Server (SQL Server 2016 (13.x) e posteriores)

Quando um arquivo no grupo de arquivos atinge o limite de crescimento automático, todos os arquivos no grupo de arquivos crescem.

Observação

Este é o valor padrão do TempDB.

<filegroup_updatability_option>::=

Define a propriedade de apenas leitura ou leitura/escrita para o grupo de ficheiros.

READ_ONLY | READONLY Especifica que o grupo de ficheiros é só de leitura. Não são permitidas atualizações a objetos nele presentes. O grupo de ficheiros primário não pode ser tornado apenas de leitura. Para alterar esse estado, você deve ter acesso exclusivo ao banco de dados. Para obter mais informações, consulte a cláusula SINGLE_USER.

Como uma base de dados de apenas leitura não permite modificações de dados:

• A recuperação automática é ignorada no arranque do sistema.
• Reduzir a base de dados não é possível.
• Não ocorre bloqueio em bases de dados apenas de leitura. Isto pode causar um desempenho de consulta mais rápido.

Observação

A palavra-chave READONLY será removida numa versão futura do Microsoft SQL Server. Evite usar READONLY em novos trabalhos de desenvolvimento e planeie modificar aplicações que atualmente utilizam READONLY. Utilize READ_ONLY em substituição.

READ_WRITE | READWRITE Especifica que o grupo é READ_WRITE. As atualizações estão ativadas para os objetos do grupo de ficheiros. Para alterar esse estado, você deve ter acesso exclusivo ao banco de dados. Para obter mais informações, consulte a cláusula SINGLE_USER.

Observação

A palavra-chave READWRITE será removida numa versão futura do Microsoft SQL Server. Evite usar READWRITE em novos trabalhos de desenvolvimento e planeie modificar as aplicações que já usam READWRITE para READ_WRITE usar em vez disso.

Sugestão

O estado destas opções pode ser determinado examinando a coluna is_read_only na vista de catálogo sys.databases ou a propriedade Updateability da DATABASEPROPERTYEX função.

Observações

Para diminuir o tamanho de um banco de dados, use DBCC SHRINKDATABASE.

Não pode adicionar ou remover um ficheiro enquanto uma BACKUP instrução está a correr.

Um máximo de 32.767 arquivos e 32.767 grupos de arquivos podem ser especificados para cada banco de dados.

A partir do SQL Server 2005 (9.x), o estado de um ficheiro de base de dados (por exemplo, online ou offline) é mantido independentemente do estado da base de dados. Para obter mais informações, consulte Estados do arquivo.

• O estado dos arquivos dentro de um grupo de arquivos determina a disponibilidade de todo o grupo de arquivos. Para que um grupo de arquivos esteja disponível, todos os arquivos dentro do grupo de arquivos devem estar online.
• Se um grupo de ficheiros estiver offline, qualquer tentativa de aceder ao grupo de ficheiros por uma instrução SQL falhará com um erro. Quando constrói planos de consulta para SELECT as sentenças, o otimizador de consultas evita índices não agrupados e vistas indexadas que residem em grupos de ficheiros offline. Isso permite que essas declarações sejam bem-sucedidas. No entanto, se o grupo de ficheiros offline contiver o heap ou índice agrupado da tabela de destino, as SELECT instruções falham. Além disso, qualquer INSERT, UPDATE, ou DELETE instrução que modifique uma tabela com qualquer índice num grupo de ficheiros offline falhará.

Os parâmetros SIZE, MAXSIZE e FILEGROWTH não podem ser definidos quando um caminho UNC é especificado para o ficheiro.

Os parâmetros SIZE e FILEGROWTH não podem ser definidos para grupos de ficheiros otimizados para memória.

A palavra-chave READONLY será removida numa versão futura do Microsoft SQL Server. Evite usar READONLY em novos trabalhos de desenvolvimento e planeie modificar aplicações que atualmente utilizam apenas leitura. Utilize READ_ONLY em substituição.

A palavra-chave READWRITE será removida numa versão futura do Microsoft SQL Server. Evite usar READWRITE em novos trabalhos de desenvolvimento e planeie modificar as aplicações que já usam READWRITE para READ_WRITE usar em vez disso.

Ficheiros de Mudança

Pode mover dados e ficheiros de registo definidos pelo sistema ou pelo utilizador especificando a nova localização no NOME do FICHEIRO. Isto pode ser útil nos seguintes cenários:

• Recuperação de falhas. Por exemplo, a base de dados está em modo suspeito ou desligada causada por falha de hardware.
• Mudança planeada.
• Relocalização para manutenção programada do disco.

Para mais informações, consulte Mover Ficheiros de Base de Dados.

Inicialização de ficheiros

Por defeito, os dados e ficheiros de registo são inicializados preenchendo os ficheiros com zeros quando se realiza uma das seguintes operações:

• Crie um banco de dados.
• Adicione ficheiros a uma base de dados existente.
• Aumente o tamanho de um ficheiro existente.
• Restaurar uma base de dados ou grupo de ficheiros.

Os ficheiros de dados podem ser inicializados instantaneamente. Isto permite a execução rápida destas operações de ficheiros. Para obter mais informações, consulte Inicialização do arquivo de banco de dados.

Remoção de um Contentor FILESTREAM

Embora o contentor FILESTREAM possa ter sido esvaziado usando a operação "DBCC SHRINKFILE", a base de dados pode ainda precisar de manter referências aos ficheiros eliminados por várias razões de manutenção do sistema. sp_filestream_force_garbage_collection executa o FILESTREAM Garbage Collector para remover estes ficheiros quando for seguro fazê-lo. A menos que o Coletor de Lixo FILESTREAM tenha removido todos os ficheiros de um contentor FILESTREAM, a operação ALTERAR A BASE DE DADOS REMOVER FICHEIRO falhará em remover um contentor FILESTREAM e devolverá um erro. Recomenda-se o seguinte processo para remover um contentor FILESTREAM.

1. Execute o DBCC SHRINKFILE com a opção EMPTYFILE para mover o conteúdo ativo deste contentor para outros contentores.
2. Certifique-se de que as cópias de segurança dos Log foram feitas, no modelo COMPLETO ou BULK_LOGGED de recuperação.
3. Certifique-se de que o trabalho do leitor de registo de replicação foi executado, se aplicável.
4. Execute sp_filestream_force_garbage_collection para forçar o coletor de lixo a eliminar quaisquer ficheiros que já não sejam necessários neste contentor.
5. Execute ALTER DATABASE com a opção REMOVER FICHEIRO para remover este contentor.
6. Repita os passos 2 a 4 mais uma vez para completar a recolha do lixo.
7. Usar a Base de Dados ALTER... REMOVER FICHEIRO para remover este contentor.

Examples

A. Adicionar um ficheiro a uma base de dados

O exemplo seguinte adiciona um ficheiro de dados de 5 MB à base de dados AdventureWorks2025.

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. Adicionar um grupo de ficheiros com dois ficheiros a uma base de dados

O exemplo seguinte cria o grupo Test1FG1 de ficheiros na base de dados AdventureWorks2025 e adiciona dois ficheiros de 5 MB ao grupo de ficheiros.

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. Adicionar dois ficheiros de registo a uma base de dados

O exemplo seguinte adiciona dois ficheiros de registo de 5 MB à base de dados AdventureWorks2025.

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. Remover um ficheiro de uma base de dados

O exemplo seguinte remove um dos ficheiros adicionados no exemplo B.

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

E. Modificar um ficheiro

O exemplo seguinte aumenta o tamanho de um dos ficheiros adicionados no exemplo B. A base de dados ALTER com o comando MODIFY FILE só pode aumentar o tamanho do ficheiro, por isso, se precisares de reduzir o tamanho do ficheiro, tens de usar o DBCC SHRINKFILE.

USE master;
GO

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

Este exemplo reduz o tamanho de um ficheiro de dados para 100 MB e depois especifica o tamanho nesse valor.

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

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

F. Mover um ficheiro para uma nova localização

O exemplo seguinte move o Test1dat2 ficheiro criado no exemplo A para um novo diretório.

Observação

Deve mover fisicamente o ficheiro para o novo diretório antes de executar este exemplo. Depois, pare e inicie a instância do SQL Server ou coloque a AdventureWorks2025 base de dados OFFLINE e depois ONLINE para implementar a alteração.

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

G. Transferir o tempdb para um novo local

O exemplo seguinte move-se tempdb da sua posição atual no disco para outra localização do disco. Como tempdb é recriado cada vez que o serviço MSSQLSERVER é iniciado, não é necessário mover fisicamente os dados e os ficheiros de log. Os ficheiros são criados quando o serviço é reiniciado no passo 3. Até ao reinício do serviço, tempdb continua a funcionar na sua localização atual.

1. Determinar os nomes lógicos dos ficheiros da tempdb base de dados e a sua localização atual no disco.

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

2. Altere a localização de cada ficheiro usando 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. Pare e reinicie a instância do SQL Server.

4. Verifica a alteração do ficheiro.

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

5. Apaga os ficheiros tempdb.mdf e templog.ldf da sua localização original.

H. Tornar um grupo de ficheiros o padrão

O exemplo seguinte faz do Test1FG1 grupo de ficheiros criado no exemplo B o grupo de ficheiros por defeito. Depois, o grupo de ficheiros predefinido é reiniciado para esse PRIMARY grupo. Note que PRIMARY deve ser delimitado por parênteses ou aspas.

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

I. Adicionar um grupo de ficheiros usando a base de dados ALTER

O exemplo seguinte adiciona um FILEGROUP que contém a FILESTREAM cláusula à FileStreamPhotoDB base de dados.

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

O exemplo seguinte adiciona um FILEGROUP que contém a MEMORY_OPTIMIZED_DATA cláusula à xtp_db base de dados. O grupo de ficheiros armazena dados otimizados para memória.

--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. Altere o grupo de ficheiros para que, quando um ficheiro no grupo atingir o limiar de crescimento automático, todos os ficheiros do grupo cresçam

O exemplo seguinte gera as instruções necessárias ALTER DATABASE para modificar grupos de ficheiros de leitura e escrita com a AUTOGROW_ALL_FILES definição.

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

Ver também

• CRIAR BANCO DE DADOS
• DATABASEPROPERTYEX
• BASE DE DADOS DE DROP
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• Objetos binários grandes
• DBCC SHRINKFIL
• sp_filestream_force_garbage_collection
• Inicialização de ficheiro de base de dados