Gestione del Database TempDB in SQL Server: Guida Dettagliata

La gestione dei database MS SQL può presentare delle sfide, specialmente quando si tratta di ottimizzare le prestazioni e garantire la corretta configurazione dei file di sistema. Questo articolo si concentra sul database tempdb, un componente cruciale di SQL Server, fornendo informazioni dettagliate su come gestirlo efficacemente.

Capita frequentemente che, durante i progetti di consolidamento di una infrastruttura SQL, ci sia la necessità di spostare i file dei database di sistema, tra cui il TempDB, su una nuova unità.

Cos'è il TempDB?

In MS SQL Server, il tempdb è un database di sistema essenziale. Le sue funzioni principali includono:

• Memorizzazione di tabelle temporanee.
• Gestione di cursori.
• Archiviazione di procedure memorizzate.
• Supporto per altri oggetti interni creati dal motore del database.

All'avvio di SQL Server, tempdb viene ricreato usando una copia del database model e tempdb viene ripristinato all'ultima dimensione configurata.

Le dimensioni configurate sono le ultime dimensioni esplicite impostate usando un'operazione di modifica delle dimensioni del file, ad esempio ALTER DATABASE che usa l'opzione MODIFY FILE o le istruzioni DBCC SHRINKFILE o DBCC SHRINKDATABASE.

Come Spostare il TempDB

Partiamo con una nota importante: SQL Server non supporta lo spostamento del database TempDB utilizzando il backup-restore e gli altri metodi di detach-attach del database.

Spostare il TempDB è un’operazione semplice se abbiamo chiari tutti i passaggi.

Capita frequentemente che durante i progetti di consolidamento di una infrastruttura SQL, ci sia la necessità di spostare i file dei database di sistema, tra cui il TempDB, su una nuova unità.

Nella mia macchina di sviluppo, il TempDB è composto da 5 file: 4 file dati (nelle posizioni 1,3,4,5) ed un file di log (posizione 2).

Il passaggio successivo sarà creare le rispettive cartelle sulla nuova unità in cui si desidera salvare il TempDB.

Eseguiamo il comando ALTER DATABASE riportato di seguito per modificare il percorso del TempDB.

Come notate, il messaggio indica chiaramente che il nuovo percorso sarà utilizzato al prossimo riavvio del database.

Come accennato precedentemente, ogni volta che il servizio SQL viene fatto partire, il TempDB viene ricreato.

Poiché tempdb viene ricreato a ogni avvio dell'istanza di SQL Server, non è necessario spostare fisicamente i file di dati e di log. I file vengono creati nel nuovo percorso quando il servizio viene riavviato.

Passaggi per lo spostamento:

1. Verificare che l'account del servizio per il motore di database SQL Server disponga delle autorizzazioni complete per il nuovo percorso dei file.
2. Arrestare l'istanza di SQL Server per eseguire la manutenzione.
3. Copiare il file di database o i file nella nuova posizione.
4. Riavviare il servizio SQL Server per l'istanza del server.
5. Verificare la modifica ai file eseguendo la query appropriata.

Fate particolare attenzione ai diritti di lettura e scrittura per l’utente di servizio: è la prima causa di fallimento di tutto il processo descritto.

Recupero da Errore

Se è necessario spostare un file a causa di un errore hardware, eseguire la procedura seguente per rilocare il file in una nuova posizione. Questa procedura è valida per tutti i database di sistema ad eccezione dei database master e Resource.

1. Verificare che l'account del servizio per il motore di database SQL Server disponga delle autorizzazioni complete per il nuovo percorso dei file.
2. Avviare l'istanza di SQL Server in modalità di recupero del solo master digitando uno dei comandi appropriati al prompt dei comandi. Usare il parametro di avvio 3608 impedisce a SQL Server di avviare e recuperare automaticamente database ad eccezione del database master. I parametri specificati in questi comandi fanno distinzione tra maiuscole e minuscole.
3. Immediatamente dopo l'avvio del servizio con il flag di traccia 3608 e /f, avviare una connessione sqlcmd al server per richiedere la connessione singola disponibile.

Modifica del Percorso Predefinito del Database

1. Dal menu Start, individuare e avviare Gestione configurazione SQL Server.
2. Nella casella Parametri esistenti, selezionare il parametro -d. Nella casella Specificare un parametro di avvio, modificare il parametro sul nuovo percorso del file di dati master.
3. Nella casella Parametri esistenti, selezionare il parametro -l. Nella casella Specificare un parametro di avvio, modificare il parametro sul nuovo percorso del file di resoconto master. Il valore del parametro per il file di dati deve seguire il parametro -d e il valore per il file di log deve seguire il parametro -l .
4. Nella finestra di dialogo Configura log degli errori di SQL Server Agent specificare il nuovo percorso del file SQLAGENT.OUT.

Microsoft consiglia comunque di modificare anche la voce del Registro di sistema in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance_ID\Setup, dove instance_ID è simile a MSSQL13.MSSQLSERVER. In tale hive, modificare il valore SQLDataRoot impostando il nuovo percorso della nuova posizione dei file di database master.

Gestione delle Dimensioni del TempDB

È possibile usare uno dei metodi seguenti per modificare le dimensioni di tempdb. Le prime tre opzioni sono descritte in questo articolo. Per impostazione predefinita, il database tempdb è configurato per l'aumento automatico in base alle esigenze. Pertanto, col tempo questo database può aumentare in modo imprevisto fino a raggiungere una dimensione maggiore di quella desiderata.

È possibile ridurre tempdb mentre l'attività tempdb è in corso. Tuttavia, è possibile che si verifichino altri errori, ad esempio blocchi, stalli e altri ancora, che possono impedire il completamento del processo di riduzione.

SQL Server registra solo informazioni sufficienti nel log delle transazioni tempdb per eseguire il rollback di una transazione, ma non per ripetere le transazioni durante il recupero del database. Questa funzione migliora le prestazioni delle dichiarazioni INSERT in tempdb. Inoltre, non è necessario registrare le informazioni per ripetere le transazioni perché tempdb viene ricreata ogni volta che si riavvia SQL Server.

Questo comando opera solo sui file logici tempdb predefiniti tempdev e templog. Se vengono aggiunti ulteriori file a tempdb, è possibile compattarli dopo il riavvio di SQL Server come servizio. Tutti i file tempdb vengono ricreati durante l'avvio. Tuttavia, questi file sono vuoti e possono essere rimossi.

Utilizzo di DBCC SHRINKDATABASE

DBCC SHRINKDATABASE riceve il parametro target_percent. Questa è la percentuale di spazio che si desidera rendere disponibile nel file di database dopo la compattazione del database.

Determinare lo spazio attualmente usato in tempdb tramite la stored procedure sp_spaceused. Calcolare quindi la percentuale di spazio disponibile lasciato per l'uso come parametro per DBCC SHRINKDATABASE.

Si supponga che tempdb abbia due file: il file di dati primario (tempdb.mdf) di 1024 MB e il file di resoconto (tempdb.ldf) pari a 360 MB. Si supponga che sp_spaceused segnali che il file di dati primario contenga 600 MB di dati.

Connettersi a SQL Server con SSMS, Azure Data Studio o sqlcmd, quindi eseguire il comando Transact-SQL seguente.

Esistono limitazioni con il comando DBCC SHRINKDATABASE in tempdb. Le dimensioni di destinazione per i file di dati e di resoconto non possono essere inferiori alle dimensioni specificate al momento della creazione del database o inferiori alle ultime dimensioni impostate in modo esplicito tramite un'operazione di modifica delle dimensioni del file, ad esempio ALTER DATABASE che usa l'opzione MODIFY FILE.

Utilizzo di DBCC SHRINKFILE

Usare il comando DBCC SHRINKFILE per compattare i singoli file tempdb. DBCC SHRINKFILE offre maggiore flessibilità rispetto DBCC SHRINKDATABASE in quanto è possibile usarlo in un singolo file di database senza influire sugli altri file appartenenti allo stesso database.

DBCC SHRINKFILE riceve il parametro target_size. Determinare le dimensioni desiderate per il file di dati primario (tempdb.mdf), il file di resoconto (templog.ldf) e i file extra aggiunti a tempdb.

Connettersi a SQL Server con SSMS, Azure Data Studio o sqlcmd, quindi eseguire il comando Transact-SQL seguente per compattare i file di database specifici.

Un vantaggio di DBCC SHRINKFILE è che può ridurre le dimensioni di un file a una dimensione inferiore a quella originale.

È possibile eseguire DBCC SHRINKFILE in uno qualsiasi dei file di dati o di resoconto.

Configurazione Avanzata: Split TempDB

La realtà è che avremo principalmente bisogno di creare più file negli ambienti di produzione. Esistono molti consigli non solo su come crearli, ma anche sulla quantità di file da creare.

Se tutti i file hanno esattamente le stesse dimensioni, SQL Server utilizza i file in modo “Round Robin“, ripartendo il carico equamente tra i file.

Vorrei solo suggerire di dividere il database tempdb in quattro file di dati (se si dispone di quattro o otto core) o otto file di dati (per 16, 32, 64 o più core) e SOLO un file di registro. Idealmente, dobbiamo anche localizzarli in diverse unità RAID1, RAID5 o RAID10.

Abbiamo visto come eseguire lo split TempDb in SqlServer e vari consigli sul perchè fare questa configurazione.

Utilizzando gli script sopra, personalizzati in base alle esigenze, è possibile scoprire dove si trova tempdb e come vengono chiamati i file, scrivere lo spostamento dei file esistenti e aggiungerne di nuovi per facilitare o rimuovere la contesa.

Esempio di Script per Enumerare i Dischi:

Di seguito un esempio che utilizza xp_fixeddrives per enumerare i dischi disponibili sulla macchina che esegue SQL Server; si provvede di seguito a scartare dischi rimovibili e cd-rom...per continuare l'esplorazione del file system potremmo quindi utilizzare xp_dirtree opportunamente valorizzato, ma in questo caso possiamo tranquillamente andare oltre, e quindi il secondo batch provvede a verificare la presenza della directory richiesta nella root (o nell'albero) del disco ottenuto...

Errori Comuni e Come Risolverli

A volte, durante la gestione del tempdb, possono verificarsi errori. Un esempio è:

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header.

Questo errore non indica necessariamente un danneggiamento reale in tempdb. Tuttavia, potrebbero esserci altri motivi per errori di danneggiamento dei dati fisici, ad esempio problemi del sottosistema I/O. In questi casi, è fondamentale analizzare e risolvere i problemi del sottosistema I/O.

Tabella Riepilogativa delle Operazioni Comuni

Operazione	Descrizione	Strumenti/Comandi
Spostamento del TempDB	Trasferire i file del TempDB su una nuova unità.	ALTER DATABASE, SQL Server Management Studio
Ridimensionamento del TempDB	Modificare le dimensioni dei file del TempDB.	DBCC SHRINKDATABASE, DBCC SHRINKFILE
Split del TempDB	Dividere il TempDB in più file per migliorare le prestazioni.	ALTER DATABASE, SQL Server Management Studio
Verifica dello spazio utilizzato	Controllare lo spazio attualmente utilizzato nel TempDB.	sp_spaceused

tags: #sql #tempdb #mdf