Upgrade Progress Script v2

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

How to See the Current Status of a Dynamics GP Upgrade or Service Pack

UPDATE: See updated version here.

I've referenced before that we have a customer with over 80 GP companies; they are the mother of many script inventions for me as I have no desire to manually do anything for 80 databases. Well, now they are at 90.

Needless to say, one of the many challenges with such a large number of companies is keeping track of where the database processing is during an upgrade or service pack. It would also be nice to know how long the thing is going to take.

Here is a handy little script I created to help keep track of this:

Edit 8/20/2014: Fixed a couple letter cases to work properly for Binary SQL sorting
Edit 10/15/2014: See updated script here

DECLARE @NewVersion int
DECLARE @NewBuild int

SET @NewVersion = 11    -- Change this to the major version number you are upgrading TO
SET @NewBuild = 1752    -- Change this to the build number you are upgrading TO

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 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 start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            ELSE
                MAX(stop_time)-(SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            END,
        Upgrading = CASE WHEN MAX(db_status) <> 0 THEN 1 ELSE 0 END
    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
        )
    GROUP BY db_name
) a

SELECT 
    Currently_Upgrading = ISNULL((select TOP 1 db_name from DB_Upgrade where ((db_verMajor = @NewVersion AND db_verBuild = @NewBuild) AND (db_verOldMajor <> db_verMajor OR db_verOldBuild <> db_verBuild)) and PRODID = 0 and db_name <> 'DYNAMICS' order by start_time desc),
                                (SELECT TOP 1 db_name FROM DB_Upgrade WHERE db_status <> 0)),
    Not_Upgraded = (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS'),
    Upgraded = (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'),
    Average_Time_Per_DB = 
        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 '
    ,
    Elapsed_Time =
        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 '
    ,
    Estimated_Remaining_Time = 
        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 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 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 Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 's '
                                
SELECT
        Completed_Company = CASE WHEN Upgrading = 1 THEN Completed_Company + ' - Upgrading' ELSE Completed_Company END,
        Started_At,
        Ended_At,
        Run_Time = 
            CONVERT(varchar(4),DATEPART(HOUR,(Run_Time))) + 'h ' +
            CONVERT(varchar(4),DATEPART(MINUTE,(Run_Time))) + 'm ' +
            CONVERT(varchar(4),DATEPART(SECOND,(Run_Time))) + 's '
FROM
    #Completed_List
ORDER BY 
    Upgrading DESC, 
    Ended_At DESC

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END