Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
  • Welcome
  • Products
  • Support
  • About
  • More
  • Cancel
Content Management
  • Product Communities
  • ProjectWise
  • Content Management
  • More
  • Cancel
Content Management
Content Management Wiki ProjectWise SELECTseries 3 Database Upgrade Time
    • Sign in
    • -Content Management - Wiki
      • +About ProjectWise
      • +GreenBook - Best Practices
      • -SELECTsupport TechNotes and FAQ's
        • +Application Integration
        • +Bentley Transmittal Services for ProjectWise
        • How to setup MicroStation logging with ProjectWise Exlporer Integration TN
        • -Installation/Upgrade
          • Configuring ProjectWise On A Windows 2003 Server [TN]
          • Converting ProjectWise 3.8 To ProjectWise V8 XM Edition [TN]
          • DMS Convert and Installation Issues [TN]
          • ProjectWise 2004 Edition Requirements [TN]
          • Projectwise 2004 [FAQ]
          • ProjectWise Design Integration Connect Edition Client Unattended Install
          • ProjectWise Explorer V8i (SELECTseries 1) Client Compatibility [FAQ]
          • ProjectWise SELECTseries 2 Compatibility [TN]
          • ProjectWise SELECTseries 3 Database Upgrade Time
          • ProjectWise V08.09.04.Xx Modules - Supported Operating Systems [TN]
          • ProjectWise V8 XM Edition New Feature List [TN]
          • ProjectWise V8i Installation - Error 1720
          • Spatial version record mismatch or Spatial tables creation Failed
          • Troubleshooting Projectwise DMSCONV issues
          • Uninstalling A Projectwise Service That Will Not Uninstall [TN]
          • Upgrading To A New Minor Version Of ProjectWise 08.09.04.Xx To 08.09.04.Xx [TN]
          • Upgrading to SS3 08.11.09.91+ & Original Admin User is Deleted TN
          • Using Microsoft Installer Technology [TN]
          • Using ProjectWise Explorer V8i Against ProjectWise Integration Server V8i (SELECTseries 1) [TN]
          • XM Edition Requirements [TN]
        • +iPad Applications
        • +Licensing
        • +Orchestration Framework/Automation Service/Distribution Service
        • +ProjectWise Administrator
        • +ProjectWise Client
        • +ProjectWise Glossary
        • +ProjectWise Server
        • +ProjectWise StartPoint
        • +ProjectWise Web Server
      • +V8i Release Information
    • +Bentley i-model Composition Server
    • +Bentley i-model Composition Server V8i
    • +Bentley Transmittal Services (BTS)
    • +Field Data Management - Welcome
    • Installing PW error 0x80070643
    • Issues Resolution
    • Last Error [112235]
    • No users in PW Admin
    • Opening a Second or Third Excel .XLS (old format) in ProjectWise
    • Project Performance Dashboards
    • +ProjectWise Caching Server
    • +ProjectWise Change Storage Area
    • The Levels of ProjectWise Design Integration Application Integration
    • +ProjectWise Design Integration Server
    • +ProjectWise Design Integration V8i
    • +ProjectWise Document Publisher
    • +ProjectWise Edge
    • +ProjectWise Edge - Mobile
    • +ProjectWise Explorer Client
    • +ProjectWise Explorer Client V8i
    • ProjectWise Explorer Connect Edition Upd3 Unattended Install
    • +ProjectWise integration module for Revit
    • +ProjectWise Integration Server
    • +ProjectWise Publishing Server
    • +ProjectWise Solutions
    • +ProjectWise Web View Server
    • ProjectWise Project Insights
    • +ProjectWise Web
    • +ProjectWise Web View
    • +Microsoft Teams Integration
    • +ProjectWise Workflow Rules Engine
    • +Working from Home with ProjectWise

     
     Questions about this article, topic, or product? Click here. 

    ProjectWise SELECTseries 3 Database Upgrade Time

       
      Applies To 
       
      Product(s): ProjectWise
      Version(s): All
      Environment:  N/A
      Area:  N/A
      Subarea:  N/A
      Original Author: Bentley Technical Support Group
       

     

     

     

     

     

     

     

     

     

    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:

    • The database has a large number of audit trail records 
    • The database has a large number of thumbnail image records (if upgrading from V8 XM Edition)
    • The database statistics are not up-to-date

    1) Audit Trail Records
    Audit 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 table
    b) 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.

    • Please Note: The number of document and/or user records alone will not add time to the general database conversion time. Only when combined with a large number of audit trail records will the number of document and/or user records play a role in the database conversion time.

    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) Statistics
    Out-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.

    • Please Note: It is always recommended to run the dmsconv process in a test environment before your upgrade to determine if there will be any issues. In certain cases the new dmsconv process has been known to utilize 100% CPU usage while running the dmsconv process. This will cause a performance issue if other systems are using databases located on the same database server.

    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

    1,500,000

    21,000,000

    700,000

    22 minutes

    DB3

    100,000

    2,400,000

    100,000

    4 minutes

      

    Converting ProjectWise V8 XM Edition to ProjectWise V8i (SELECTseries 3)

     

    Number of Documents (dms_doc table)

    Number of Audit Trail Entries (dms_audttable and audit trail truncation table)

    Number of Thumbnails (dms_thumb table)

    Approximate Conversion Time

    DB1

    2,900,000

    15,000,000

    1,500,000

    5 hours

    DB2

    1,500,000

    21,000,000

    700,000

    2.5 hours

    DB3

    100,000

    2,400,000

    100,000

    12 minutes

    • ProjectWise V8i
    • ProjectWise SELECTsupport
    • dms convert
    • ProjectWise
    • Share
    • History
    • More
    • Cancel
    • Elisa McGraw Created by Elisa McGraw
    • When: Fri, Jul 8 2011 1:01 PM
    • Elisabeth Pry Last revision by Bentley Colleague Elisabeth Pry
    • When: Fri, Jun 7 2013 4:08 PM
    • Revisions: 4
    • Comments: 0
    Recommended
    Related
    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2021 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies