SQL Server – defaulttrace

Vem tog bort tabellen? När fick den användaren rättigheter? Många vet inte om det men SQL Server kan berätta en hel del för dig. Sedan SQL Server 2005 finns det en defaulttrace uppsatt på alla instanser som innehåller en hel del matnyttig information vid felsökning.

Normalt är defaulttracen aktiverad och startas automatiskt vid uppstart av instansen. Den tar väldigt lite resurser att ha aktiverad och består normalt sätt av fem rullande tracefiler med basnamnet logNN.trc (där NN är ett löpnummer) som lagras under MSSQLLOG under installationssökvägen för instansen och har oftast traceid 1. Både sökväg och antal filer går att ställa in och det är oftast rekommenderat att utöka antalet logfiler beroende på aktivitet på servern och vilka krav som finns på uppföljningstid.

Aktivering och uppsättning
Kör nedan fråga för att kontrollera om funktionen är aktiv på din instans:

SELECT value_in_use FROM master.sys.configurations WHERE configuration_id = 1568
Om den inte är aktiv och du vill starta den kan du köra detta skript:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO

För att se mer information om den, t.ex. sökvägen till tracefilerna, trace id etc. finns funktionen fn_trace_getinfo som, om den körs med default värde, visar information om alla aktiva tracer på instansen:

 SELECT * FROM ::fn_trace_getinfo(DEFAULT)

Tracefilerna kan öppnas som vanliga .trc filer i SQL Server Profiler eller läsas via T-SQL med funktionen fn_trace_gettable.

Events
Via fn_trace_geteventinfo eller SQL Server Profiler tracedefinition kan du se alla event som spåras på din instans. Det vi oftast använder den till är att hämta information om auto growth för databasfiler, förändring av databasobjekt och säkerhetshändelser.
Auto growth representeras av fyra olika event som uppkommer vid utökning och förminskning av data- respektive logfilerna för alla databaser.

92 Data File Auto Grow
93 Log File Auto Grow
94 Data File Auto Shrink
95 Log File Auto Shrink

Säkerhetsrelaterade events använder vi oftast för att del undersöka säkerhetsproblem men även för att spåra rättighetsförändringar etc. Defaulttracen innehåller bl.a.:

20 Audit Login Failed
102 Audit Database Scope GDR event (Grant, Deny, Revoke)
104 Audit Add login event
105 Audit Login GDR event (Grant, Deny, Revoke)
108 Audit Add login to server role event
109 Audit Add DB user event
110 Audit Add Member to DB role event

De event som finns för förändringar av objekt används för att spåra förändringar i databaser, t.ex. vem/vad tog bort tabellen, etc.

46 Object:Created
47 Object:Deleted
164 Object:Altered

För dessa events är det bara att känna till kolumnen ObjectType som berättar vilken typ av objekt det handlar om. T ex:

8272 Lagrad procedur
8277 Tabell
16964 Databas

Exempel
Nedan SQL sats hämtar info från den aktiva default tracefilen, i detta fall om misslyckade inloggningar. För att bestämma vilka event som ska visas byts bara EventClass ut mot t ex något av de event jag angett ovan. Du kan även ange ett filnamn direkt eller ange hur många filer som funktionen ska hämta data från. Det finns även många fler kolumner i tracefilen som kan behövas beroende på vilka event du är intresserad av. Titta gärna mer på fn_trace_gettable i SQL Servers dokumentation.

 SELECT e.name AS [EventNamn]
,t.DatabaseName AS [Databas]
,t.ApplicationName AS [Klientapplikation]
,t.LoginName AS [LoginNamn]
,t.StartTime AS [StartTid]
,t.EndTime AS [SlutTid]
,t.ObjectName AS [Objektnamn]
,t.ObjectType AS [ObjectTyp]
FROM [sys].[fn_trace_gettable](CONVERT(VARCHAR(150), (
SELECT TOP 1 tf.[value]
FROM [sys].[fn_trace_getinfo](NULL) AS tf
WHERE tf.property = 2
)), 1) AS t
INNER JOIN [sys].[trace_events] e ON t.EventClass = e.trace_event_id
WHERE t.EventClass IN (20)
ORDER BY t.StartTime ;

En liten brasklapp så här på slutet, Microsoft har markerat att defaulttracen kommer att ersättas av en extended events i framtida versioner av SQL Server.
/Björn