Personalized Community is here!
Quickly customize your community to find the content you seek.
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 1Discover the latest updates and new features to Dynamics 365 planned April 2021 through September 2021.
Release overview guides and videos Release Plan | Preview 2021 Release Wave 1 Timeline
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
we are going to upgrade our old CRM to 2011 verzion and I found somwhere, that we should do some cleaning at first. Our production DB has more than 270 GB (asyncoperationbase table has 220 GB + 10 GB index, thanks to KB I already know how to reduce this).
BUT: In [MyOrgName]_MSCRM DataBase we have more then 1600 tables beginning on PIF. Most of them are mentioned in table ImportFileBase with statuscode 2,3,4,5. It looks like some mess after (un)succesfuul data file imports. Is there any way how can I reduce this tables? I can't found any tool, blog, article about this.
Has anyone idea how can I handle with this?
I would follow the steps in the CRM on the field blog, they are !Microsoft's a team engineers
They recommend some scripts to run and they will have dinner thousands of grades from crm4 to CRM 2011
Thanks for reply, I've found some usefull news there, but still nothing about deleting PIF tables.
So these tables are not custom entity tables with a customisations prefix of PIF? I don't remember any system tables with this prefix in previous version of Dynamics CRM.
If not - they could have been created manually for the purposes of importing data. When you say they are 'mentioned' - is this just in the importfilebase file name? It could be that someone created these tables by had purely to import data from.
If this is the case, you'll need to delete them all before upgrading.
all custom entity tables starts with [org name]_ prefix.
Structure of all PIF_*** tables is
SN (PK, bigint, not null)
ImportDataID (uniqueidentifier, null)
COL0 (uniqueidentifier, null)
COL1 (nvarchar(500), null)
COLX (nvarchar(500), null)
and they have not been created manually (typing create table command). Content of these tables is the same as the content of csv files, which we have imported since we installed CRM few years ago. According to the ImportFile table over 14 000 files were imported using CRM built in Import Data wizard. And they left 1 600 PIF tables in [org name]_MSCRM database.
'Mentioned' means, that I can run this select:
from dbo.importfilebase f
join sys.objects s
on s.name = f.ParsedTableName
and a lot of inports were sucessfull and they ened without errors.
Can I just drop these tables? Aren't there any constrains whis has to be deleted?
I don't need data in PIF*** tables anymore, they are already in CRM entities (or I deal with the situation), but won't CRM need that tables?
If the tables are the Base and Extension tables for entities in CRM then you should delete the entity in CRM - which will in turn delete the table.
If they are not entity tables and have been created by CRM for import purposes (I don't remember it ever doing this though) - then I suspect you can safely delete them before the upgrade - there certainly isn't any need for them in CRM2011/2013.
I would run a test upgrade first by backing up your database and restoring it into a non-production environment.
Thanks for your answers.
Here it is a short review what I've done:
Those PIF_*** were not Base and Extension, it looks they work as a temporary tables when files are imported.
PIF_*** table is created when a file is imported. The content of the file is stored in the table while the header is stored in ImportFileBase. In a column ParsedTableName there is also a name of a the table where a actually data stored (for example PIF_23093_1Z21LF). After file is sucessfully imported PIF table and the reference in ParsedTableName are deleted.
So I dropped all table beginning with PIF using a primitive code:
select name,max_column_id_used,RANK() over (partition by '1' order by newid()) r
where name like 'PIF%'
declare @i int
set @i = 1
while @i <= (select MAX(r) from #tables)
declare @name varchar(100)
select @name = name from #tables where r = @i
exec('drop table dbo.'+@name)
set @i = @i + 1
We saved about 600MB after dropping 1600 tables. After testing I found just some problems displaying detailed error messages in imports. I can live with this. Probably I'll change the deleting code adding "and create_date < DATEADD(MONTH,-1,GETDATE())" condition.
Then I ran AsyncOperationBase table cleaning ( support2.microsoft.com/.../968520 ). Now it is only 40GB of data+indexes (it was 270! GB before). After shrinking database to 50 GB problem with importing files occured - system job was in Waiting status. After forcing the import subtask manually data has been imported. Workflows were running fine, so CRM Async Process was causing the problem. In Event Viewer there were errors with SQL connection time out. I've rebuilt all indexes and now it all works.
Business Applications communities