SQL Server 2008 CDC 无响应桌子掉落后卡住
我在一个表上测试 SQL Server 2008 更改数据捕获功能时，它卡住了。
- 仅为 CDC 启用一张表。
- 在对表执行各种 DDL 更改以测试 CDC 如何响应后，我删除了该表。
- 在我之前的测试中，删除表也会导致 CDC 删除 CT 表以及与删除的表相关的任何元数据。
- CDC 似乎从未检测到最后一个删除语句，因为 CT 表仍然存在，并且 cdc.change_tables 表中存在该表的记录
- 我无法为该表禁用 CDC，也无法为新创建的表启用 CDC具有相同的名称，因为 cdc.change_table 记录存在并且链接到 SQL Server 元数据中不存在的对象 ID。
- 我尝试禁用数据库的 CDC，但该命令运行了 1 小时没有响应，然后我才将其停止。
- 尝试禁用数据库的 CDC 时，SQL Server 代理的作业列表在 Management Studio 中变得无响应（锁定请求超时）。取消禁用CDC命令后，Agent仍然没有响应。
- 我尝试重新启动代理服务。它成功停止，但在启动期间（似乎是最后阶段）挂起。该服务现在停留在“正在启动”状态。
我正在重新启动服务器以尝试让代理再次运行。
我可以删除并重新创建我的数据库，因为它仍在开发中并且其中没有任何数据，但是还有其他方法可以纠正该问题吗？到目前为止我读到的所有内容都表明我不应该直接破坏 CDC 的任何资源。我是否有任何真正的理由不应该删除 cdc.change_tables 记录和相关的 CT 表？
有没有人遇到过任何类似的 CDC 问题，这表明系统不稳定？
编辑：再次运行代理后，我尝试手动删除change_tables记录和CT表，但是当我尝试启用该表时，出现一系列与CDC元数据相关的错误。但是，我能够成功禁用整个数据库的 CDC，然后重新启用它。这当然会删除与 CDC 功能相关的所有资源。
I was testing out the SQL Server 2008 change data capture feature on a table when it became stuck.
- There is only one table enabled for CDC.
- After performing various DDL changes to the table to test how CDC responds, I dropped the table.
- Previously in my testing, dropping the table also causes CDC to drop the CT table, and any meta data related to the dropped table.
- The last drop statement appears to never have been detected by CDC, as the CT table still exists, and a record exists for it in the cdc.change_tables table
- I can't disable CDC for the table, or enable CDC for a newly created table with the same name, because the cdc.change_table record exists and is linked to a non-existent object ID in the SQL Server meta data.
- I tried disabling CDC for the database, but the command ran for 1 hour without response before I stopped it.
- While attempting to disable CDC for the database, the jobs listing for SQL Server Agent became unresponsive (lock request timeout) in management studio. After canceling the disable CDC command, Agent was still unresponsive.
- I tried restarting the Agent service. It stopped successfully but hung during (what appeared to be the final stages of) startup. The service is now stuck in "Starting" status.
I'm rebooting the server to attempt to get Agent running again.
I can drop and recreate my database since it's still under development and doesn't have any data in it, but is there any other way to correct the problem? Everything I've read so far indicates that I shouldn't mess directly with any of the CDC resources. Is there any real reason I shouldn't just delete the cdc.change_tables record and related CT table?
Has anyone experienced any similar problems with CDC that would indicate instability with the system?
EDIT: After getting Agent running again, I tried manually deleting the change_tables record and CT table, but when I tried to enable the table, there were a series of errors relating to CDC metadata. However, I was able to disable CDC for the entire database successfully, and then re-enable it. This of course dropped any and all resources related to the CDC feature.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
再次运行代理（服务器重新启动）后，我尝试手动删除change_tables记录和CT表，但是当我尝试启用该表时，出现一系列与CDC元数据相关的错误。但是，我能够成功禁用整个数据库的 CDC，然后重新启用它。这当然会删除与 CDC 功能相关的所有资源。
我将这种奇怪的行为归因于我运行测试的所有快速 DDL。由于这是一个测试环境，我有一些在生产环境中不可用的选项，至少在不备份捕获的数据的情况下。
我的结论是，是的，您确实不应该手动弄乱任何 CDC 资源。
After getting Agent running again (server reboot), I tried manually deleting the change_tables record and CT table, but when I tried to enable the table, there were a series of errors relating to CDC metadata. However, I was able to disable CDC for the entire database successfully, and then re-enable it. This of course dropped any and all resources related to the CDC feature.
I'm chalking up the weird behavior to the all of the rapid DDL I was running for testing. As this was a test environment, I had options that wouldn't be available in a production environment, at least without backing up the captured data.
I'm concluding that yes, you really shouldn't manually mess with any of the CDC resources.
我发现尝试在不属于 sa 的数据库中运行 EXEC sys.sp_cdc_disable_db 失败。
（我仍然通过
select db_name() as dbname , p.rows,* from sys.objects o 看到 cdc 表)将 sys.partitions p 加入 o.object_id=p.object_id
其中 o.schema_id=schema_id('cdc') 且 p.index_id < 2
Sys.sp_cdc_disable_db在简单地exec sp_changedbowner 'sa'后立即成功I found that trying to run
EXEC sys.sp_cdc_disable_dbfails in a database not owned by sa.(I still see the cdc tables by
select db_name() as dbname , p.rows,* from sys.objects o)join sys.partitions p on o.object_id=p.object_id
where o.schema_id=schema_id('cdc') and p.index_id < 2
Sys.sp_cdc_disable_dbimmediately succeeds after simplyexec sp_changedbowner 'sa'