How Long Should a ProjectWise Database Upgrade Take? Upgrading to ProjectWise V8i (SELECTseries 3) requires that you convert the database schema of your existing ProjectWise database to meet the requirements of ProjectWise V8i (SELECTseries 3).Database conversion may be relatively quick (about 15 minutes or less), or it could take much longer due to the following 3 things:
1) Audit Trail RecordsAudit trail conversion time primarily depends on the total number of audit trail records in the database which includes:a) The records in the dms_audt tableb) The records in the audit trail truncation table (if you have one).
In general, you can expect about 1 to 3 minutes for each 1 million audit trail records in the database.
A secondary factor that may further impede audit trail conversion time is the number of document and/or user records in the database. In other words, a large number of document and/or user records in the database will cause the audit trail conversion to take longer than if there were not a large number of document and/or user records in the database.
2) Thumbnail Image Records When upgrading from ProjectWise V8 XM Edition, thumbnail image record conversion may also add to the database conversion time. Thumbnail conversion time depends solely on the number of rows in dms_thumb table. In general, you can expect about 100 minutes for each 1 million thumbnail image records in the database.
3) StatisticsOut-of-date database statistics can turn what would otherwise be a relatively quick upgrade into one that takes hours to complete. Always make sure you update your database statistics prior to upgrading the database.
For example, when running the DMS Convert on a SQL Server database which contains a very large Audit Trail Truncation table (to the order of magnitude of: 15,000,000 or more) you could see the database consuming 100% of the CPU on the database server, giving the perception of a hung DMS Convert Process.
The offending SQL Statement is:
insert into dms_audt_convN select rv.o_objguid, rv.o_name, rv.o_desc, rv.o_parentguid from dms_remv rv where rv.o_objguid is not null and not exists (select null from dms_proj p where p.o_projguid=rv.o_objguid) and not exists (select null from dms_doc d where d.o_docguid=rv.o_objguid) and exists (select null from dms_audt_convBckp a where rv.o_objguid=a.o_objguid)
Solution:
There are two possible workarounds depending on the need to retain the Archived Audit Trail data.1) If this data is no longer needed or only needs to be kept for audit purposes, backup this table and then remove it from the database. Removing it from the database will prevent the conversion process trying to convert this table.
2) If the table is actively used and needs to be retained the following index should be added to the table prior to attempting the DMS Convert.
CREATE NONCLUSTERED INDEX [i_dms_audt_4] ON [dbo].[TABLENAME] ([o_objguid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO
Replace the with the actual table name. If the server is already shut down for conversion purposes this table name can be found in the dms_gcfg table with the following query:
select o_textval from dms_gcfg where o_paramno = 119
The following tables illustrate conversion times taken from real-world databases:
Converting ProjectWise V8i (or later) to ProjectWise V8i (SELECTseries 3)
Number of Documents (dms_doc table)
Number of Audit Trail records (dms_audttable and audit trail truncation table)
Number of Thumbnails (dms_thumb table)
Approximate Conversion Time
DB1
2,900,00
15,000,000
1,500,000
1 hour
DB2
21,000,000
700,000
22 minutes
DB3
100,000
2,400,000
4 minutes
Converting ProjectWise V8 XM Edition to ProjectWise V8i (SELECTseries 3)
Number of Audit Trail Entries (dms_audttable and audit trail truncation table)
2,900,000
5 hours
2.5 hours
12 minutes