Opções de arquivo e grupos de arquivos ALTER DATABASE (Transact-SQL)

  • Artigo

Modifica os arquivos e grupos de arquivos associados ao banco de dados. Adiciona ou remove arquivos e grupos de arquivos de um banco de dados e altera os atributos de um banco de dados ou seus arquivos e grupos de arquivos. Para outras opções ALTER DATABASE, consulte ALTER DATABASE (Transact-SQL).

Ícone de vínculo de tópico Convenções da sintaxe Transact-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' } ] 
    [ , 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 ]
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

Argumentos

<add_or_modify_files>::=

Especifica o arquivo a ser adicionado, removido ou modificado.

  • database_name
    É o nome do banco de dados a ser modificado.

  • ADD FILE
    Adiciona um arquivo ao banco de dados.

    • TO FILEGROUP { filegroup_name }
      Especifica o grupo de arquivos ao qual adicionar o arquivo especificado. Para exibir os grupos de arquivos atuais e qual grupo de arquivos é o padrão atual, use a exibição de catálogo sys.filegroups.

  • ADD LOG FILE
    Adiciona um arquivo de log ao banco de dados especificado.

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

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

  • MODIFY FILE
    Especifica o arquivo que deve ser modificado. Só uma propriedade de <filespec>pode ser alterada de cada vez. NAME sempre deve ser especificado em <filespec> para identificar o arquivo a ser modificado. Se SIZE for especificado, o novo tamanho deverá ser maior que o tamanho do arquivo atual.

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

    MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

    Para mover um arquivo de dados ou de log para um novo local, especifique o nome do arquivo lógico atual na cláusula NAME e especifique o novo caminho e o nome do arquivo do sistema operacional na cláusula FILENAME. Por exemplo:

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

    Quando você move um catálogo de texto completo, especifique somente o novo caminho na cláusula FILENAME. Não especifique o nome do arquivo do sistema operacional.

    Para obter mais informações, consulte Mover arquivos de banco de dados.

    Para um grupo de arquivos FILESTREAM, NAME pode ser modificado online. FILENAME pode ser modificado online; entretanto, a operação não entra em vigor até que o contêiner seja fisicamente realocado e o servidor seja desligado e reiniciado.

    Você pode definir um arquivo FILESTREAM como OFFLINE. Quando um arquivo FILESTREAM estiver offline, seu grupo de arquivos pai será internamente marcado como offline; portanto, haverá falha em todo acesso aos dados FILESTREAM naquele grupo de arquivos.

ObservaçãoObservação

As opções <add_or_modify_files> não estão disponíveis em um Banco de Dados Independente.

<filespec>::=

Controla as propriedades do arquivo.

  • NAME logical_file_name
    Especifica o nome lógico do arquivo.

    • logical_file_name
      É o nome lógico usado em uma instância do SQL Server ao referenciar o arquivo.

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

    • new_logical_file_name
      É o nome para substituir o nome do arquivo lógico existente. O nome deve ser exclusivo dentro do banco de dados e estar de acordo com a regras de identificadores. O nome pode ser uma constante de caractere ou Unicode, um identificador comum ou delimitado.

  • FILENAME { 'os_file_name' | 'filestream_path' }
    Especifica o nome do arquivo (físico) do sistema operacional.

    • ' os_file_name '
      Para um grupo de arquivos padrão (ROWS), esse é o caminho e o nome do arquivo usado pelo sistema operacional quando você cria o arquivo. O arquivo deve residir no servidor no qual o SQL Server está instalado. O caminho especificado deve existir antes da execução da instrução ALTER DATABASE.

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

      ObservaçãoObservação

      Bancos de dados do sistema não podem residir em diretórios de compartilhamento UNC.

      Arquivos de dados não devem ser colocados em sistemas de arquivos compactados, a não ser que os arquivos sejam secundários e somente leitura ou que o banco de dados seja somente leitura. 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 arquivo de dados pode ser colocado em cada partição bruta.

    • ' filestream_path '
      Para um grupo de arquivos FILESTREAM, FILENAME faz referência a um caminho onde os dados de FILESTREAM serão armazenados. O caminho até a última pasta deve existir e a última pasta não deve existir. Por exemplo, se você especificar o caminho C:\MyFiles\MyFilestreamData, C:\MyFiles deve existir antes da execução de ALTER DATABASE, mas a pasta MyFilestreamData não deve existir.

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

  • SIZE size
    Especifica o tamanho do arquivo. SIZE não se aplica a grupos de arquivos FILESTREAM.

    • size
      É o tamanho do arquivo.

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

      Quando size não for fornecido para o arquivo primário, o SQL Server usará o tamanho do arquivo primário no banco de dados modelo. Quando um arquivo de dados ou de log secundário estiver especificado, mas size não estiver, o Mecanismo de Banco de Dados o transformará em um arquivo de 1 MB.

      Os sufixos KB, MB, GB e TB podem ser usados para especificar quilobytes, 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 um megabyte, converta 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).

  • MAXSIZE { max_size| UNLIMITED }
    Especifica o tamanho máximo até o qual o arquivo pode crescer.

    • max_size
      É o tamanho máximo do arquivo. Os sufixos KB, MB, GB e TB podem ser usados para especificar quilobytes, 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 arquivo aumentará até que o disco esteja cheio.

    • UNLIMITED
      Especifica que o arquivo crescerá até que o disco esteja 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 há nenhum tamanho máximo quando essa opção é especificada para um contêiner FILESTREAM. Ele continua crescendo até que o disco esteja cheio.

  • FILEGROWTH growth_increment
    Especifica o incremento de crescimento automático do arquivo. A configuração de FILEGROWTH de um arquivo não pode exceder a configuração de MAXSIZE. FILEGROWTH não se aplica a grupos de arquivos FILESTREAM.

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

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

      Um valor 0 indica que o crescimento automático está definido como off e nenhum espaço adicional é permitido.

      Se FILEGROWTH não estiver especificado, o valor padrão será 1 MB para arquivos de dados e 10% para arquivos de log e o valor mínimo será 64 KB.

      ObservaçãoObservação

      A partir do SQL Server 2005, o incremento de crescimento padrão para arquivos de dados foi alterado de 10% para 1 MB. O padrão de 10% do arquivo de log permanece inalterado.

  • OFFLINE
    Define o arquivo como offline e torna todos os objetos no grupo de arquivos inacessíveis.

    Observação sobre cuidadosCuidado

    Só use essa opção quando o arquivo estiver corrompido e puder ser restaurado. Um arquivo definido como OFFLINE só pode ser definido como online por meio da restauração do arquivo do backup. Para obter mais informações sobre como restaurar um único arquivo, consulte RESTORE (Transact-SQL).
ObservaçãoObservação

As opções <filespec> não estão disponíveis em um Banco de Dados Independente.

<add_or_modify_filegroups>::=

Adiciona, modifica ou remove um grupo de arquivos do banco de dados.

  • ADD FILEGROUP filegroup_name
    Adiciona um grupo de arquivos ao banco de dados.

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

  • REMOVE FILEGROUP filegroup_name
    Remove um grupo de arquivos do banco de dados. O grupo de arquivos não pode ser removido, a menos que esteja vazio. Remova todos os arquivos do grupo de arquivos primeiro. Para obter mais informações, consulte "REMOVE FILE logical_file_name", anteriormente neste tópico.

    ObservaçãoObservação

    A menos que o Coletor de Lixo de FILESTREAM tenha removido todos os arquivos de um contêiner FILESTREAM, haverá falha e um erro será retornado na operação ALTER DATABASE REMOVE FILE para remover um contêiner FILESTREAM. Consulte a seção "Remover contêiner FILESTREAM" em Comentários posteriormente neste tópico.

  • MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
    Modifica o grupo de arquivos definindo o status como READ_ONLY ou READ_WRITE, tornando o grupo de arquivos o padrão para o banco de dados ou alterando o nome do grupo de arquivos.

    • <filegroup_updatability_option>
      Define a propriedade somente leitura ou leitura/gravação para o grupo de arquivos.

    • DEFAULT
      Altera o grupo de arquivos do banco do dados padrão para filegroup_name. Apenas um grupo de arquivos no banco de dados pode ser o grupo de arquivos padrão. Para obter mais informações, consulte Arquivos e grupos de arquivos do banco de dados.

    • NAME = new_filegroup_name
      Altera o nome do grupo de arquivos para o new_filegroup_name.

<filegroup_updatability_option>::=

Define a propriedade somente leitura ou leitura/gravação para o grupo de arquivos.

  • READ_ONLY | READONLY
    Especifica que o grupo de arquivos é somente leitura. Não são permitidas atualizações nos objetos. O grupo de arquivos primário não pode ser somente leitura. Para alterar esse estado, é necessário ter acesso exclusivo ao banco de dados. Para obter mais informações, consulte a cláusula SINGLE_USER.

    Como um banco de dados somente leitura não permite modificações de dados:

    • A recuperação automática é ignorada na inicialização do sistema.

    • Não é possível reduzir o banco de dados.

    • Não ocorrem bloqueios em bancos de dados somente leitura. Isso pode acelerar o desempenho das consultas.

    ObservaçãoObservação

    A palavra-chave READONLY será removida em uma versão futura do Microsoft SQL Server. Evite usar READONLY em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que o utilizam atualmente. Em vez disso, use READ_ONLY.

  • READ_WRITE | READWRITE
    Especifica que o grupo é READ_WRITE. As atualizações são habilitadas para os objetos no grupo de arquivos. Para alterar esse estado, é necessário ter acesso exclusivo ao banco de dados. Para obter mais informações, consulte a cláusula SINGLE_USER.

    ObservaçãoObservação

    A palavra-chave READWRITE será removida em uma versão futura do Microsoft SQL Server. Evite usar READWRITE em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que o utilizam atualmente. Em vez disso, use READ_WRITE.

O status dessas opções pode ser determinado examinando a coluna is_ready_only na exibição do catálogo sys.databases ou a propriedade Updateability da função DATABASEPROPERTYEX.

Comentários

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

Você não pode adicionar ou remover um arquivo enquanto uma instrução BACKUP está em execução.

Um máximo de 32.767 arquivos e 32.767 grupos de arquivos pode ser especificado para cada banco de dados.

No SQL Server 2005 ou posterior, o estado de um arquivo de banco de dados (por exemplo, online ou offline) é mantido independentemente do estado do banco de dados. Para obter mais informações, consulte Estados de arquivo. O estado dos arquivos dentro de um grupo de arquivos determina a disponibilidade de todo o grupo. Para que um grupo de arquivos fique disponível, todos os seus arquivos devem estar online. Se um grupo de arquivos estiver offline, qualquer tentativa de acessá-lo por meio de uma instrução SQL falhará com erro. Quando você cria planos de consulta para instruções SELECT, o otimizador de consultas evita índices não clusterizados e exibições indexadas que residam em grupos de arquivos offline. Isso permite que essas instruções tenham êxito. Porém, se o grupo de arquivos offline contiver o heap ou índice clusterizado da tabela de destino, as instruções SELECT falharão. Além disso, qualquer instrução INSERT, UPDATE ou DELETE que modifique uma tabela contendo um índice em um grupo de arquivos offline falhará.

Movendo arquivos

No SQL Server 2005 ou versão posterior, você pode mover dados do sistema ou definidos pelo usuário e arquivos de log, especificando o novo local em FILENAME. Isso pode ser útil nos seguintes cenários:

  • Recuperação de falha. Por exemplo, o banco de dados está em modo suspeito ou desligado devido a uma falha no hardware

  • Realocação planejada

  • Realocação para manutenção de disco programada

Para obter mais informações, consulte Mover arquivos de banco de dados.

Inicializando arquivos

Por padrão, arquivos de dados e de log são inicializados por meio do preenchimento com zeros quando você executa uma das seguintes operações:

  • Criar um banco de dados

  • Adicionar arquivos a um banco de dados existente

  • Aumentar o tamanho de um arquivo existente

  • Restaurar um banco de dados ou grupo de arquivos

Arquivos de dados podem ser inicializados instantaneamente. Isso permite uma execução rápida dessas operações de arquivo.

Removendo um contêiner FILESTREAM

Embora o contêiner FILESTREAM possa ter sido esvaziado por meio da operação "DBCC SHRINKFILE", o banco de dados ainda pode precisar manter referências aos arquivos excluídos por várias razões de manutenção do sistema. sp_filestream_force_garbage_collection (Transact-SQL) executará o Coletor de Lixo de FILESTREAM para remover esses arquivos quando for seguro fazê-lo. A menos que o Coletor de Lixo de FILESTREAM tenha removido todos os arquivos de um contêiner FILESTREAM, a operação ALTER DATABASEREMOVE FILE não poderá remover um contêiner FILESTREAM e um erro será retornado. O processo a seguir é recomendado para remover um contêiner FILESTREAM.

  1. Execute DBCC SHRINKFILE (Transact-SQL) com a opção EMPTYFILE para mover o conteúdo ativo desse para outros contêineres.

  2. Verifique se foram executados backups de log no modelo de recuperação FULL ou BULK_LOGGED.

  3. Verifique se o trabalho do leitor de log de replicação foi executado, se pertinente.

  4. Execute sp_filestream_force_garbage_collection (Transact-SQL) para forçar o coletor de lixo a excluir qualquer arquivo que não seja mais necessário neste contêiner.

  5. Execute ALTER DATABASE com a opção REMOVE FILE para remover este contêiner.

  6. Repita as etapas 2 a 4 uma vez para concluir a coleta de lixo.

  7. Use ALTER Database...REMOVE FILE para remover este contêiner.

Exemplos

A.Adicionando um arquivo a um banco de dados

O exemplo a seguir adiciona um arquivo de dados de 5 MB ao banco de dados AdventureWorks2012.

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

B.Adicionando um grupo de arquivos com dois arquivos a um banco de dados

O exemplo a seguir cria o grupo de arquivos Test1FG1 no banco de dados AdventureWorks2012 e adiciona dois arquivos de 5 MB ao grupo de arquivos.

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

C.Adicionando dois arquivos de log a um banco de dados

O exemplo a seguir adiciona dois arquivos de log de 5 MB ao banco de dados AdventureWorks2012.

USE master;
GO
ALTER DATABASE AdventureWorks2012 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.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.Removendo um arquivo de um banco de dados

O exemplo a seguir remove um dos arquivos adicionados no exemplo B.

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

E.Modificando um arquivo

O exemplo a seguir aumenta o tamanho de um dos arquivos adicionados no exemplo B.

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

F.Movendo um arquivo para um novo local

O exemplo a seguir move o arquivo Test1dat2 criado no exemplo A para um novo diretório.

ObservaçãoObservação

Você deve mover o arquivo fisicamente para o novo diretório antes de executar este exemplo. Em seguida, interrompa e reinicie a instância do SQL Server ou coloque o banco de dados AdventureWorks2012 OFFLINE e, em seguida, ONLINE para implementar a alteração.

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

G.Movendo tempdb para um novo local

O exemplo a seguir move o tempdb de seu local atual no disco para outro local no disco. Como o tempdb é recriado cada vez que o serviço MSSQLSERVER é iniciado, não é necessário mover fisicamente os arquivos de dados e de log. Os arquivos são criados quando o serviço é reiniciado na etapa 3. Enquanto o serviço não é reiniciado, o tempdb continua funcionando em seu local existente.

  1. Determine os nomes dos arquivos lógicos do banco de dados tempdb e o seu local atual no disco.

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

  2. Altere o local de cada arquivo 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. Verifique a alteração do arquivo.

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

  5. Exclua os arquivos tempdb.mdf e templog.ldf de seu local original.

H.Tornando um grupo de arquivos o padrão

O exemplo a seguir torna o grupo de arquivos Test1FG1 criado no exemplo B no grupo de arquivos padrão. Em seguida, o grupo de arquivos padrão é redefinido para o grupo de arquivos PRIMARY. Observe que PRIMARY deve ser delimitado por colchetes ou aspas.

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

I.Adicionando um grupo de arquivos usando ALTER DATABASE

O exemplo a seguir adiciona um FILEGROUP que contém a cláusula FILESTREAM ao banco de dados FileStreamPhotoDB.

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause to
--the FileStreamPhotoDB database.
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

Consulte também

Referência

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

sp_spaceused (Transact-SQL)

sys.databases (Transact-SQL)

sys.database_files (Transact-SQL)

sys.data_spaces (Transact-SQL)

sys.filegroups (Transact-SQL)

sys.master_files (Transact-SQL)

DBCC SHRINKFILE (Transact-SQL)

sp_filestream_force_garbage_collection (Transact-SQL)

Conceitos

Dados de objeto binário grande (Blob) (SQL Server)