Felesleges tranzakciós log file törlése

Ismét egy ki nem javított SQL Server hibába futottam bele. A történet alapját több, általam átnézett SQL Server szolgáltatja. Természetesen a tranzakciós log nem felesleges, csak nincs értelme egynél több file-nak, normális esetben.

A probléma

Van egy adatbázis, aminek két tranzakciós logja volt. Értelme ugyan nincs, mert semmiféle teljesítménybeli, rendelkezésre állási vagy bármi egyéb előnnyel nem jár. A tranzakciós log – több file esetén – először elkezdi használni az elsőt, majd ha az megtelt, akkor a többit, stb. Ráadásul a tranzakciós log szekvenciálisan van írva, így a párhuzamosított írási művelet nincs, mint az adat file esetében. Mit tesz ilyenkor az egyszeri konzulens? Hát persze, megpróbálja kitörölni a felesleges log file-t és rendbe rakja a mentési stratégiát, hogy ne 200 GB++ log legyen egy 40 GB-os adatbázisnak ;-).

Megpróbáltuk eltávolítani a logot, majd az SSMS-ben még mindig láttuk. Itt ismét megpróbáltuk letörölni, de az alábbi hibaüzenetet kaptuk:

Error: Msg 5009, Level 16, State 9, One or more files listed in the statement could not be found or could not be initialized.

Érdekes… na pont ezért ennek egy picit utánajártam.

A javítás

Elvileg ezt valamelyik SP vagy CU javítja, de mégsem! Erről ennyit… :-S

Akkor most mi legyen?

Természetesen javítsuk ki :-).Az alábbi példa alapján bárki simán megoldhatja ezt a problémát – a probléma reprodukálható. Első lépésként csináljunk egy adatbázist:

 1USE [master];
 2GO
 3IF DATABASEPROPERTY('MultiFileDb', 'Version') IS NOT NULL
 4    BEGIN
 5        ALTER DATABASE [MultiFileDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 6        DROP DATABASE [MultiFileDb];
 7    END
 8GO
 9 
10CREATE DATABASE [MultiFileDb] ON  PRIMARY
11( NAME = N'MultiFileDb', FILENAME = N'C:\temp\MultiFileDb.mdf' ,
12SIZE = 8192KB , FILEGROWTH = 1024KB )
13 LOG ON
14( NAME = N'MultiFileDb_log', FILENAME = N'C:\temp\MultiFileDb.ldf' ,
15  SIZE = 8192KB , FILEGROWTH = 10%),
16( NAME = N'MultiFileDb_log1', FILENAME = N'C:\temp\MultiFileDb1.ldf' ,
17  SIZE = 8192KB , FILEGROWTH = 10%)
18GO

Majd mindjárt készítsünk is egy FULL mentést.

1BACKUP DATABASE [MultiFileDb] TO DISK = N'C:\temp\multifiledb.bak'
2WITH INIT, COMPRESSION, STATS = 10;
3GO

Ezek után adjunk neki egy kicsit, hogy a tranzakciós logban is legyen valami, így érdekesebb lesz ;-).

 1USE [MultiFileDb]
 2GO
 3CREATE TABLE Table1
 4(
 5 Col1 int IDENTITY
 6);
 7GO
 8 
 9SET NOCOUNT ON;
10INSERT INTO Table1 DEFAULT VALUES
11GO 30000
12SET NOCOUNT OFF;

A fenti kód eltarthat egy ideig. Ezek után próbáljuk meg eltávolítani a MultiFileDb_log1 névre hallgató tranzakciós logot:

1ALTER DATABASE [MultiFileDb] REMOVE FILE [MultiFileDb_log1]

Hmm, nem igazán megy, mert az alábbi hibaüzenet jön vissza:

Msg 5042, Level 16, State 2, Line 1 The file 'MultiFileDb_log1' cannot be removed because it is not empty.

Persze ez normális is. Ahogy ezt már fentebb írtam, elkezdi az első file-nál az írást, majd ha az megtelik, akkor a másodikkal, stb. folytatja, ha azok is betelnek, akkor növeli a méretét ezeknek. Mivel van aktív tranzakció ebben a file-ban, így ezt nem tudjuk törölni.

Ahhoz, hogy törölni tudjuk, az alábbiaknak kell teljesülni:

• ne legyen aktív tranzakció a log file-ban,
• üres legyen a log file

Ezt az alábbi kód segítségével érhetjük el:

1--log backup
2BACKUP LOG [MultiFileDb] TO DISK = N'C:\temp\multifiledb.bak'
3WITH COMPRESSION, STATS = 10;
4 
5--empty log file
6USE [MultiFileDb]
7GO
8DBCC SHRINKFILE (N'MultiFileDb_log1' , EMPTYFILE)

Most próbáljuk meg ismét eltávolítani a “felesleges” log file-t.

1ALTER DATABASE [MultiFileDb] REMOVE FILE [MultiFileDb_log1]

Hurrá, most ezt kaptuk: The file 'MultiFileDb_log1' has been removed. De biztos ez??? Lássuk:

1USE [MultiFileDb]
2GO
3EXEC sp_helpfile

Ez mintha jó lenne:

1USE [MultiFileDb]
2GO
3SELECT * FROM sys.master_files WHERE [database_id] = DB_ID()

1USE [MultiFileDb]
2GO
3SELECT * FROM sys.database_files

Egyre jobb, a UI-nak fogalma sincs, hogy eltávolítottam a MultiFileDb_log1 log file-t. Most megpróbálom a UI-ról eltávolítani, ebben az esetben az alábbi hibát kapom:

Jaj, jaj :-), meg is érkeztünk az elején emlegetett hibaüzenethez. Persze, hogy nem tudja eltávolítani ezt a logot, mert már megtettük. A c:\temp mappában már nincs ott a MultiFileDb_log1.ldf és az SQL is offline-ként tartja számon. A UI a sys.master_files-ból doglozik, így ezt meg is jeleníti. Hogyan tovább? Indítsam újra az SQL Server szolgáltatást vagy csatoljam le és vissza az adatbázist? Hát persze, hogy nem, az nem profi megoldás, és egyébként sem segít. A probléma a sys.sysfiles1 nem dokumentált táblában keresendő, de ezt most nem vesézném ki, hanem a megoldást mutatom:

Adjunk az adatbázishoz egy új adat file-t, majd töröljük is ki azonnal:

1USE [master]
2GO
3ALTER DATABASE [MultiFileDb]
4ADD FILE ( NAME = N'datafile1', FILENAME = N'C:\temp\datafile1.mdf' ,
5SIZE = 3072KB , FILEGROWTH = 1024KB )
6TO FILEGROUP [PRIMARY]
7GO
8ALTER DATABASE [MultiFileDb] REMOVE FILE [datafile1]
9GO

Ezek után egy log mentés más meg is oldja a problémát.

1BACKUP LOG [MultiFileDb] TO DISK = N'C:\temp\multifiledb.bak'
2WITH COMPRESSION, STATS = 10;

Ezek után ha megnézzük a sys.database_files, sys.master_files és az SSMS-t, akkor nem fogjuk látni már.

Tanulság

Ne legyen egynél több log file, mert nem jelent semmilyen előnyt, csak problémát okoz, kivéve pár speciális esetet. Ismét fontos megemlítenem, ha egy SP vagy CU azért kerülne fel a szerverre, mert javít egy olyan problémát, amivel találkozunk, minden esetben teszteljük le előtte. Nagyon tanulságos eset az SQL 2012 SP1 során felmerült probléma – lásd itt.