Di: Sergey Gigoyan | Aggiornato: 2015-11-18 | Comments (9) | Related: More > Amministrazione del database
Problema
SQL Server consente di utilizzare più di un file di log delle transazioni, ma sorge la domanda se sia necessarioe qual è il vantaggio di avere più file di log delle transazioni. C’è un equivoco tra alcuni sviluppatori che avere file di registro multipletransaction può aumentare le prestazioni perché SQL Server può usarli in parallelo. SQL Server utilizzasolo un file di log delle transazioni al momento e in questo caso non c’è parallelismo. A volte può essere necessario più di un file di log ai fini della risoluzione dei problemi. Quindi, normalmente non è necessario avere più di un file di registro.Consideriamo un caso, quando il nostro database ha più di un file di registro e dovremmo conservarne solo uno.Questo suggerimento ha lo scopo di descrivere come rimuovere correttamente i file di registro non necessari e conservarne solo uno.
Soluzione
Prima di iniziare a illustrare come rimuovere i file di log non necessari, descriviamo brevemente come funziona SQL Server con i file di log: quando il database ha più di un file di registro, SQL Server continua a scrivere sul primo fino a quando non è pieno, quindi passa al secondo e così via. Dopo che l’ultimo file di registro diventa completo SQL Server ritorna al primo e il ciclo continua.Tuttavia, come abbiamo detto, a volte può essere richiesto più di un file di registro. Ad esempio quando il disco, dove si trova il file di logdiventa pieno e dobbiamo creare il secondo file di registro in un’altra posizione, ma dopo aver risolto il problema,dovremmo eliminare il secondo file di registro, perché non è necessario avere più di un file di registro.Ora, supponiamo di avere un database con due file di registro e il nostro compito è rimuovere il secondo.Lo script seguente crea il database TestDB con due file di registro e la tabella TestTable. Per eseguirlo da solo, è necessario sostituire”D:\SQL Dati” con un percorso di cartella esistente.
USE GOCREATE DATABASE ON PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\SQL DATA\TestDB.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'TestDB_log1', FILENAME = N'D:\SQL DATA\TestDB_log1.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%), ( NAME = N'TestDB_log2', FILENAME = N'D:\SQL DATA\TestDB_log2.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE TestDBGOCREATE TABLE TestTable(ID INT IDENTITY(1,1),Value BIGINT)--Change recovery model for TestDB to FULL (if your model database is in FULL recovery model, TestDB has been already created in this mode)ALTER DATABASE TestDB SET RECOVERY FULL
Ora comprendiamo la struttura fisica del log delle transazioni: internamente il file di log delle transazioni è costituito da file di log virtuali (VLF), che sono l’unità di gestione nel file di log delle transazioni. Significa che quando il motore di database cresce o restringe il file di registro, lo fa con VLF completi (ad esempio non può ridurre metà del VLF). La dimensione dei file di log virtuali e del loro numero nel file di log fisico non è fissa e viene gestita dinamicamente dal motore di database. Per monitorareil file di log delle transazioni internamenteutilizziamo il comando “DBCC LOGINFO”, che fornisce informazioni sui file di log virtuali. Nello script qui sotto abbiamo usato questo comando per il nostro database:
--Getting log files infoDBCC LOGINFO('TestDB')
E il risultato è il seguente:
FileID è ID file di log per il nostro database. Lo stato indica è VLF riutilizzabile ornot (valori possibili: 0-sì, 2-no). Come possiamo vedere c’è solo un VLF withStatus=2. Ora quando inseriremo i dati nella tabella dei test e monitoreremo come i file di registro stanno crescendo:
USE TestDBGO--Checking log information before insertionSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files--Inserting data into TestTable;WITH ValueTable AS(SELECT 1 nUNION ALL SELECT n+ 1FROM ValueTableWHERE n
Con questo esempio possiamo vedere che entrambi i file di registro sono cresciuti e ora ci sono VLF con “Status=2” anche nel secondo file di registro (FileID = 3):
Ora dobbiamo rimuovere TestDB_log2.file ldf. Si noti che possiamo rimuovere solo i file di registro secondari. Removingthe file di registro primario non è consentito da SQL Server.Ogni database ha un solo file di registro primario e il primo file di registro che viene creato inthe script di creazione del database è considerato il primario.Se proviamo a rimuovere il secondo file di registro:
USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
Riceveremo il seguente messaggio:
Possiamo rimuovere il file di log delle transazioni solo quando è vuoto, quindi dobbiamo prima svuotare it.To fallo, dovremmo eseguire il backup del registro delle transazioni. Dato che il nostro “TestDB” database isnewly creato e non ci sono i backup completi, abbiamo bisogno di emettere un backup completo del database per il database TestDB, dopo di che si può rilasciare agli backup del log delle transazioni:
--Full backupBACKUP DATABASE TestDB TO DISK =N'D:\SQL DATA\TestDB.bak'--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'
Il backup del log delle transazioni tronca il file di registro (ci sono alcune eccezioni, che sono fuori del campo di applicazione thistip). Il troncamento del log elimina i file di log virtuali inattivi dall’inizio del log logico e rilascia lo spazio nel file di log. Tuttavia, il troncamento non riduce le dimensioni di un file di registro fisico. Libera solo lo spazio in esso, che può essere riutilizzato. Eseguiamo di nuovo “DBCC LOGINFO” :
Come possiamo vedere non ci sono file di log virtuali nel “TestDB_log2.ldf ” file con Status=2 e ora il nostro file di registro è vuoto e pronto per la rimozione:
--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
La rimozione è riuscita.
Tuttavia, quando controlliamo nuovamente le informazioni di log, vedremo che il file di log logico esiste ancora:
--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Se eseguiamo un altro backup del registro, il file verrà eliminato:
--Transaction log backupBACKUP LOG TestDB TO DISK =N'D:\SQL DATA\TestDB.trn'--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
Passaggi successivi
- Tieni presente questo suggerimento per determinare l’utilizzo del log delle transazioni e come rimuovere un file di registro non necessario.
- Controlla queste risorse:
- Amministrazione del database | Log delle transazioni
- https://technet.microsoft.com/en-us/library/ms191433(v = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms179355(v = sql.105).aspx
- https://technet.microsoft.com/en-us/library/ms345414(v = sql.105).aspx
Circa l’autore
Sergey Gigoyan è un database di professionisti con oltre 10 anni di esperienza, con un focus sulla progettazione di database, sviluppo, ottimizzazione delle prestazioni, l’ottimizzazione, l’alta disponibilità, BI e DW design.Visualizza tutti i miei suggerimenti
Articolo Ultimo aggiornamento: 2015-11-18