I've put the DB in simple recovery model and expanded the size of the database to be twice that of the original data (the size is now over 100GB) - is this a normal amount of time to wait before 1% on an upgrade of this size? I've already moved the data from NAV4 to NAV2009R2 which only took a couple of hours. I need to take into account the time to upgrade to 2013 when planning the production database upgrade - and having the upgrade take more than two days is going to cause all sorts of problems for the client (ideally I'd like to get it done in a day!)
I'm running everything locally - the machine that is running this processed a v400 to 2009R2 update in a matter of minutes for the 50gig db (it took a few minutes for upgrade step 1 for the biggest company and less than an hour to finish the second upgrade step)
My company database/tempdb are not growing and the machine performance is as so:
30-35% cpu usage (most of that is SQL server)
at points 100% disk utilisation, at the moment not much
almost 100% ram utilisation
I'm on decent machine running 2 quad core i7s and an SSD - 16gig of ram, and like I said - the first upgrade took no time at all.
I've left this running all day and overnight and it's still on the 'preparing for upgrade' stage (0% progress)
Checking SQL shows the following UPDATE statement is running:
Code: Select all
update [Company Database$upgrade355_Dim] set DimSetID= (select DimSetID from [Company Database$upgrade355_DimSetID] as D where ((D.DimVal1 = [Company Database$upgrade355_Dim].DimVal1) or (D.DimVal1 is null) and ([Company Database$upgrade355_Dim].DimVal1 is null)) and ((D.DimVal2 = [Company Database$upgrade355_Dim].DimVal2) or (D.DimVal2 is null) and ([Company Database$upgrade355_Dim].DimVal2 is null)) and ((D.DimVal3 = ... etc
There don't appear to be any resource waits - I've run the query that SQL management studio runs to get a list of locks for process monitor, and the only entry is:
DB: Company Database
App: .Net SqlClient Data Provider
I/O (MB): 106
Last Req: 2013-10-01 21:27:08.693
So as far as I can see, SQL is actually just processing.
Checking the stats for IO I get
CPU ms/sec: 949
L. Writes/sec: 2
L. Reads/sec: 831404
Execution plan says that the heavy hitting operation is a key lookup against a clustered index which says it estimates at 80 million executions - this is taking up 79% of the query cost
- in summary, it looks like SQL is doing something, but it's been doing this for hours and hours - is there anything I can do to speed this up?
Maybe you can run on the production machine of the customer on a sunday or something like that.
Otherwise you can go to upgrade service centers who have servers that can run this routine
I'm now getting an error on the Document Dimension table so I'm having to try and debug (it's not making a lot of sense at the moment) but that's another story!
Let's hope it's quicker on the production server!
Thanks for everyone's input
Deleting these got everything going and finally it finished!
Thanks to anyone who had a look
I am also stuck at 2nd upgrade toolkit as "Ledger Entry Dimensions" are preparing to update.
Please suggest me the solution.
My DB Size is 110 GB. "Ledger Entry Dimensions" table has 3,56,76,241 records.
Thanks & Regards,
We have 67m entries in T355 and it takes about 35 minutes to convert it - the test server I'm using has 4vCPUs, 80GB of RAM and SQL on it is limited to 64GB
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
for more info about the feature