door: Sergey Gigoyan | bijgewerkt: 2015-11-18 | Comments (9) | gerelateerd: meer > databasebeheer
probleem
SQL Server maakt het mogelijk om meer dan één transactielogbestand te gebruiken, maar de vraag rijst of dit noodzakelijk is en wat het voordeel is van meerdere transactielogbestanden. Er is een misvatting bij sommige ontwikkelaars dat het hebben van logbestanden voor meerdere transacties de prestaties kan verhogen omdat SQL Server ze parallel kan gebruiken. SQL Server gebruikt momenteel slechts één transactielogbestand en er is in dit geval geen parallellisme. Soms kan meer dan één logbestand worden gebruikt voor het oplossen van problemen. Dus, normaal gesproken is er geen noodzaak om meer dan één logbestand te hebben.Laten we een geval overwegen, wanneer onze database meer dan één logbestand heeft en we er slechts één moeten behouden.Deze tip is bedoeld om te beschrijven hoe onnodige logbestanden correct te verwijderen en slechts één te behouden.
oplossing
voordat we beginnen met illustreren hoe onnodige logbestanden te verwijderen, laten we kort beschrijven hoe SQL Server werkt met logbestanden: wanneer de database meer dan één logbestand heeft, blijft SQL Server naar het eerste schrijven totdat het vol is,schakelt het vervolgens over naar het tweede en ga zo maar door. Nadat het laatste logbestand volledig is geworden, keert SQL Server terug naar de eerste en gaat de cyclus verder.Echter, zoals we al zeiden, soms meer dan één logbestand kan nodig zijn. Bijvoorbeeld wanneer de schijf, waar het logbestand is gelokaliseerd, vol wordt en we het tweede logbestand op een andere locatie moeten maken,maar na het oplossen van het probleem, moeten we het tweede logbestand verwijderen, omdat er geen gebruik is om meer dan één logbestand te hebben.Laten we nu aannemen dat we een database hebben met twee logbestanden, en onze taak is om de tweede te verwijderen.Het volgende script maakt de testdb database met twee logbestanden en TestTable tabel. Om het zelf uit te voeren, moet u vervangen”D:\SQL Data” met een bestaand mappad.
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
laten we nu de fysieke structuur van het transactielogboek begrijpen: intern transactielogboekbestand bestaat uitvirtuele logbestanden (VLF), die de beheerseenheid zijn in het transactielogboekbestand. Het betekent dat wanneer database engine groeit of krimpt het logbestand, het doet dat met volledige VLFs (bijvoorbeeld het kan niet krimpen de helft van de VLF). De grootte van virtuele logbestanden en hun aantal in het fysieke logbestand is niet vast en wordt dynamisch beheerd door de database engine. Om het transactielogbestand intern te monitoren gebruiken we de opdracht” DBCC LOGINFO”, die informatie geeft over virtuele logbestanden. In het script hieronder hebben we dit commando gebruikt voor onze database:
--Getting log files infoDBCC LOGINFO('TestDB')
en het resultaat is het volgende:
FileID is log file id ‘ s voor onze database. Status geeft aan dat VLF herbruikbaar is of niet (Mogelijke waarden: 0 – ja, 2-Nee). Zoals we kunnen zien is er maar één VLF withStatus = 2. Nu wanneer we gegevens invoegen in de testtabel en controleren hoe logbestanden groeien:
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
met dit voorbeeld kunnen we zien dat beide logbestanden zijn gegroeid en nu zijn er VLFs met “Status = 2” in het tweede logbestand (FileID=3) ook:
nu moeten we TestDB_log2 verwijderen.ldf-bestand. Merk op dat we alleen de secundaire logbestanden kunnen verwijderen. Het verwijderen van het primaire logbestand is niet toegestaan door SQL Server.Elke database heeft slechts één primair logbestand en het eerste logbestand dat wordt aangemaakt inde database creation script wordt beschouwd als de primaire.Als we proberen het tweede logbestand te verwijderen:
USE masterGO--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
wij ontvangen het volgende bericht:
we kunnen het transactielogboek alleen verwijderen als het leeg is, daarom moeten we eerst legen it.To doe dat, we moeten een back-up maken van het transactielogboek. Aangezien onze “TestDB” database nieuw is aangemaakt en er geen volledige back-ups zijn, moeten we een volledige database back-up voor de TestDB database uitgeven, waarna we een transactielogboek back-up kunnen uitgeven:
--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'
de back-up van het transactielogboek snijdt het logbestand af (er zijn enkele uitzonderingen, die buiten het bereik van dit bestand vallen). Log truncation verwijdert inactieve virtuele logbestanden vanaf het begin van het logische log en geeft ruimte vrij in het logbestand. Afkappen verkleint echter niet de grootte van een fysiek logbestand. Het maakt alleen ruimte vrij, die hergebruikt kan worden. Laten we “DBCC LOGINFO” opnieuw uitvoeren:
zoals we kunnen zien zijn er geen virtuele logbestanden in de ” TestDB_log2.ldf ” bestand met Status = 2 en nu is ons logbestand leeg en klaar voor verwijdering:
--Remove TestDB_log2 fileALTER DATABASE TestDB REMOVE FILE TestDB_log2
de verwijdering is succesvol.
maar als we de informatie opnieuw controleren, zullen we zien dat het logische logbestand nog steeds bestaat:
--Checking log informationSELECT file_id, name, type_desc, physical_name, size, max_sizeFROM sys.database_files
als we nog een log back-up maken, wordt het bestand verwijderd:
--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
volgende stappen
- houd deze tip in gedachten om het gebruik van het transactielogboek te bepalen en hoe een overbodig logbestand te verwijderen.
- bekijk deze bronnen:
- databasebeheer / transactielogboek
- 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
Over de auteur
Sergey Gigoyan is een professionele database met meer dan 10 jaar ervaring, met een focus op database ontwerp, ontwikkeling, performance tuning, optimalisatie, hoge beschikbaarheid, BI-en DW design.Bekijk al mijn tips
artikel Laatst bijgewerkt: 2015-11-18