Verificarea utilizării spațiului din jurnalul de tranzacții pentru bazele de date folosind comanda T-SQL

Puteți verifica utilizarea spațiului din jurnalul de tranzacții pentru toate bazele de date folosind următoarea comandă T-SQL:

DBCC SQLPERF (LOGSPACE);

GO

Nota bene: În SQL Server 2016 și versiunile ulterioare, este recomandata utilizarea DMV-ului sys.dm_db_log_space_usage pentru informații specifice bazei de date privind utilizarea jurnalului de tranzacții:

SELECT *
FROM sys.dm_db_log_space_usage;

De ce jurnalele de tranzacții continuă să crească

Înainte de a trunchia/reduce un jurnal de tranzacții, ar trebui să identificați motivul pentru care jurnalul continuă să crească. Iată cauzele frecvente:

• Lipsa copiilor de siguranță ale jurnalului de tranzacții în modelele de recuperare „Full” sau „Bulk-Logged”;
• Tranzacții de lungă durată sau neconfirmate;
• Replicarea SQL Server;
• Captarea datelor modificate (CDC);
• Grupuri de disponibilitate. Următoarea comandă afișează starea curentă a cozii de trimitere și de refacere a jurnalului pentru bazele de date dintr-un grup de disponibilitate Always On:
  SELECT *
  FROM sys.dm_hadr_database_replica_states;
• Oglindirea bazelor de date;
• Alte caracteristici care împiedică trunchierea jurnalului.

Pentru a verifica dacă o tranzacție deschisă împiedică trunchierea jurnalului, executați următoarea comandă:

DBCC OPENTRAN;

Comanda de mai sus afișează cea mai veche tranzacție activă din baza de date și vă poate ajuta să identificați motivul pentru care jurnalul de tranzacții nu poate fi trunchiat.

Analiza motivelor pentru care SQL Server nu poate reutiliza spațiul din jurnalul de tranzacții

Înainte de a trunchia/reduce un jurnal de tranzacții, ar trebui să stabiliți de ce SQL Server nu poate reutiliza spațiul din jurnal. Pentru aceasta, rulați următoarea interogare:

SELECT nume,
log_reuse_wait_desc
FROM sys.databases;

Coloana log_reuse_wait_desc indică motivul pentru care spațiul din jurnalul de tranzacții nu poate fi reutilizat.

Iată valorile obișnuite:

• LOG_BACKUP — este necesară o copie de rezervă a jurnalului de tranzacții;
• ACTIVE_TRANSACTION — o tranzacție deschisă împiedică trunchierea jurnalului;
• REPLICATION — replicarea reține înregistrările din jurnal;
• AVAILABILITY_REPLICA — o replică a grupului de disponibilitate nu a procesat jurnalul;
• DATABASE_MIRRORING — oglindirea bazei de date întârzie reutilizarea jurnalului.

Aceste informații vă ajută să identificați cauza principală a creșterii jurnalului de tranzacții înainte de a efectua operațiuni de reducere a dimensiunii acestuia.

Verificarea numărului de fișiere de jurnal virtuale (VLF) din jurnalul de tranzacții

Jurnalele de tranzacții SQL Server sunt împărțite intern în fișiere de jurnal virtuale (VLF). Creșterea excesivă a jurnalului și operațiunile frecvente de extindere automată pot genera mii de fișiere VLF, ceea ce poate afecta negativ performanța la pornire, recuperare, copiere de rezervă și restaurare. Înainte de a micșora un jurnal de tranzacții, ar trebui să verificați numărul actual de fișiere VLF.

Pentru SQL Server 2016 SP2 și versiunile ulterioare, utilizați următoarele:

SELECT *
FROM sys.dm_db_log_info(DB_ID());

Pentru a afișa numărul de VLF-uri din baza de date curentă, folosiți următoarea comandă:

SELECT COUNT(*) AS VLFCount
FROM sys.dm_db_log_info(DB_ID());

În cazul în care utilizați versiuni mai vechi de SQL Server, folosiți următoarele:

DBCC LOGINFO;

În cazul în care jurnalul de tranzacții conține un număr excesiv de mare de VLF-uri, reducerea și apoi extinderea jurnalului în pași de dimensiuni adecvate vă pot ajuta să reduceți fragmentarea VLF-urilor și să îmbunătățiți performanța jurnalului de tranzacții.

Cum se trunchiază jurnalele de tranzacții în MS SQL Server

Dacă dimensiunea fișierelor jurnalului de tranzacții (*.ldf) este fixă sau dacă spațiul pe disc este insuficient, toate operațiunile de modificare a bazei de date vor fi indisponibile. Este posibil să întâmpinați următoarele erori la conectarea la o bază de date MS SQL:

The transaction log for database is full due…

or

ODBC error: (42000) – [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database ‘database’ is full. Back up the transaction log for the database to free up some log space.

Aceasta este situația care apare de obicei atunci când se utilizează un model de recuperare completă. Pentru a elibera spațiu, ar trebui să faceți o copie de rezervă a jurnalelor de tranzacții (cea mai sigură metodă) sau puteți șterge jurnalele schimbând modul de recuperare al bazei de date la „Simplu”.

Avertisment. Rețineți că trecerea unei baze de date de producție de la modelul de recuperare „Full” la „Simple” trebuie efectuată numai ca operațiune de urgență, atunci când copiile de rezervă ale jurnalului de tranzacții nu sunt disponibile. Rețineți că această operațiune întrerupe lanțul de copii de rezervă ale jurnalului de tranzacții și împiedică recuperarea la un moment dat (până la efectuarea unei noi copii de rezervă complete a bazei de date).

Modificarea modelului de recuperare al MS SQL Server în timp real

Este posibil să modificați modelul de recuperare al MS SQL Server în timp real, dar, pentru a reduce riscurile, este recomandabil să comutați baza de date în modul „read-only”.

Deschideți SQL Server Management Studio (SSMS), selectați baza de date (cu jurnale de tranzacții voluminoase), faceți click dreapta și selectați „Properties”. Accesați secțiunea „Options” și comutați modelul de recuperare al bazei de date la „Simple”.

Sa presupunem ca in acest caz, jurnalele de tranzacții sunt trunchiate automat, dar ocupă în continuare mult spațiu, deoarece spațiul trunchiat nu va fi eliberat. Pentru a reduce dimensiunea jurnalelor, puteți, de asemenea, să micșorați fișierele.

Pentru a face asta, faceți clic dreapta pe DB, selectați Tasks > Shrink > Files.. În câmpul Tip File selectați Log, iar în câmpul File name specificați numele fișierului jurnal. În secțiunea Shrink action alegeți Reorganize pages înainte de eliberarea spațiului neutilizat, setați dimensiunea dorită a fișierului și faceți clic pe OK.

Opțiuni de reducere a dimensiunii

Aici veți găsi trei opțiuni de reducere a dimensiunii:

• Release unused space — această opțiune va recupera spațiul neutilizat din fișierul jurnalului de tranzacții și va reduce dimensiunea fișierului până la ultima extensie alocată. Permite reducerea dimensiunii fișierului fără a muta datele;
• Reorganize pages before releasing unused space — eliberează spațiul neutilizat și încearcă să relocheze rândurile în pagini nealocate;
• Empty file by migrating the data to other files in the same filegroup — este utilizată pentru a muta toate datele din fișierul specificat către alte fișiere din același grup de fișiere. Fișierul golit va fi eliminat ulterior.

După finalizarea unei operațiuni, comutați modul de restaurare al bazei de date înapoi la „Full” sau „Bulk-Logged”.

ATENȚIE! Această opțiune vă permite să reduceți rapid dimensiunea fișierelor jurnalelor de tranzacții. Totuși, aceasta duce la pierderea înregistrărilor tranzacțiilor efectuate de la ultima copie de rezervă. De aceea, ar trebui să efectuați o copie de rezervă completă a bazei de date cât mai curând posibil.

Activați opțiunea „Auto Shrink”

Rețineți că opțiunea „Auto Shrink” nu este, în general, recomandată pentru bazele de date de producție.

În general, ar trebui să evitați opțiunea „Auto Shrink” și să o luați în considerare doar pentru bazele de date mici, care nu sunt de producție. Accesați „DB Options” > și setați valoarea parametrului „Auto Shrink” din secțiunea „Automatic” la „True”. După ce activați opțiunea „Auto Shrink”, MS SQL va efectua compresia automată numai dacă spațiul neutilizat depășește 25% din dimensiunea totală a volumului. Cu toate acestea, este mai bine să comprimați fișierele de tranzacții decât fișierele de date.

Utilizarea comenzii Transact-SQL pentru trunchierea jurnalului de tranzacții

Notă. Pentru bazele de date de producție care rulează în modelul de recuperare completă, ar trebui să utilizați copierea de rezervă a jurnalului de tranzacții ca metodă preferată de trunchiere a înregistrărilor din jurnal. Ar trebui să luați în considerare trecerea la modelul de recuperare simplu numai în cazul scenariilor de urgență pentru eliberarea spațiului de stocare.

Înainte de a executa orice operațiuni legate de modelul de recuperare sau de reducere a dimensiunii, trebuie să vă asigurați că există o copie de rezervă completă a bazei de date pentru fiecare bază de date de utilizator.

De asemenea, puteți comuta baza de date MS SQL în modul de recuperare simplu și puteți reduce dimensiunea fișierului jurnal folosind un script T-SQL:

USE ″YourDBName″
ALTER DATABASE ″YourDBName″ SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (″YourDBName_log″, ″Desired_size_in_MB″);
—

De exemplu, pentru a reduce dimensiunea unui fișier jurnal la 4 GiB (4096 MiB), folosiți comanda:

USE YourDBName;
GO

ALTER DATABASE YourDBName
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (YourDBName_log, 4096);
GO

ALTER DATABASE YourDBName
SET RECOVERY FULL;
GO

Asigurați-vă că faceți o copie de rezervă a bazei de date în modelul de recuperare completă.

O altă modalitate de a reduce dimensiunea jurnalului de tranzacții SQL este să faceți o copie de rezervă a jurnalelor bazei de date folosind comanda:

BACKUP LOG YourDBName
TO DISK = «D:\Backup\YourDBName.trn»;

Rețineți că BackupDevice trebuie creat în prealabil folosind sp_addumpdevice; alternativ, puteți utiliza TO DISK pentru a efectua o copie de rezervă directă într-un fișier.

Avertisment. Rețineți că acest script schimbă toate bazele de date ale utilizatorilor de la modelul de recuperare „Full/Bulk-Logged” la „Simple”, reduce jurnalele de tranzacții ale acestora și apoi le readuce la modelul de recuperare „Full”. Rețineți că rularea acestui script întrerupe lanțul de copii de siguranță ale jurnalului de tranzacții și face imposibilă recuperarea la un moment dat până la crearea unei noi copii de siguranță complete a bazei de date. Ar trebui să utilizați această abordare doar ca operațiune de urgență și să verificați fiecare cerință de recuperare a bazei de date înainte de execuție.

În situații de urgență, puteți utiliza următorul script pentru a comuta toate bazele de date ale utilizatorilor la modelul de recuperare „Simple” și pentru a reduce jurnalele de tranzacții ale acestora. Verificați cerințele de recuperare ale fiecărei baze de date înainte de a utiliza această abordare:

DECLARE @db_name nvarchar(100);
DECLARE @logname nvarchar(255);
DECLARE @sql nvarchar(max);

DECLARE cursor_size_srv CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN (‘tempdb’,’master’,’msdb’,’model’)
ORDER BY name;

OPEN cursor_size_srv;
FETCH NEXT FROM cursor_size_srv INTO @db_name;

WHILE (@@FETCH_STATUS = 0)
BEGIN

SELECT @logname = mf.name
FROM sys.master_files mf
WHERE mf.database_id = DB_ID(@db_name)
AND mf.type_desc = ‘LOG’;

SET @sql = ‘
USE [‘ + @db_name + ‘];

ALTER DATABASE [‘ + @db_name + ‘] SET RECOVERY SIMPLE;

DBCC SHRINKFILE (‘ + QUOTENAME(@logname) + ‘, 10, TRUNCATEONLY);

ALTER DATABASE [‘ + @db_name + ‘] SET RECOVERY FULL;
‘;

EXEC sp_executesql @sql;

FETCH NEXT FROM cursor_size_srv INTO @db_name;
END;

CLOSE cursor_size_srv;
DEALLOCATE cursor_size_srv;

Rețineți că, atunci când se utilizează TRUNCATEONLY, parametrul de dimensiune țintă este ignorat. SQL Server eliberează doar spațiul neutilizat de la sfârșitul fișierului jurnal și nu garantează reducerea la o dimensiune specifică.

Cum se mută fișierele jurnalului de tranzacții pe o altă unitate?

Dacă nu puteți extinde unitatea pe care este stocat jurnalul de tranzacții (LDF), îl puteți muta pe o altă unitate care dispune de suficient spațiu liber. Din păcate, mutarea jurnalului va necesita detașarea și reatașarea bazei de date (baza de date SQL va fi indisponibilă pentru o perioadă de timp).

Abordarea recomandată este să actualizați metadatele fișierului folosind comanda ALTER DATABASE … MODIFY FILE, să scoateți baza de date offline (sau să opriți serviciul SQL Server în timpul unei ferestre de întreținere), să mutați fișierul jurnal și apoi să repuneți baza de date online.

Rulați următoarea comandă pentru a afla locația curentă a fișierului jurnal de tranzacții și dimensiunea sa maximă:

select file_id, type, type_desc, name, physical_name, state, state_desc, size

din sys.database_files

În exemplul nostru, baza de date are un singur fișier jurnal de tranzacții, cu calea D:\msdb\mysqldb.ldf.

Pentru a obține informații despre dimensiunea actuală a fișierului jurnal de tranzacții și procentul de utilizare al acestuia, executați comanda T-SQL:

declare @logSpace tabel (

dbName varchar(100),

logSizeMB float,

logSpaceUsed float,

status int

)

insert into @logSpace

execute(«dbcc sqlperf(»’LogSpace’«)»)

select * from @logSpace where dbName = ‘mysqldb’

Pasul 1. Modificați calea către fișierul jurnal în metadatele SQL Server

USE master;
GO

ALTER DATABASE mysqldb
MODIFY FILE
(
NAME = mysqldb_log,
FILENAME = «M:\msdb\mysqldb.ldf»
);
GO

Pasul 2. Deconectați baza de date

ALTER DATABASE mysqldb
SET OFFLINE;
GO

Pasul 3. Mutarea fișierului .ldf

Mutați fișierul jurnalului de tranzacții în noua locație folosind File Explorer sau un alt instrument de gestionare a fișierelor.

Pasul 4. Repune baza de date în funcțiune

ALTER DATABASE mysqldb
SET ONLINE;
GO

Dacă nu este acceptabilă o perioadă lungă de nefuncționare a bazei de date SQL Server, puteți adăuga un nou fișier jurnal la baza de date pe o altă unitate de stocare cu suficient spațiu liber. Pentru a adăuga un fișier jurnal suplimentar, utilizați comanda ALTER DATABASE [dbname]ADD LOG FILE.

De exemplu, vom adăuga un fișier jurnal suplimentar pe un alt disc pentru baza de date mysqldb:

ALTER DATABASE mysqldb ADD LOG FILE
(
NAME = mysqldb,
FILENAME = ‘D:\mssql\data\mysqldb2.ldf’,
SIZE = 1000MB,
MAXSIZE = 2000MB,
FILEGROWTH = 5%
);
GO

Rețineți că metoda „detach/attach” funcționează în continuare și poate fi utilă în anumite situații de recuperare, dar Microsoft recomandă, în general, utilizarea comenzii ALTER DATABASE … MODIFY FILE pentru operațiunile de rutină de relocare a fișierelor (deoarece aceasta păstrează metadatele bazei de date și reduce riscul operațional).

Nota Bene: Microsoft nu recomandă utilizarea mai multor fișiere jurnal pentru o singură bază de date ca soluție pe termen lung. Această soluție vă permite să porniți rapid baza de date atunci când rămâneți fără spațiu pe unitatea de stocare a jurnalelor de tranzacții. După ce ați identificat motivul pentru care jurnalul de tranzacții este plin și nu poate fi trunchiat, ar trebui să dezactivați un astfel de fișier.