Why I'm receiving dm_db_partition_stats is not supported on DMS task with Sql Server as source?

Question

Hi
I run a DMS task with a SqlServer instanca (not in AWS) as a source. Fails with this message on Cloudwatch:
dm_db_partition_stats is not supported

I've followed the doc instructions to grant some privileges to the sqlServer user, without problems.
I'm not finding that message in any doc or forum..
Thanks

Pandurangaswamy · Accepted Answer

Here are a few possible reasons and solutions to investigate:

**1. SQL Server Version Compatibility**

**Issue:** Certain system views and functions, such as dm_db_partition_stats, may not be supported in older versions of SQL Server or specific editions (like SQL Express).

**Solution:** Ensure that your SQL Server instance is running a version compatible with AWS DMS. AWS DMS supports SQL Server versions from 2008 R2 to the latest version, but not all features are available in every version.

You can run the following query to check your SQL Server version:

```
SELECT @@VERSION;

```
**2.Permission Issue**

**Issue:** Even though you granted privileges to the SQL Server user as per the AWS DMS documentation, certain system views (like dm_db_partition_stats) require more elevated permissions, like the VIEW SERVER STATE privilege.

**Solution:** Ensure that the DMS user has the necessary permissions:

Grant the VIEW SERVER STATE permission to the user DMS is using:

```
GRANT VIEW SERVER STATE TO [dms_user];

```
**3. Schema or Partitioning Configuration**

**Issue:** The view dm_db_partition_stats might be used by DMS to gather partitioning information from tables. If your source database uses partitioned tables or specific partitioning schemes, this could be triggering the error.

**Solution:** If you have partitioned tables in your source SQL Server database, verify that DMS supports replicating partitioned tables from your SQL Server version.

**4. AWS DMS Task Configuration**

**Issue:** The DMS task might be configured to perform operations that involve querying partition statistics, which isn't fully supported for your SQL Server version or instance.

**Solution:** Review your DMS task settings. Consider changing the migration mode (full load, CDC, or full load + CDC) to see if the issue is related to a specific task configuration.

**5. DMS or SQL Server Bug**

**Issue:** There might be a bug or compatibility issue between AWS DMS and your SQL Server version.

**Solution:** Ensure that you are using the latest version of AWS DMS and the SQL Server replication agent. Check for any updates or patches for SQL Server that might resolve the issue.

**Next Steps:**

**AWS DMS Logs:** Review the detailed task logs in AWS DMS to understand which query or operation is failing and whether it is directly related to dm_db_partition_stats.

**Test with Simple Table:** Try running a migration task on a simple table that does not use partitions to rule out partition-related issues.

re:Post Agent · Answer

The error message "dm_db_partition_stats is not supported" that you're encountering with your AWS Database Migration Service (DMS) task using SQL Server as a source is not a common issue documented in AWS resources. However, this error likely relates to permissions or limitations when using SQL Server as a source for AWS DMS.

To address this issue, consider the following points:

1. Permissions: Ensure that the SQL Server user account used for the DMS task has the necessary permissions. Specifically, the user should have the "VIEW SERVER STATE" permission. This server-level permission allows access to dynamic management views (DMVs) and functions, which are crucial for DMS to access Change Data Capture (CDC) resources.

2. SQL Server Configuration: Verify that your SQL Server instance is properly configured for use with AWS DMS. This includes ensuring that CDC is enabled on the source database and the tables that are in scope for the DMS task.

3. DMS Task Configuration: Review your DMS task settings to ensure they are compatible with your SQL Server version and configuration. Some features or settings might not be supported depending on your specific SQL Server setup.

4. SQL Server Version: Check if your SQL Server version is fully supported by AWS DMS. Some limitations may apply to older versions of SQL Server.

5. Table Partitioning: The error message mentions partition stats, which could be related to partitioned tables. AWS DMS has some limitations when it comes to partitioned tables, such as not supporting different data compression settings for each partition.

If the issue persists after checking these points, you may need to contact AWS Support for further assistance, as this specific error message is not commonly documented. They might be able to provide more insight into why dm_db_partition_stats is not supported in your particular case and offer a workaround or solution.  
**Sources**  
[Using a Microsoft SQL Server database as a source for AWS DMS - AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html)  
[Sql Server assessments - AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.AssessmentReport.SqlServer.html)

Fernando · Answer

Hello
Thanks for the info. I've review the permission and now the message is very different:
 [METADATA_MANAGE ]E:  RetCode: SQL_ERROR  SqlState: 42000 NativeError: 1222 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Lock request time out period exceeded. [1022502]  (ar_odbc_conn.c:604)

I'm just trying to read a single table. No locks or update on the sql server side.

Why I'm receiving dm_db_partition_stats is not supported on DMS task with Sql Server as source?

Relevant content