Lock error during 2013 conversion

spider1269spider1269 Member Posts: 67
edited 2014-12-23 in NAV Three Tier
I'm running a test conversion from 2009R2 to 2013. After opening the database in 2013 I get the standard prompts to convert and everything starts normally. After about 10 hours I've been getting "Locked by another user" errors and can't get past them. It's been the Value Entry, Ledger Entry Dimension tables. No one is logged into the stand alone server except for myself. Also, the database is in Single User mode so I'm not sure how these errors could be happening. When the error occurs I've checked SSMS and have found 2 Session id's with Administrator assigned to them. I'm assuming this is normal??

Any suggestions on troubleshooting would be helpful.

Comments

  • davmac1davmac1 Member Posts: 1,283
    I have not had this problem during any of the conversions I have run.
    The dimension conversion uses SQL. Does your user have sufficient SQL Server rights to that database to update tables? The process does not use the service tier since it is using sql statements for the dimension upgrade process.
  • spider1269spider1269 Member Posts: 67
    I'm logged in as Admin and I have full rights to the database. I haven't seen this before either. I'm a little baffled how I could get locking issues in Single user mode. I'm running the conversion again and will report back if it fails.
  • spider1269spider1269 Member Posts: 67
    I think I figured out what is happening. When the conversion fails it keeps a Session ID active and starts a new Session ID. I found 2 old sessions using sp_who2 in SQL that were connected to the database. None of these sessions were visible through SQL Activity Monitor or through NAV>File>Database>Information>Sessions. I'll update this with solved once I confirm this was the issue.
  • spider1269spider1269 Member Posts: 67
    Still working on the issue. I've tried disabling the indexes of the larger tables but it still fails on the "Ledger Entry Dimension" table with a "Locked by another user" error. I see that there's another thread dealing with the same issue with the G/L table. I'm working with Microsoft and will update the thread once we get a resolution.
  • spider1269spider1269 Member Posts: 67
    I was able to resolve the issue by adding RAM to the test server. We had 24GB and took it up to 90GB.
  • bbrownbbrown Member Posts: 3,268
    Curious about your solution, so a few questions:

    1. How large is your database?

    2. How large (# of records) are the tables you mention?

    3. What Edition of SQL are you using?
    There are no bugs - only undocumented features.
  • spider1269spider1269 Member Posts: 67
    Sorry, just saw this.

    1. 700 GB.

    2. We ended up having issues with G/L Entry (200M records), Value Entry ( 63M records), and Ledger Entry Dimension (315M records). We purged a lot of the old data just to get it to convert. We're looking at possibly starting with a new blank database and bringing in 2 years of historical data. We run 24/7 so we're not able to shut down for days to complete the upgrade.

    3. Our test box is SQL 2014 with 90 GB of RAM and 4 TB of SSD drives.
  • mdPartnerNLmdPartnerNL Member Posts: 802
    spider1269 wrote:
    I was able to resolve the issue by adding RAM to the test server. We had 24GB and took it up to 90GB.

    Was this on advice, or did you have that memory lying around?
  • spider1269spider1269 Member Posts: 67
    I had found several blogs and entries in Mibuso that suggested adding RAM helped to resolved their similar locking issue. Also, for my company if it fixed the issue buying RAM was cheaper than 2 employees spending several weeks trying to resolve what looked to be a recurring issue with upgrades on large databases. Every time we tried to convert the database took 2 - 3 days time so if adding RAM reduced that time it was a win as well.

    We had contacted our partner and Microsoft and they weren't able to help us except to point us to blogs they had found. We're looking at getting direct Microsoft support in the future.
Sign In or Register to comment.