Movendo e criando novos arquivos tempdb com segurança

Para mover tempdbarquivos, você só precisa fazer o seguinte:

alter database tempdb
modify file
(
    name = tempdev,
    filename = 'C:\YourNewTempdbDir\tempdb.mdf'
)
go

alter database tempdb
modify file
(
    name = templog,
    filename = 'C:\YourNewTempdbDir\templog.ldf'
)
go

Se você deseja adicionar um novo arquivo a tempdb, basta fazer o seguinte (desde que queira adicioná-lo ao grupo de PRIMARYarquivos ou criar o seu próprio):

alter database tempdb
add file
(
    name = tempdb2,
    filename = 'C:\YourNewTempdbDir\Tempdb2.ndf'
)
go

Para que essas alterações tenham efeito, você precisará reiniciar o serviço SQL Server. Portanto , no que diz respeito à minimização do tempo de inatividade, você está limitado ao tempo necessário para a reinicialização do serviço . Você não precisa se preocupar em mover os tempdbarquivos de banco de dados pré-existentes, pois o SQL Server sempre recria os arquivos e os novos locais/arquivos serão criados na inicialização do serviço.

Quanto ao "1 arquivo de dados tempdb por núcleo", isso é em grande parte um mito. A abordagem correta é monitorar a tempdbcontenção de arquivo para as páginas PFS (Page Free Space), Global Allocation Map (GAM) e Shared Global Allocation Map (SGAM). Consulte este artigo para obter uma consulta (link alternativo) que examina o sys.dm_os_waiting_tasksDMV para ver quanta tempdbcontenção de arquivo existe. Então você precisa sair disso, em vez de apenas cobrir tempdbcom a mesma quantidade de arquivos que existem núcleos. É a abordagem mais aconselhável.

1. Para mover o tempdb, execute:

   ALTER DATABASE tempdb 
   MODIFY FILE ( name=tempdev, filename='D:\Newpath\tempdb.mdf') 
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE ( name=templog, filename='D:\Newpath\templog.ldf') 
   GO
   

   Em seguida, reinicie o SQL Server Service (MSSQLServer).

2. Número de arquivos em tempdb - consulte o artigo de Paul Randall: Um mito de DBA do SQL Server por dia: (30/12) tempdb deve sempre ter um arquivo de dados por núcleo de processador

Do conselho da Microsoft :

Como regra geral, se o número de processadores lógicos for menor ou igual a 8, use o mesmo número de arquivos de dados que os processadores lógicos.

Se o número de processadores lógicos for maior que 8, use 8 arquivos de dados e, se a contenção continuar, aumente o número de arquivos de dados em múltiplos de 4 (até o número de processadores lógicos) até que a contenção seja reduzida a níveis aceitáveis ​​ou faça alterações na carga de trabalho/código.

Mover os arquivos TempDB é um processo de 2 etapas:

1. Diga ao SQL para onde você deseja que seus novos arquivos TempDB vão (isso não tem tempo de inatividade)
2. Reinicie o SQL Serverserviço para que a alteração tenha efeito (este é o tempo de inatividade mínimo que você precisa)

Para informar ao SQL onde criar os novos arquivos TempDB, você pode usar:

DECLARE @newDriveAndFolder VARCHAR(8000);

SET @newDriveAndFolder = 'Z:\YourTempDBfolder';

SELECT [name] AS [Logical Name]
    ,physical_name AS [Current Location]
    ,state_desc AS [Status]
    ,size / 128 AS [Size(MB)] --Number of 8KB pages / 128 = MB
    ,'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + QUOTENAME(f.[name])
    + CHAR(9) /* Tab */
    + ',FILENAME = ''' + @newDriveAndFolder + CHAR(92) /* Backslash */ + f.[name]
    + CASE WHEN f.[type] = 1 /* Log */ THEN '.ldf' ELSE '.mdf' END  + ''''
    + ');'
    AS [Create new TempDB files]
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb')
ORDER BY f.[type];

Isso gerará as instruções T-SQL que você precisa executar para mover os arquivos para o novo drive:\folderdesejado. (clique na imagem para aumentar)

Depois de executar suas instruções de movimentação, você pode executar a consulta acima novamente para verificar se a Current Locationcoluna agora está mostrando seu novo arquivo drive:\folder.

Quando estiver satisfeito com suas alterações, reinicie o serviço SQL Server .