4

A colleague of mine noticed incorrect ids generated for inserted items. Most probable cause: some script issues a command like the following:

dbcc checkident('dbo.table', reseed, 1)

I have thought about setting up a DDL trigger to catch this commands, but this list of DDL events does not seem to include anything related to reseeding.

Question: is there a way to catch such commands in a similar way DDL triggers catch various database schema changes?

2

Sure, these are captured by the default trace (and maybe by extended events system_health session, not sure). Given the caveats in this answer (for example a command issued last year is unlikely to still exist in the trace):

You can see recent DBCC events here:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), 
       CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1;

SELECT TextData, ApplicationName, LoginName, StartTime, DatabaseName
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 116
  --AND DatabaseName = DB_NAME() / or constant
  ORDER BY StartTime DESC;

Note, this will show more than just CHECKIDENT events, but you can filter that further. On the other hand, it might be useful to spot check what other DBCC commands are (or are not) being run, though.

  • Since I am interested in what happened very recently, this is very good for me. Thanks. – Alexei Feb 22 '17 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.