A few years ago I created a script (
version 1) to see how far along Dynamics GP Utilities was in the process of upgrading company databases. If you've ever upgraded GP before you know the upgrade screen doesn't always refresh nor does it give any real indication of time. With the arrival of GP 2015, I needed to update my original script to work correctly. I figured it was also about time to give the script a major face-lift.
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
