• Thomas Pollinger
• 10.07.2017
• DE

Gewusst wie! "Das richtige Recovery Model für den SQL Server 2014 auswählen"

"Nobody is perfect" dachte ich mir, als ich bemerkt hatte, dass einer unser SQL Server 2014 ein 3,1 TB großes Transaktionslogfile auf der Platte hatte. Der Grund dafür war direkt klar, nur es war ärgerlich das es vorher nicht aufgefallen ist.
 

Ursache

Wenn man einen Microsoft SQL Server 2014 mit Standardwerten installiert, wird automatisch das s.g. "Recovery Model" auf "Full" gesetzt. Das hat zur Folge, dass diese Logfiles sehr schnell, extrem groß werden. Wie in unserem Fall, da extrem viele Transaktionen für mehrere Wochen liefen, wuchs es auf 3,1 TB an.
 

Lösung

Als Lösung dafür wird im Netz immer wieder mitgeteilt, dass man das Recovery Model auf "Simple" stellen soll. Doch wenn man schon unzählige DB-Schema angelegt hat und auch verhindern möchte das dies bei neuen wieder auftritt, muss man etwas mehr machen.

Im SQL Server 2014 (auch in früheren Version) wird beim anlegen eines neuen DB-Schemas, die Einstellungen des "model" Schema übernommen. Also sollte man auf jeden Fall zuerst die Einstellungen an diesem Schema anpassen. 

Ebenso wird auch immer wieder empfohlen, direkt das s.g. "Auto Shrink" auch, sofern aktiv, abzuschalten.

Damit man dies nun nicht bei jedem DB-Schema einzeln machen muss, kann man das auch via SQL-Skript tun:

-- This script will set recovery model to Simple on all databases
use [master]
go
 
-- Declare variable for each database name
declare @databaseName nvarchar(128)
 
-- Define the cursor
declare databaseCursor cursor
 
-- Define the cursor dataset
for
select [name] from sys.databases
 
-- Start loop
open databaseCursor
 
-- Get information from the first row
fetch next from databaseCursor into @databaseName
 
-- Loop until there are no more rows
while @@fetch_status = 0
begin
 print 'Setting recovery model to Simple for database [' + @databaseName + ']'
 exec('alter database [' + @databaseName + '] set recovery Simple')
 exec('alter database [' + @databaseName + '] set auto_shrink off')
 
-- Get information from next row
 fetch next from databaseCursor into @databaseName
end
 
-- End loop and clean up
close databaseCursor
deallocate databaseCursor
go

Quelle: https://aspnetfaq.com/set-all-databases-to-simple/

Damit wird dann direkt "model" und alle anderen vorhanden Schemata angepasst. Meinerseits habe ich hier auch direkt noch das "auto_shrink off" aufgenommen, damit wird alles aufeinmal durchgeführt.

Hinweis: Es wird nicht explizit die tempdb und master ausgeschlossen. Jedoch können nicht alle Änderungen an diesen Schemata vorgenommen werden. Daher wird es zu einer Fehler-/Hinweis-Meldung im SQL Studio kommen. Diese ist aber nicht schlimm und kann ignoriert werden.

Nachdem die Einstellungen vorgenommen wurden, sollte man nochmals ein "FullBackup" durchführen und dann einen Wartungsaufträge vom Typ "Shrink" durchführen. Damit werden die Transaktionslogs wieder kleiner und wachsen nicht mehr so schnell an.

Ebenso epfiehlt es sich, diese Wartungsaufträge auch regelmäßig durch zu führen, damit z.B. einmal pro Woche ein Shrink auf alle DB-Schemata vorgenommen wird. Dazu bietet der SQL Server 2014 via SQL Studio einen kleinen einfachen Wizard an.

Fazit

Für einen optimimalen Betrieb des Web Site Managemennt (RedDot CMS) Server, sollte nicht nur der Applikationsserver optimal eingestellt werden. Auch ist die Datenbank und die entsprechenden Schemata eine wichtige Komponente und benötigt ebenso eine optimale Konfiguration. Happy optimize your database ;)

Über den Autor: