SQL Server Log-Based Troubleshooting
  • 1 Minute to read
  • Dark
    Light
  • PDF

SQL Server Log-Based Troubleshooting

  • Dark
    Light
  • PDF

No Data Had Been Captured Regarding Some Tables in the CDC Tables

In this case, there are some reasons to make sure the CDC works correctly in your SQL Server. 

Check if your table(s) enabled for CDC

In SQL Server, the CDC mechanism creates a job, and a Change Tracking (CT) table for each table separately. 

That means, there's a need to enable any table you want to be capture by the CDC jobs. 

Therefore, please refer to our docs on enabling databases and tables in order to make sure the table had been enabled to CDC.

In order to check if the table is enabled for CDC and track changes, please use the next command:

-- The command should return results.
EXEC sys.sp_cdc_help_change_data_capture
GO

And search the table schema and table name under the source_schema and source_name fields.

Check if your database has CDC jobs enabled and SQL Server Agent is running

First check that SQL server agent is running by selecting it with right-click:


If the database is enabled in CDC and the tables are enabled on CDC and you still can't see new logs coming in, check that the SQL agent for CDC is enabled for capture logs and clean logs. You can do so by downloading Microsoft SQL Server Management Studio, connecting to your data SQL server and underneath:

{HOSTNAME} > SQL Server Agent > Jobs > cdc.{DATABASE}_capture

{HOSTNAME} > SQL Server Agent > Jobs > cdc.{DATABASE}_cleanup

where {HOSTNAME} is your connection's hostname (or the name of the connection you chose), and {DATABASE} is the name of the database you set up for cdc.

you should see enabled jobs. 


Start the SQL Server Agent on Azure SQL Server Virtual Machine  

In case your SQL Server is running on Azure SQL Server Virtual Machine, there are some cases the SQL Server Agent is needed to be started from the instance itself.
In order to make it, please follow the next steps:

  1. Go to Azure portal, and search for SQL Server Virtual Machine.
  2. Click on the right sql server name you want to start the agent on.
  3. On the SQL Server machine, under the overview, click on the virtual machine name:
  4. In the virtual machine that opened, click on Run Command -> RunPowerShellScript:
  5. Run the next script and wait for its end:
> Start-Service SQLSERVERAGENT



Was this article helpful?