Polling

Capture SQL Server changes by reading log records with sys.fn_dblog over the ordinary connection, with nothing installed on the database host.

Polling asks SQL Server for its log records with sys.fn_dblog over the agent's ordinary SQL connection. Use it when you can't install a log broker on the database host and can't use Native CDC, and the login may be sysadmin or db_owner.

How it works

The agent connects to SQL Server like any client and reads the log records written since its last position with sys.fn_dblog, on a polling interval. Because the engine reads the log, Polling works on databases encrypted with TDE, and nothing runs on the database host.

How Polling affects the log

Polling holds no log, so your log backups and checkpoints truncate it as usual. When the agent's position is older than the log SQL Server still holds, for example after a pause, the agent reads the missing records from your log backups through SQL Server, which needs a sysadmin login. The start check and the pause warning tell you in advance when the login can't read log backups. When no log backup covers the gap, the pipeline stops and needs a reload.

Polling reads log backups written to disk, unencrypted and not striped. It reads compressed backups made with MS_XPRESS on SQL Server 2019 and later, or with ZSTD on SQL Server 2025. Other compressed backups, including every compressed backup from SQL Server 2016 SP2 and 2017, are refused.

Requirements

Setup

1. Put the database in FULL recovery and take a full backup. Replace <database_name> and <backup_path>.

   ALTER DATABASE [<database_name>] SET RECOVERY FULL;
   BACKUP DATABASE [<database_name>] TO DISK = N'<backup_path>';

2. Turn on row versioning. Either setting is enough. Setting READ_COMMITTED_SNAPSHOT waits until it is the only connection in the database.

   ALTER DATABASE [<database_name>] SET ALLOW_SNAPSHOT_ISOLATION ON;
   -- or: ALTER DATABASE [<database_name>] SET READ_COMMITTED_SNAPSHOT ON;

3. Confirm that the database has one log file.

   USE [<database_name>];
   SELECT name, physical_name FROM sys.database_files WHERE type = 1;

4. Create the capture login, add it to db_owner and grant the server permission. Replace <login_name> and <password>. On SQL Server 2019 and earlier, grant VIEW SERVER STATE instead.

   USE master;
   CREATE LOGIN [<login_name>] WITH PASSWORD = N'<password>';
   GRANT VIEW SERVER PERFORMANCE STATE TO [<login_name>];
   USE [<database_name>];
   CREATE USER [<login_name>] FOR LOGIN [<login_name>];
   ALTER ROLE db_owner ADD MEMBER [<login_name>];

   On SQL Server 2025, also let the login read sys.fn_dblog:

   USE master;
   CREATE USER [<login_name>] FOR LOGIN [<login_name>];
   GRANT SELECT ON OBJECT::sys.fn_dblog TO [<login_name>];

5. In the pipeline wizard, choose SQL Server on the Source step, select Polling under Capture mode, and enter the connection.

6. Select Test connection & list tables. The test refuses a database that isn't in FULL recovery. The full privilege check runs when the pipeline starts and names every missing permission or setting at once.

Encrypted data

• TDE. Supported. sys.fn_dblog reads the log through the engine, which decrypts it.

• Always Encrypted columns. Refused. A selected table with an Always Encrypted column is refused with:

  always_encrypted_column: SQL Server capture refuses selected column <schema>.<table>.<column> because it is Always Encrypted; decryption and ciphertext pass-through are not supported.

• Connections. The agent always encrypts its SQL connection. With Encryption at Require (default), it validates the server certificate; Prefer skips that check, for an on-premises server with a self-signed certificate.

Pros and cons

Pros

• Nothing to install on the database host, and no CDC on the database.
• Works on databases encrypted with TDE.
• Captures every change from the log, including every intermediate version of an updated row.
• Captures tables without a primary key when a usable unique index serves as the row key.

Cons

• Every read runs sys.fn_dblog on the server and costs it query work.
• Lag is seconds rather than milliseconds.
• The login needs sysadmin or db_owner, plus a SELECT grant on sys.fn_dblog on SQL Server 2025. Reading log backups after a gap needs sysadmin.
• ROW, PAGE and columnstore-compressed tables are refused.

Limitations

• Typed xml. A column of xml typed by an XML schema collection is refused. Untyped xml is supported. Adding a typed xml column to a tracked table stops the pipeline; see Stopped at a schema change.
• sql_variant. Captured, but SQL Server and Azure SQL targets refuse a sql_variant column, because Polling reads each value without its base type. To replicate it to those targets, capture the table with DirectLog or Reconstruction.
• CLR user-defined types. Captured as their bytes, which SQL Server, Azure SQL and PostgreSQL targets refuse.
• Sparse columns. Captured, also in a table with a column set. A table with sparse columns and a dropped column that no rebuild has removed yet is refused: on SQL Server 2025 only when the table is partitioned, on earlier versions always. Run ALTER TABLE <table> REBUILD, then start again.
• Compressed tables. A table whose heap or clustered index uses ROW, PAGE or columnstore compression is refused. A table rebuilt with compression after the start stops the capture on its first compressed row.
• Masked columns. A column with a dynamic data mask that the login can't UNMASK is refused.
• ANSI_PADDING OFF. A nullable char or binary column created with ANSI_PADDING OFF is refused.
• Keys. A table needs a primary key or a usable unique index (enabled, not filtered, every key and included column NOT NULL).
• TRUNCATE TABLE. A TRUNCATE TABLE of a whole tracked table is captured in order with the other changes. A partition TRUNCATE of a tracked table (TRUNCATE TABLE ... WITH (PARTITIONS (...))) is refused with sqlserver_polling_partition_truncate_refused, because it would empty the whole target table; reload the table. See Supported changes.
• Index rebuilds. A resumable rebuild, an online rebuild of a partitioned table, and a rebuild that changes the row layout (for example the first rebuild after a dropped column) stop the capture by name; reload the table. Other rebuilds don't stop capture.

Troubleshooting

Missing sysadmin or db_owner role

sysadmin server role or db_owner database role (required for fn_dblog)

Cause: the login is neither sysadmin nor a member of db_owner in the source database.

Fix: run ALTER ROLE db_owner ADD MEMBER [<user_name>]; in the source database.

Missing VIEW SERVER PERFORMANCE STATE

VIEW SERVER PERFORMANCE STATE for Polling (VIEW SERVER STATE before SQL Server 2022): ...

Cause: db_owner doesn't include this server permission, which Polling needs at the start.

Fix: in master, run GRANT VIEW SERVER PERFORMANCE STATE TO [<login_name>];, or GRANT VIEW SERVER STATE TO [<login_name>]; before SQL Server 2022.

Missing SELECT on sys.fn_dblog

SELECT on sys.fn_dblog for Polling: ...

Cause: SQL Server refused the login's read of sys.fn_dblog. On SQL Server 2025, db_owner alone isn't enough.

Fix: run the statements the message gives: in master, create a user for the login and run GRANT SELECT ON OBJECT::sys.fn_dblog TO [<login_name>];, as in Setup. Or add the login to sysadmin, or use DirectLog or Native CDC.

Tracked table uses compression

uncompressed rowstore for tracked table <table> under Polling (...)

Cause: the table uses ROW, PAGE or columnstore compression.

Fix: rebuild the table with DATA_COMPRESSION = NONE, or remove it from the pipeline.

Source busy: read window retries exhausted

SQL Server Polling source busy: <n> consecutive retries of the same read window failed over <m> minutes (bound: 30 minutes and 12 retries). ...

Cause: the server stayed too busy to return the log records for 30 minutes and 12 retries.

Fix: take log backups, reduce the load on the source, or use DirectLog. The pipeline resumes from its last committed position once the source can serve the read.

Log retention gap

SQL Server Polling retention gap: the reader needs every log record after <position>, but the retained transaction log now starts at <position> ...

Cause: a log backup or checkpoint truncated log records the pipeline hadn't read yet, and no log backup covers them.

Fix: reload the pipeline to run the initial load of its tables again. Keep the pipeline running, or keep your log backups available, so the agent can read the gap next time.

Login cannot read the log backup

The source principal cannot execute sys.fn_dump_dblog for the selected backup set.

Cause: the agent tried to read a log backup to close a gap, and SQL Server refused the login. The related message The source principal cannot inspect the selected backup's actual media header. has the same cause. The start check and the pause warning flag this in advance.

Fix: use a sysadmin login, or reload the pipeline.

Source busy: open DDL or TRUNCATE transaction

SQL Server Polling source busy: the Polling window probe after <lsn> waits on an open DDL or TRUNCATE transaction (1222). ...

Cause: an open transaction on the source holds a schema change or a TRUNCATE. Lag grows while it stays open, but the pipeline doesn't fail: capture continues when that transaction commits or rolls back.

Fix: commit or roll back the open transaction on the source.

Target refuses a sql_variant column

Table '<table>', column '<column>' (sql_variant): sql_variant remains deferred for this capture: only DirectLog and Reconstruction into a SQL Server or Azure SQL target carry each value's own base type exactly; Built-in CDC and Polling read it as a plain value, which would lose that type. To continue: ...

Cause: a selected table has a sql_variant column and the target is SQL Server or Azure SQL. The Prepare step shows this under the target schema check.

Fix: capture the table with DirectLog or Reconstruction, or deselect it on the Tables step.

Stopped at a schema change

SQL Server log-native schema history gap for '<table>': <reason>. No successor plan, row output, or checkpoint was published. A restart, or Pause → Start, reads this change from the log again and stops here, also after the change is undone at the source; Reload from source starts the pipeline over from a fresh copy.

Cause: a tracked table has a committed schema change that the capture can't carry exactly, for example an added column of typed xml or of a CLR user-defined type. Every restart reads the same change again, even after you undo it on the source.

Fix: reload the pipeline with Reload from source.

Database not in FULL recovery

recovery model=FULL (currently 'SIMPLE'; SIMPLE recovery truncates log records aggressively)

Cause: the database isn't in FULL recovery, so SQL Server can truncate log records before Polling reads them.

Fix: set FULL recovery and take a full database backup, as in Setup.

See also

• SQL Server capture modes
• DirectLog
• Lag, spool and log retention
• Performance considerations