I am Berke

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:

USE [master];
GO
IF DATABASEPROPERTY('MultiFileDb', 'Version') IS NOT NULL
	BEGIN
		ALTER DATABASE [MultiFileDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE [MultiFileDb];
	END
GO

CREATE DATABASE [MultiFileDb] ON  PRIMARY 
( NAME = N'MultiFileDb', FILENAME = N'C:\temp\MultiFileDb.mdf' , 
SIZE = 8192KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'MultiFileDb_log', FILENAME = N'C:\temp\MultiFileDb.ldf' , 
  SIZE = 8192KB , FILEGROWTH = 10%), 
( NAME = N'MultiFileDb_log1', FILENAME = N'C:\temp\MultiFileDb1.ldf' , 
  SIZE = 8192KB , FILEGROWTH = 10%)
GO

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

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

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

USE [MultiFileDb]
GO
CREATE TABLE Table1
(
 Col1 int IDENTITY
);
GO

SET NOCOUNT ON;
INSERT INTO Table1 DEFAULT VALUES
GO 30000
SET 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:

ALTER 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:

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

--empty log file
USE [MultiFileDb]
GO
DBCC SHRINKFILE (N'MultiFileDb_log1' , EMPTYFILE)

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

ALTER DATABASE [MultiFileDb] REMOVE FILE [MultiFileDb_log1]

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

USE [MultiFileDb]
GO
EXEC sp_helpfile

Ez mintha jó lenne:

Rendben, de mi van a sys.master_files-ban és a sys.database_files-ban?

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

USE [MultiFileDb]
GO
SELECT * FROM sys.database_files

Ajaj, itt még látható a MultiFileDb_log1 file, de már offline. Hmm. Érdekes, de vajon mit mutat az SSMS?

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:

USE [master]
GO
ALTER DATABASE [MultiFileDb] 
ADD FILE ( NAME = N'datafile1', FILENAME = N'C:\temp\datafile1.mdf' , 
SIZE = 3072KB , FILEGROWTH = 1024KB ) 
TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [MultiFileDb] REMOVE FILE [datafile1]
GO

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

BACKUP LOG [MultiFileDb] TO DISK = N'C:\temp\multifiledb.bak' 
WITH 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.