CODEWITHSUNDEEP
sql-server
The One
The One

Reputation: 2331

How to trace down more information about SQL Server session ID in the past?

I got into a problem when one of my DB was in "restoring" state. After checking error logs, i found out that someone had done something.

- Starting up database "mydb"

- The database "mydb" is makred RESTORING and is in a state that does not allow recovery to be fun

- Starting up database "mydb"

- RESTORE DATABASE sucessfully processed 192392 pages in 178.seconds

All of this messages belong to spid128 source.

But i couldn't trace down who did this.

I can check all of the current session ID but that's not what i want. I'm looking for a way to, let's say check information about that spid yesterday.

Is that possible?

Upvotes: 0

Views: 732

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46233

The default trace captures backup and restore events so it will have details of the restore. However, since it's a rollover trace with a max of 5 files of 20MB each, older historical data might not be available depending on server activity.

Below is an example query to get backup/restore events from default trace files for the problem database:

SELECT 
     te.name
    ,tt.TextData
    ,tt.StartTime
    ,tt.HostName
    ,tt.LoginName
    ,tt.ApplicationName
FROM sys.traces AS t
CROSS APPLY fn_trace_gettable(
    REVERSE(N'crt.gol' + SUBSTRING(REVERSE(t.path), CHARINDEX(N'\', REVERSE(t.path)), 128)), default) AS tt
JOIN sys.trace_events AS te ON
    te.trace_event_id = tt.EventClass
JOIN sys.trace_subclass_values AS tesv ON
    tesv.trace_event_id = tt.EventClass
    AND tesv.subclass_value = tt.EventSubClass
WHERE
    t.is_default = 1 --default trace
    AND te.name = N'Audit Backup/Restore Event'
    AND DatabaseName = N'mydb';

Upvotes: 1

Related Questions