Customer Engagement & Dynamics CRM Forum

Hello Everyone,

Presently, database capacity is over limit for a client's tenant and it appears that the main culprit is that someone created Test from a full copy of Production. There are a large number of emails that come in to queues for this client and we have identified the ActivityPointerBase as a table of concern. I am looking to clear historical emails out of Test to free up space.

In doing so I am running the Bulk Record Deletion. I also ran SQL for CDS to understand how many records should be getting removed and get an idea of how long it might take. Unfortunately, something appear to be odd and I am hoping that someone in the community might be able to shed some light on this.

My deletion query is for email messages created on or before 8/31/2019. On Monday ~600,000 had been deleted and SQL for CDS was showing ~13,000 emails remaining.


What would be the reason for this to have such different numbers?

Thanks
Scott

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

Scott,

I haven't used the SQL 4 CDS utility and I can't think of a reason for the large difference in row counts. If you can enable the TDS endpoint for the org, then you could try connecting to the CDS using SQL Server Management Studio as outlined here: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/cds-sql-query

I'd be curious to see if the following query in SSMS yields the same row count as the SQL 4 CDS utility.

select count(*) from activitypointer where activitytypecode=4202 and createdon <= '8/31/2019'

------------------------------
Andy Arndt
Minitab, Inc.
State College PA
------------------------------

Original Message:
Sent: Aug 05, 2020 10:06 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Hello Everyone,

Presently, database capacity is over limit for a client's tenant and it appears that the main culprit is that someone created Test from a full copy of Production. There are a large number of emails that come in to queues for this client and we have identified the ActivityPointerBase as a table of concern. I am looking to clear historical emails out of Test to free up space.

In doing so I am running the Bulk Record Deletion. I also ran SQL for CDS to understand how many records should be getting removed and get an idea of how long it might take. Unfortunately, something appear to be odd and I am hoping that someone in the community might be able to shed some light on this.

My deletion query is for email messages created on or before 8/31/2019. On Monday ~600,000 had been deleted and SQL for CDS was showing ~13,000 emails remaining.

Today I checked and the deletion job is still running and is showing ~6 million emails deleted but SQL for CDS show there are still about ~11,000 to go.

What would be the reason for this to have such different numbers?

Thanks
Scott

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

@Mark Carrington (author of SQL 4 CDS) might have some input here? ​

------------------------------
Jonas Rapp
MVP
Sweden
------------------------------

Original Message:
Sent: Aug 05, 2020 10:06 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Hello Everyone,

Presently, database capacity is over limit for a client's tenant and it appears that the main culprit is that someone created Test from a full copy of Production. There are a large number of emails that come in to queues for this client and we have identified the ActivityPointerBase as a table of concern. I am looking to clear historical emails out of Test to free up space.

In doing so I am running the Bulk Record Deletion. I also ran SQL for CDS to understand how many records should be getting removed and get an idea of how long it might take. Unfortunately, something appear to be odd and I am hoping that someone in the community might be able to shed some light on this.

My deletion query is for email messages created on or before 8/31/2019. On Monday ~600,000 had been deleted and SQL for CDS was showing ~13,000 emails remaining.

Today I checked and the deletion job is still running and is showing ~6 million emails deleted but SQL for CDS show there are still about ~11,000 to go.

What would be the reason for this to have such different numbers?

Thanks
Scott

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

@Mark Carrington here posting under another account as the forums seem to be broken for me at the moment.

@Scott Florance, apologies for the delay, if the forums were working better I'd have responded to this a week ago. Presumably by now your job has finished so there's not much we'll actually be able to see. My best guess on the reason for the low numbers coming from your SQL 4 CDS queries though are permissions - do you have rights to see all the emails?

I'd also have been interested to see some different ways of getting the results - SELECT COUNT(1) isn't something I've optimised for so far, though this will be in the next release. SELECT COUNT(*) will use some more efficient ways of running the query, and while the results should be the same it's possible there's a bug in there somewhere.

------------------------------
Matt Beard
Senior Software Engineer
Data8
------------------------------

Original Message:
Sent: Aug 13, 2020 05:35 AM
From: Jonas Rapp
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington (author of SQL 4 CDS) might have some input here? ​

------------------------------
Jonas Rapp
MVP
Sweden

Original Message:
Sent: Aug 05, 2020 10:06 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Hello Everyone,

Presently, database capacity is over limit for a client's tenant and it appears that the main culprit is that someone created Test from a full copy of Production. There are a large number of emails that come in to queues for this client and we have identified the ActivityPointerBase as a table of concern. I am looking to clear historical emails out of Test to free up space.

In doing so I am running the Bulk Record Deletion. I also ran SQL for CDS to understand how many records should be getting removed and get an idea of how long it might take. Unfortunately, something appear to be odd and I am hoping that someone in the community might be able to shed some light on this.

My deletion query is for email messages created on or before 8/31/2019. On Monday ~600,000 had been deleted and SQL for CDS was showing ~13,000 emails remaining.

Today I checked and the deletion job is still running and is showing ~6 million emails deleted but SQL for CDS show there are still about ~11,000 to go.

What would be the reason for this to have such different numbers?

Thanks
Scott

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

Thank you everyone for reaching out and the feedback.

Andy, Mark can confirm but SQL4CDS should do the same as accessing via T-SQL in SSMS. Within SQL4CDS you can enable the T-SQL endpoint to use that if desired.

Mark,
My user is a system admin in the environment so it shouldn't be permission related. Also, I found that using SELECT COUNT(1) and SELECT COUNT(*) returned the same results so I used COUNT(1) on my subsequent queries.

The the job has completed but there are still some interesting outcomes. Bulk Deletion shows there were 12,685,775 deleted, way more than the 13-11k returned in the SQL4CDS query. I would see thousands added to the deletion total before seeing it drop by 1 or 2 in the SQL query.
Additionally, if I query in SQL4CDS now I show 5,323 records still meeting my criteria. If I run a bulk delete with that criteria it shows the 5,323 as deleted but re-querying after the deletion once again shows the 5,323. They are reported as deleted but never are.

When building the Bulk Deletion job I select the entity "Email Messages", not sure if that plays into a difference with the "email" table queried in SQL??

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

Original Message:
Sent: Aug 14, 2020 11:31 AM
From: Matt Beard
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington here posting under another account as the forums seem to be broken for me at the moment.

@Scott Florance, apologies for the delay, if the forums were working better I'd have responded to this a week ago. Presumably by now your job has finished so there's not much we'll actually be able to see. My best guess on the reason for the low numbers coming from your SQL 4 CDS queries though are permissions - do you have rights to see all the emails?

I'd also have been interested to see some different ways of getting the results - SELECT COUNT(1) isn't something I've optimised for so far, though this will be in the next release. SELECT COUNT(*) will use some more efficient ways of running the query, and while the results should be the same it's possible there's a bug in there somewhere.

------------------------------
Matt Beard
Senior Software Engineer
Data8

Original Message:
Sent: Aug 13, 2020 05:35 AM
From: Jonas Rapp
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington (author of SQL 4 CDS) might have some input here? ​

------------------------------
Jonas Rapp
MVP
Sweden

Original Message:
Sent: Aug 05, 2020 10:06 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Hello Everyone,

Presently, database capacity is over limit for a client's tenant and it appears that the main culprit is that someone created Test from a full copy of Production. There are a large number of emails that come in to queues for this client and we have identified the ActivityPointerBase as a table of concern. I am looking to clear historical emails out of Test to free up space.

In doing so I am running the Bulk Record Deletion. I also ran SQL for CDS to understand how many records should be getting removed and get an idea of how long it might take. Unfortunately, something appear to be odd and I am hoping that someone in the community might be able to shed some light on this.

My deletion query is for email messages created on or before 8/31/2019. On Monday ~600,000 had been deleted and SQL for CDS was showing ~13,000 emails remaining.

Today I checked and the deletion job is still running and is showing ~6 million emails deleted but SQL for CDS show there are still about ~11,000 to go.

What would be the reason for this to have such different numbers?

Thanks
Scott

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

Woo, the forums seem to be working for me again today!

Do you have a feel for whether the original number of emails was closer to the numbers reported by SQL 4 CDS or bulk delete?

With the 5k emails still reported by SQL 4 CDS that bulk delete doesn't delete, can you see them in Advanced Find? Is there anything obviously in common about them that might explain why bulk delete isn't working?

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester
------------------------------

Original Message:
Sent: Aug 14, 2020 11:54 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Thank you everyone for reaching out and the feedback.

Andy, Mark can confirm but SQL4CDS should do the same as accessing via T-SQL in SSMS. Within SQL4CDS you can enable the T-SQL endpoint to use that if desired.

Mark,
My user is a system admin in the environment so it shouldn't be permission related. Also, I found that using SELECT COUNT(1) and SELECT COUNT(*) returned the same results so I used COUNT(1) on my subsequent queries.

The the job has completed but there are still some interesting outcomes. Bulk Deletion shows there were 12,685,775 deleted, way more than the 13-11k returned in the SQL4CDS query. I would see thousands added to the deletion total before seeing it drop by 1 or 2 in the SQL query.
Additionally, if I query in SQL4CDS now I show 5,323 records still meeting my criteria. If I run a bulk delete with that criteria it shows the 5,323 as deleted but re-querying after the deletion once again shows the 5,323. They are reported as deleted but never are.

When building the Bulk Deletion job I select the entity "Email Messages", not sure if that plays into a difference with the "email" table queried in SQL??

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD

Original Message:
Sent: Aug 14, 2020 11:31 AM
From: Matt Beard
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington here posting under another account as the forums seem to be broken for me at the moment.

@Scott Florance, apologies for the delay, if the forums were working better I'd have responded to this a week ago. Presumably by now your job has finished so there's not much we'll actually be able to see. My best guess on the reason for the low numbers coming from your SQL 4 CDS queries though are permissions - do you have rights to see all the emails?

I'd also have been interested to see some different ways of getting the results - SELECT COUNT(1) isn't something I've optimised for so far, though this will be in the next release. SELECT COUNT(*) will use some more efficient ways of running the query, and while the results should be the same it's possible there's a bug in there somewhere.

------------------------------
Matt Beard
Senior Software Engineer
Data8

Original Message:
Sent: Aug 13, 2020 05:35 AM
From: Jonas Rapp
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington (author of SQL 4 CDS) might have some input here? ​

------------------------------
Jonas Rapp
MVP
Sweden

Original Message:
Sent: Aug 05, 2020 10:06 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Hello Everyone,

Presently, database capacity is over limit for a client's tenant and it appears that the main culprit is that someone created Test from a full copy of Production. There are a large number of emails that come in to queues for this client and we have identified the ActivityPointerBase as a table of concern. I am looking to clear historical emails out of Test to free up space.

In doing so I am running the Bulk Record Deletion. I also ran SQL for CDS to understand how many records should be getting removed and get an idea of how long it might take. Unfortunately, something appear to be odd and I am hoping that someone in the community might be able to shed some light on this.

My deletion query is for email messages created on or before 8/31/2019. On Monday ~600,000 had been deleted and SQL for CDS was showing ~13,000 emails remaining.

Today I checked and the deletion job is still running and is showing ~6 million emails deleted but SQL for CDS show there are still about ~11,000 to go.

What would be the reason for this to have such different numbers?

Thanks
Scott

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

Glad you can be back to your usual self!

I am leaning towards the bulk delete as the activitypointerbase has been ridiculously high when viewing the capacity, but it is odd as now with the 5k emails they are both reflecting the same number.

As for the 5k emails not deleting, I think I have an idea as to what the issue is and will probably need MS support. I can see the emails in Advanced Find and after digging it appears the common tie is that they all have a ghost attachment. If I attempt to delete the attachment or the email it throws an error saying the attachment does not exist.


------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

Original Message:
Sent: Aug 17, 2020 02:32 AM
From: Mark Carrington
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Woo, the forums seem to be working for me again today!

Do you have a feel for whether the original number of emails was closer to the numbers reported by SQL 4 CDS or bulk delete?

With the 5k emails still reported by SQL 4 CDS that bulk delete doesn't delete, can you see them in Advanced Find? Is there anything obviously in common about them that might explain why bulk delete isn't working?

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Aug 14, 2020 11:54 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Thank you everyone for reaching out and the feedback.

Andy, Mark can confirm but SQL4CDS should do the same as accessing via T-SQL in SSMS. Within SQL4CDS you can enable the T-SQL endpoint to use that if desired.

Mark,
My user is a system admin in the environment so it shouldn't be permission related. Also, I found that using SELECT COUNT(1) and SELECT COUNT(*) returned the same results so I used COUNT(1) on my subsequent queries.

The the job has completed but there are still some interesting outcomes. Bulk Deletion shows there were 12,685,775 deleted, way more than the 13-11k returned in the SQL4CDS query. I would see thousands added to the deletion total before seeing it drop by 1 or 2 in the SQL query.
Additionally, if I query in SQL4CDS now I show 5,323 records still meeting my criteria. If I run a bulk delete with that criteria it shows the 5,323 as deleted but re-querying after the deletion once again shows the 5,323. They are reported as deleted but never are.

When building the Bulk Deletion job I select the entity "Email Messages", not sure if that plays into a difference with the "email" table queried in SQL??

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD

Original Message:
Sent: Aug 14, 2020 11:31 AM
From: Matt Beard
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington here posting under another account as the forums seem to be broken for me at the moment.

@Scott Florance, apologies for the delay, if the forums were working better I'd have responded to this a week ago. Presumably by now your job has finished so there's not much we'll actually be able to see. My best guess on the reason for the low numbers coming from your SQL 4 CDS queries though are permissions - do you have rights to see all the emails?

I'd also have been interested to see some different ways of getting the results - SELECT COUNT(1) isn't something I've optimised for so far, though this will be in the next release. SELECT COUNT(*) will use some more efficient ways of running the query, and while the results should be the same it's possible there's a bug in there somewhere.

------------------------------
Matt Beard
Senior Software Engineer
Data8

Original Message:
Sent: Aug 13, 2020 05:35 AM
From: Jonas Rapp
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington (author of SQL 4 CDS) might have some input here? ​

------------------------------
Jonas Rapp
MVP
Sweden

Original Message:
Sent: Aug 05, 2020 10:06 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Hello Everyone,

Presently, database capacity is over limit for a client's tenant and it appears that the main culprit is that someone created Test from a full copy of Production. There are a large number of emails that come in to queues for this client and we have identified the ActivityPointerBase as a table of concern. I am looking to clear historical emails out of Test to free up space.

In doing so I am running the Bulk Record Deletion. I also ran SQL for CDS to understand how many records should be getting removed and get an idea of how long it might take. Unfortunately, something appear to be odd and I am hoping that someone in the community might be able to shed some light on this.

My deletion query is for email messages created on or before 8/31/2019. On Monday ~600,000 had been deleted and SQL for CDS was showing ~13,000 emails remaining.

Today I checked and the deletion job is still running and is showing ~6 million emails deleted but SQL for CDS show there are still about ~11,000 to go.

What would be the reason for this to have such different numbers?

Thanks
Scott

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------

OK, we'll probably be doing a similar process ourselves shortly so I'll run the same queries myself and see if I can spot anything going wrong.

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester
------------------------------

Original Message:
Sent: Aug 17, 2020 10:27 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Glad you can be back to your usual self!

I am leaning towards the bulk delete as the activitypointerbase has been ridiculously high when viewing the capacity, but it is odd as now with the 5k emails they are both reflecting the same number.

As for the 5k emails not deleting, I think I have an idea as to what the issue is and will probably need MS support. I can see the emails in Advanced Find and after digging it appears the common tie is that they all have a ghost attachment. If I attempt to delete the attachment or the email it throws an error saying the attachment does not exist.

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD

Original Message:
Sent: Aug 17, 2020 02:32 AM
From: Mark Carrington
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Woo, the forums seem to be working for me again today!

Do you have a feel for whether the original number of emails was closer to the numbers reported by SQL 4 CDS or bulk delete?

With the 5k emails still reported by SQL 4 CDS that bulk delete doesn't delete, can you see them in Advanced Find? Is there anything obviously in common about them that might explain why bulk delete isn't working?

------------------------------
Mark Carrington
Chief Technologist
Data8
Chester

Original Message:
Sent: Aug 14, 2020 11:54 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Thank you everyone for reaching out and the feedback.

Andy, Mark can confirm but SQL4CDS should do the same as accessing via T-SQL in SSMS. Within SQL4CDS you can enable the T-SQL endpoint to use that if desired.

Mark,
My user is a system admin in the environment so it shouldn't be permission related. Also, I found that using SELECT COUNT(1) and SELECT COUNT(*) returned the same results so I used COUNT(1) on my subsequent queries.

The the job has completed but there are still some interesting outcomes. Bulk Deletion shows there were 12,685,775 deleted, way more than the 13-11k returned in the SQL4CDS query. I would see thousands added to the deletion total before seeing it drop by 1 or 2 in the SQL query.
Additionally, if I query in SQL4CDS now I show 5,323 records still meeting my criteria. If I run a bulk delete with that criteria it shows the 5,323 as deleted but re-querying after the deletion once again shows the 5,323. They are reported as deleted but never are.

When building the Bulk Deletion job I select the entity "Email Messages", not sure if that plays into a difference with the "email" table queried in SQL??

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD

Original Message:
Sent: Aug 14, 2020 11:31 AM
From: Matt Beard
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington here posting under another account as the forums seem to be broken for me at the moment.

@Scott Florance, apologies for the delay, if the forums were working better I'd have responded to this a week ago. Presumably by now your job has finished so there's not much we'll actually be able to see. My best guess on the reason for the low numbers coming from your SQL 4 CDS queries though are permissions - do you have rights to see all the emails?

I'd also have been interested to see some different ways of getting the results - SELECT COUNT(1) isn't something I've optimised for so far, though this will be in the next release. SELECT COUNT(*) will use some more efficient ways of running the query, and while the results should be the same it's possible there's a bug in there somewhere.

------------------------------
Matt Beard
Senior Software Engineer
Data8

Original Message:
Sent: Aug 13, 2020 05:35 AM
From: Jonas Rapp
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

@Mark Carrington (author of SQL 4 CDS) might have some input here? ​

------------------------------
Jonas Rapp
MVP
Sweden

Original Message:
Sent: Aug 05, 2020 10:06 AM
From: Scott Florance
Subject: Bulk Record Deletion of Emails vs SQL for CDS Results

Hello Everyone,

Presently, database capacity is over limit for a client's tenant and it appears that the main culprit is that someone created Test from a full copy of Production. There are a large number of emails that come in to queues for this client and we have identified the ActivityPointerBase as a table of concern. I am looking to clear historical emails out of Test to free up space.

In doing so I am running the Bulk Record Deletion. I also ran SQL for CDS to understand how many records should be getting removed and get an idea of how long it might take. Unfortunately, something appear to be odd and I am hoping that someone in the community might be able to shed some light on this.

My deletion query is for email messages created on or before 8/31/2019. On Monday ~600,000 had been deleted and SQL for CDS was showing ~13,000 emails remaining.

Today I checked and the deletion job is still running and is showing ~6 million emails deleted but SQL for CDS show there are still about ~11,000 to go.

What would be the reason for this to have such different numbers?

Thanks
Scott

------------------------------
Scott Florance
Business Applications Consultant
KTL Solutions, Inc, Frederick MD
------------------------------