Ändern des Autogrowth Parameters aller SQL Server Datenbanken

Gestern stand ich mal wieder vor einem SQL Server mit der Aufgaben, „… es gab Störungen in der Anwendung, kannst du mal auf dem SQL Server schauen ob es vielleicht an der Datenbank lag…“

Was macht man in solch einem Fall?
Natürlich schaut man sich erstmal alle Logs an, das Windows Eventlog, das SQL Server Log…
Man führt zahlreiche Statements aus, um vielleicht einen Hinweis auf Probleme und/oder Engpässe zu finden…

So auch bei mir… der Blick in den Eventviewer und das SQL Log ergaben keinerlei Auffälligkeiten bezüglich eines Ausfalles des SQL Servers, irgendeines Problem mit einem Datenbank Backup oder überhaupt ein „Mucken“. War ja wieder klar 😉

Also tiefer schauen, vielleicht lassen sich aus dem Cache oder über DMVs noch relevante Daten herauslesen. Wie bereits in einem früheren Beitrag geschrieben, nutze ich gerne die großartigen Skripte von Brent Ozar, wie auch jetzt.

Also mal schnell mit der Stored Procedure „sp_blitz“ über den SQL Server geschaut…

Das Ergebnis war „kilometerlang“, also erstmal die irrelevanten Punkte über die Parameter der Stored Procedure ausschalten, denn was interessiert mich in solch einem Fall ob es suboptimal ist zahlreiche Trigger in einer Datenbank zu haben oder nicht. Viel mehr geht es jetzt darum Anzeichen für Aussetzer oder Engpässe des SQL Servers und seiner Datenbanken zu ermitteln.

Aber auch hier konnte ich keine wirklichen Auffälligkeiten erkennen, nur das bei 174 von 300 SQL Datenbanken die Autogrowth Einstellung auf prozentuale anstatt auf fixen Werten standen.

100	Performance	File growth set to percent	AdventureWorks2008R2	http://brentozar.com/go/percentgrowth	The [AdventureWorks2008R2] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100	Performance	File growth set to percent	AdventureWorksDW2008R2	http://brentozar.com/go/percentgrowth	The [AdventureWorksDW2008R2] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100	Performance	File growth set to percent	master	http://brentozar.com/go/percentgrowth	The [master] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100	Performance	File growth set to percent	model	http://brentozar.com/go/percentgrowth	The [model] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100	Performance	File growth set to percent	msdb	http://brentozar.com/go/percentgrowth	The [msdb] database is using percent filegrowth settings. This can lead to out of control filegrowth.
100	Performance	File growth set to percent	tempdb	http://brentozar.com/go/percentgrowth	The [tempdb] database is using percent filegrowth settings. This can lead to out of control filegrowth.

beispielhaft

Da diese Einstellung schnell zu Problemen mit dem vorhandenen Plattenplatz führen können, empfehlen wir (wie zahlreiche andere Seiten/Dienstleister) diese Werte entsprechend anzupassen.
Aber wie, wenn man nicht 174 SQL Datenbanken händisch anpassen möchte?! Na klar, per TSQL.

Brainstorming zum SQL Statement

– ich brauche das ALTER Statement
– einen SELECT das alle betroffenen Datenbanken ermittelt
– einen Loop der durch das Ergebnis läuft
– Datenbank-Name, den logischen Dateiname für das ALTER-Statement,  FileGrowth Eigenschaften der Datenbanken

Und schon das Management Studio geöffnet und los gehts…

Wo bekomme ich die passenden Informationen her? Richtig aus der sys.master_files, also ein SELECT-Statement welches nur den Datenbank-Namen, den logischen Dateinamen und die Information ob prozentualer Autogrowth aktiviert ist liefert.

SELECT DB_NAME(files.database_id),
name,
physical_name
FROM sys.master_files files
WHERE files.type IN (0,
1)
AND files.growth != 0
AND is_percent_growth = 1
AND files.database_id > 4

Das Ergebnis dieser SQL Abfrage schreibe ich der Einfachheit halber in eine temporäre Tabelle, damit lässt es sich besser arbeiten.

Create Table ##temp ( DatabaseName sysname, logical_name sysname, physical_name nvarchar(500) )

Temporäre Tabelle und SELECT Statement werden entsprechend mittels INSERT … INTO befüllt miteinander kombiniert:

INSERT INTO ##temp (DatabaseName, logical_name, physical_name)
SELECT DB_NAME(files.database_id),
name,
physical_name
FROM sys.master_files files
WHERE files.type IN (0,1)
AND files.growth != 0
AND is_percent_growth = 1
AND files.database_id > 4

Nun kommt der Schleife, anhand derer ich jede Zeile in der temporären Tabelle ermittel und explizit für diese dann ein SQL Statement zusammensetze, welches ich mir erstmal nur ausgeben lasse.
Ich lasse mir üblicherweise erstmal alle Statements als Text ausgeben, dann kann man nochmal schnell einen Blick drüber werfen und notfalls einzelne Zeilen optimieren.

So wird dann ein ganzes Skript mit Deklarationen und Aufräumen daraus:

DECLARE @DatabaseName nvarchar(100)
DECLARE @Logical_Name nvarchar(100)
DECLARE @SQL nvarchar(4000)

-- Erstelle einen Temp-Table
Create Table ##temp (
DatabaseName sysname,
logical_name sysname,
physical_name nvarchar(500)
)

-- Ermitteln aller relevanten Datenbanken
Insert Into ##temp (DatabaseName, logical_name, physical_name)
select DB_NAME(files.database_id), name, physical_name from sys.master_files files where files.type in (0,1) and files.growth != 0 and is_percent_growth = 1 and files.database_id > 4

-- Durch das Ergebnis loopen
while exists (Select * From ##temp)
begin
Set NOCOUNT ON
select top 1 @DatabaseName = DatabaseName, @Logical_Name = logical_name from ##temp order by DatabaseName asc

SET @SQL = 'ALTER DATABASE [' + @DatabaseName + '] MODIFY FILE ( NAME = N''' + @Logical_Name + ''', FILEGROWTH = 512MB ) GO'
print @SQL

delete ##temp where DatabaseName = @DatabaseName

end

drop table ##temp

Jetzt nur noch das Ergebnis (siehe unten) in ein neues Query Fenster kopieren und ausführen.

ALTER DATABASE [AdventureWorks2008R2] MODIFY FILE ( NAME = N'AdventureWorks2008R2_Log', FILEGROWTH = 512MB ) GO
ALTER DATABASE [AdventureWorksDW2008R2] MODIFY FILE ( NAME = N'AdventureWorksDW2008R2', FILEGROWTH = 512MB ) GO
ALTER DATABASE [AM_Reporting_SQL] MODIFY FILE ( NAME = N'AM_Reporting_SQL_Log', FILEGROWTH = 512MB ) GO

Fertig ist das Skript zum Ändern des Autogrowth Parameters aller SQL Server Datenbanken.

Im Zuge dieser Optimierung bin ich noch über eine Ungereimtheit auf diesem SQL Server gestoßen dazu später mehr…