CODEWITHSUNDEEP
sql-serversql-server-2008change-data-capture
Timofey
Timofey

Reputation: 2518

How to know when a table was removed from Change Data Capture(CDC) or added to it?

SQL Server 2008 has Change Data Capture feature that allows to capture changes made in the table, such as insert, delete or update rows.

I have noticed that a table was excluded from Change Data Capture (CDC) which brought lots of problems.

Is there a way to find out when a table was removed from CDC or even who removed the table?

Is there some kind of log of CDC where such information is available?

Upvotes: 1

Views: 1024

Answers (2)

rrozema
rrozema

Reputation: 342

CDC keeps track of DDL changes made on the monitored tables: http://msdn.microsoft.com/en-us/library/bb522553(v=sql.110).aspx and http://msdn.microsoft.com/en-us/library/bb510681(v=sql.110).aspx

Also select modify_date from sys.tables where object_id = object_id('dbo.yourtablename') will give you the time and date the table's DDL was last changed. Unless you've got monitoring installed on all DDL changes there's -to my knowledge- no way to get all changes made to your table's definitions.

Upvotes: 0

SqlACID
SqlACID

Reputation: 4014

You could check the Reports / Standard Reports / Schema changes history, I would think it would get picked up there since the capture tables probably would be dropped, but that data comes from the default trace, so it may not go back far enough for you.

Upvotes: 1

Related Questions