This script will now also take into account simultaneous company upgrades - using multiple GP clients to upgrade the company databases. (Tip: If you have a lot of GP companies to upgrade - or several large ones - using multiple clients to upgrade a subset of the companies can be a great way to reduce the upgrade time without affecting performance too much, providing the disk subsystem on the server can handle it.)
The estimated remaining time is based on the average completion time of companies that have completed so far. If you have only one GP company then this metric is pretty useless to you. If you have a few very small companies that already upgraded and the last one is your main company, the estimated time may be off by quite a bit, but you will still have some sort of idea how long it will take.
Special thanks to Lance Brigham for the upgrade statuses.
USE [DYNAMICS] DECLARE @NewVersion int DECLARE @NewBuild int SELECT @NewVersion = db_verMajor, @NewBuild = db_verBuild FROM DB_Upgrade WHERE PRODID = 0 AND db_name = 'DYNAMICS' IF object_id('tempdb..#Completed_List') IS NOT NULL BEGIN DROP TABLE #Completed_List END SELECT * INTO #Completed_List FROM ( SELECT Completed_Company = RTRIM(db_name), Started_At = (SELECT TOP 1 start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0), Ended_At = CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN GETDATE() ELSE MAX(stop_time) END, Run_Time = CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN GETDATE()-(SELECT TOP 1 start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0) ELSE MAX(stop_time)-(SELECT TOP 1 start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0) END, Upgrading = CASE WHEN EXISTS (select top 1 * from DU000030 DU INNER JOIN SY01500 SY on DU.companyID = SY.CMPANYID WHERE DU.Status NOT IN (0,15) AND DU.errornum <> 0 AND SY.INTERID = DBU.db_name) THEN -1 WHEN MAX(db_status) NOT IN (0,15) AND NOT EXISTS (SELECT * FROM duLCK L WHERE L.INTERID = DBU.db_name) THEN -1 WHEN MAX(db_status) NOT IN (0,15) THEN 1 WHEN EXISTS (SELECT * FROM duLCK L WHERE L.INTERID = DBU.db_name) THEN 2 ELSE 0 END, Status = db_status FROM DB_Upgrade DBU WHERE ( EXISTS( select * from DB_Upgrade where db_verMajor = @NewVersion and db_verBuild = @NewBuild AND PRODID = 0 AND db_name <> 'DYNAMICS' and db_name = DBU.db_name ) ) AND PRODID = 0 GROUP BY db_name, db_status UNION SELECT Completed_Company = RTRIM(db_name), Started_At = NULL, Ended_At = NULL, Run_Time = 0, Upgrading = 2, Status = db_status FROM DB_Upgrade DBU WHERE EXISTS ( select * from duLCK where DBU.db_name = duLCK.INTERID ) AND PRODID = 0 AND db_status = 0 GROUP BY db_name, db_status ) a SELECT Status = CASE Upgrading WHEN 2 THEN 'Pending' WHEN -1 THEN 'Failed' WHEN 1 THEN 'Upgrading' ELSE 'Completed' END, Company = CL.Completed_Company, [Last Step Completed] = CASE Status WHEN 0 THEN '' WHEN 1 THEN 'Step 1/59: Upgrade started' WHEN 2 THEN 'Step 2/59: Defaults loaded' WHEN 3 THEN 'Step 3/59: Tables created' WHEN 4 THEN 'Step 4/59: Indexes created' WHEN 5 THEN 'Step 5/59: Views created' WHEN 6 THEN 'Step 6/59: Dexterity procs created' WHEN 7 THEN 'Step 7/59: Changed Dexterity procs dropped' WHEN 8 THEN 'Step 8/59: Changed application procs dropped' WHEN 9 THEN 'Step 9/59: Changed indexes dropped' WHEN 10 THEN 'Step 10/59: Changed views dropped' WHEN 11 THEN 'Step 11/59: Changed triggers dropped' WHEN 12 THEN 'Step 12/59: Changed rules dropped' WHEN 13 THEN 'Step 13/59: Changed tables dropped' WHEN 14 THEN 'Step 14/59: SQL code updates ran' WHEN 15 THEN 'Step 15/59: New tables added' WHEN 16 THEN 'Step 16/59: Loading table stored procedures' WHEN 17 THEN 'Step 17/59: Loading required data' WHEN 21 THEN 'Step 21/59: Existing data conversion process started' WHEN 23 THEN 'Step 23/59: Existing data conversion process checkpoint' WHEN 30 THEN 'Step 30/59: Existing data conversion process completed' WHEN 41 THEN 'Step 41/59: New views added' WHEN 42 THEN 'Step 42/59: New triggers added' WHEN 43 THEN 'Step 43/59: Rules created' WHEN 44 THEN 'Step 44/59: Stubs created' WHEN 45 THEN 'Step 45/59: Misc stored procs created' WHEN 46 THEN 'Step 46/59: FRx data created' WHEN 47 THEN 'Step 47/59: Permissions script ran' WHEN 48 THEN 'Step 48/59: Table defaults bound' WHEN 49 THEN 'Step 49/59: Procs recompiled' WHEN 53 THEN 'Step 53/59: Functions loaded' WHEN 54 THEN 'Step 54/59: Application stored procs created, running misc scripts now' ELSE 'Step ' + CAST(Status as CHAR(2)) + '/59' END, Pending = CASE WHEN Upgrading = 1 THEN (select COUNT(*) FROM duLCK WHERE NOT EXISTS (SELECT * FROM #Completed_List AS c INNER JOIN duLCK as l ON c.Completed_Company = l.INTERID WHERE upgrading = 1 and INTERID = duLCK.INTERID) ) ELSE NULL END, [Not Upgraded] = CASE WHEN Upgrading = 1 THEN (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') ELSE NULL END, Upgraded = CASE WHEN Upgrading = 1 THEN (select COUNT(*) from DB_Upgrade WHERE (db_verMajor = @NewVersion AND db_verBuild = @NewBuild AND db_verOldMajor = db_verMajor AND db_verOldBuild = db_verBuild) and PRODID = 0 and db_name <> 'DYNAMICS') ELSE NULL END, [Avg Time Per DB] = CASE WHEN Upgrading = 1 THEN CONVERT(varchar(4),DATEPART(DAY,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))-1) + 'd ' + CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'h ' + CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'm ' + CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 's ' ELSE '' END, [Est. Remaining Time] = CASE WHEN Upgrading = 1 THEN CONVERT(varchar(4),DATEPART(DAY,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0) + ( (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) - (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1) ))-1) + 'd ' + CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0) + ( (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) - (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1) ))) + 'h ' + CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0) + ( (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) - (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1) ))) + 'm ' + CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0) + ( (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) - (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1) ))) + 's ' ELSE '' END, [Estimated End Time] = CASE WHEN Upgrading = 1 THEN CONVERT(varchar(20), GETDATE() + (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0) + ( (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) - (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1) ) ) ELSE '' END , [Elapsed Time] = CASE WHEN Upgrading = 1 THEN CONVERT(varchar(4),DATEPART(DAY,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))-1) + 'd ' + CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'h ' + CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'm ' + CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 's ' ELSE '' END FROM #Completed_List CL WHERE Upgrading <> 0 ORDER BY Status DESC, Company SELECT [Completed Company] = CASE WHEN Upgrading = 1 THEN Completed_Company + ' - Upgrading' WHEN Upgrading = -1 THEN Completed_Company + ' - Failed' ELSE Completed_Company END, [Completed Company Name] = RTRIM(CMPNYNAM), [Started At] = Started_At, [Ended At] = CASE WHEN Upgrading = -1 THEN Started_At ELSE Ended_At END, [Run Time] = CASE WHEN Upgrading = -1 THEN '0h 0m 0s' ELSE CONVERT(varchar(4),DATEPART(DAY,(Run_Time))-1) + 'd ' + CONVERT(varchar(4),DATEPART(HOUR,(Run_Time))) + 'h ' + CONVERT(varchar(4),DATEPART(MINUTE,(Run_Time))) + 'm ' + CONVERT(varchar(4),DATEPART(SECOND,(Run_Time))) + 's ' END FROM #Completed_List LEFT JOIN SY01500 ON #Completed_List.Completed_Company = SY01500.INTERID WHERE Upgrading <> 2 ORDER BY Upgrading DESC, [Ended At] DESC IF object_id('tempdb..#Completed_List') IS NOT NULL BEGIN DROP TABLE #Completed_List END
