Lock error during 2013 conversion

spider1269

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.

davmac1

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.

spider1269

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.

spider1269

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.

spider1269

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.

spider1269

I was able to resolve the issue by adding RAM to the test server. We had 24GB and took it up to 90GB.

bbrown

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?

spider1269

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.

mdPartnerNL

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?

spider1269

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.