February 23, 2017 at 1:37 pm
I am able to see the results of seeding changes if I audit a table's insertion IDs by putting a trigger on the table to write events for me, however this just gives me evidence that seeding changed throughout the insertions and not who did the reseeding. Is there a way to track DBCC CHECKIDENT events on a table and what seeding was changed to? Ideally I'd want to know when the transaction occurred, the time, the resulting or requested seed, and the Program_name that did it (or if it was automatically done by the SQL server).
February 23, 2017 at 1:48 pm
DBCC CHECKIDENT events should show up in the default trace.
SELECT TOP 100
gt.[ServerName]
,gt.[DatabaseName]
,gt.[SPID]
,gt.[StartTime]
,gt.[ObjectName]
,c.[Name] [Category]
,gt.[EventClass] [EventID]
,e.[Name] [EventName]
,gt.[ApplicationName]
,gt.[HostName]
,gt.[LoginName]
,gt.[TextData]
FROM fn_trace_gettable((SELECT cast(value as varchar(8000)) FROM ::fn_trace_getinfo(0) where traceid = 1 and property = 2), DEFAULT) gt
LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.subclass_value = gt.[objecttype]
INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id]
INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id]
WHERE gt.[TextData] LIKE '%DBCC CHECKIDENT%'
ORDER BY gt.[starttime] DESC;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply