Thursday, November 10, 2011

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

9 comments:

  1. Cool. I made one very similar to this. If I can find all the values for db_status, we can build it into the script also.

    --Status Constant Storage Value
    --DU_STATUS_DONE 0
    --DU_STATUS_START 1
    --DU_STATUS_INSTALL 2
    --DU_STATUS_UPGRADE 3
    --DU_STATUS_BIND_DEFAULTS 7
    --DU_STATUS_RECOMPILE 8
    -- 9
    -- 10
    -- 15
    -- 16
    --DU_STATUS_CONVERT 23
    --DU_STATUS_POST_CONVERT 30
    -- 43
    -- 48
    -- 53
    -- 54



    SELECT db_name, PRODID, db_verMajor, db_verBuild, db_status,
    Left(Convert(varchar(11) ,start_time, 0),11)+' ' + right(Convert(varchar ,start_time, 8), 8) as StartTime,
    Left(Convert(varchar(11) ,stop_time, 0),11)+' ' + right(Convert(varchar ,stop_time, 8), 8) as StopTime,
    Convert(varchar, stop_time - start_time, 8) AS Duration,
    Case
    When db_status = 0 then '1 - Upgraded'
    When db_status <> 0 Then '2 - IN PROCESS'
    End as Status
    FROM DB_Upgrade WHERE PRODID = 0 and db_verBuild = 1860
    Union All
    SELECT db_name, PRODID, db_verMajor, db_verBuild, db_status,
    '' as StartTime, '' as StopTime,
    '' AS Duration,
    '3 - Not Upgraded' as status
    FROM DB_Upgrade WHERE PRODID = 0 and db_verBuild < 1860
    ORDER BY status, StartTime asc

    ReplyDelete
    Replies
    1. I've been looking for something like this for so long! We have about 50 entities sitting in our GP and I usually just average out 3-6 hours of DB update time for each SP and then tack on client installation time. Now I can really follow the progress and plan around it!

      Thank you!

      Delete
  2. Hi,

    Just ran across this and it's great! So much more info than the one I built. You might want to look at the formula for the estimated remaining time - it seems to result in a time that's too long.

    Kind regards,

    Leslie

    ReplyDelete
    Replies
    1. Leslie - I'm replying to a really old comment here, but basically the calculation is an average of the time of all finished upgrades times the number of companies not upgraded.
      I'm actually working on a newer script that will show the companies selected for upgrade (which could be different than the number of non-upgraded companies because you may not be trying to upgrade all companies when running a test upgrade)

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. USE DYNAMICS --change to GP system database if not DYNAMICS

    DECLARE @PRODID INTEGER=0 --change to different prod ID if looking to track module add-on

    SELECT db_name, db_verMajor, db_verBuild
    ,CONVERT(VARCHAR(30),start_time,120) StartTime
    ,CASE WHEN start_time<>stop_time THEN
    CONVERT(VARCHAR(30),stop_time,120)
    ELSE '' END AS StopTime
    ,CASE WHEN start_time<>stop_time THEN CONVERT(VARCHAR, stop_time - start_time, 8)
    ELSE CONVERT(VARCHAR, GETDATE() - start_time, 8) END AS Duration
    ,CASE WHEN db_status = 0 THEN '1 - Upgraded'
    WHEN db_status <> 0 THEN '2 - IN PROCESS'
    END AS Status
    ,CASE WHEN db_status = 0 THEN ''
    WHEN db_status = 1 THEN 'Step 1/59: Upgrade started'
    WHEN db_status = 2 THEN 'Step 2/59: Defaults loaded'
    WHEN db_status = 3 THEN 'Step 3/59: Tables created'
    WHEN db_status = 4 THEN 'Step 4/59: Indexes created'
    WHEN db_status = 5 THEN 'Step 5/59: Views created'
    WHEN db_status = 6 THEN 'Step 6/59: Dexterity procs created'
    WHEN db_status = 7 THEN 'Step 7/59: Changed Dexterity procs dropped'
    WHEN db_status = 8 THEN 'Step 8/59: Changed application procs dropped'
    WHEN db_status = 9 THEN 'Step 9/59: Changed indexes dropped'
    WHEN db_status = 10 THEN 'Step 10/59: Changed views dropped'
    WHEN db_status = 11 THEN 'Step 11/59: Changed triggers dropped'
    WHEN db_status = 12 THEN 'Step 12/59: Changed rules dropped'
    WHEN db_status = 13 THEN 'Step 13/59: Changed tables dropped'
    WHEN db_status = 14 THEN 'Step 14/59: SQL code updates ran'
    WHEN db_status = 15 THEN 'Step 15/59: New tables added'
    WHEN db_status = 16 THEN 'Step 16/59: Loading table stored procedures'
    WHEN db_status = 17 THEN 'Step 17/59: Loading required data'
    WHEN db_status = 21 THEN 'Step 21/59: Existing data conversion process started'
    WHEN db_status = 23 THEN 'Step 23/59: Existing data conversion process checkpoint'
    WHEN db_status = 30 THEN 'Step 30/59: Existing data conversion process completed'
    WHEN db_status = 41 THEN 'Step 41/59: New views added'
    WHEN db_status = 42 THEN 'Step 42/59: New triggers added'
    WHEN db_status = 43 THEN 'Step 43/59: Rules created'
    WHEN db_status = 44 THEN 'Step 44/59: Stubs created'
    WHEN db_status = 45 THEN 'Step 45/59: Misc stored procs created'
    WHEN db_status = 46 THEN 'Step 46/59: FRx data created'
    WHEN db_status = 47 THEN 'Step 47/59: Permissions script ran'
    WHEN db_status = 48 THEN 'Step 48/59: Table defaults bound'
    WHEN db_status = 49 THEN 'Step 49/59: Procs recompiled'
    WHEN db_status = 53 THEN 'Step 53/59: Functions loaded'
    WHEN db_status = 54 THEN 'Step 54/59: Application stored procs created, running misc scripts now'
    ELSE 'Step ' + CAST(db_status as CHAR(2)) + '/59'
    END
    AS LastStepCompleted
    AS LastStepCompleted
    FROM DB_Upgrade a WHERE PRODID=@PRODID
    and db_verMajor = (SELECT db_verMajor FROM DB_Upgrade WHERE PRODID = @PRODID AND db_name = DB_NAME())
    and db_verBuild = (SELECT db_verBuild FROM DB_Upgrade WHERE PRODID = @PRODID AND db_name = DB_NAME())
    UNION ALL
    SELECT db_name, db_verMajor, db_verBuild
    ,'' AS StartTime
    , '' AS StopTime
    ,'' AS Duration
    ,'3 - Not Upgraded' as Status
    ,''
    FROM DB_Upgrade a WHERE PRODID=@PRODID
    AND (db_verMajor < (SELECT db_verMajor FROM DB_Upgrade WHERE PRODID = @PRODID AND db_name = DB_NAME())
    OR (db_verMajor = (SELECT db_verMajor FROM DB_Upgrade WHERE PRODID = @PRODID AND db_name = DB_NAME())
    AND db_verBuild < (SELECT db_verBuild FROM DB_Upgrade WHERE PRODID = @PRODID AND db_name = DB_NAME())))
    ORDER BY Status,StartTime

    ReplyDelete
    Replies
    1. I ran out of space to add this verbiage to the above post w/script, but wanted to add the following:

      This is the script I use which takes into account as many steps/db_status as possible with their descriptions, and is easy to switch between DYNAMICS as the system database and a named system database by changing the first line. You can also change the value of @PRODID if looking to track module add-on to an existing GP environment as opposed to core upgrade. I've been using this script for years and tweaking it here and there along the way. I think I found the original basis for this from DavidMO because it looks very similar so credit to DavidMO for the starting point of this.

      Delete
    2. @Lance - Do you mind if I use your status listing in a newer version of the upgrade progress script I'm working on? I've never seen a list this complete before.

      Delete
  5. This comment has been removed by a blog administrator.

    ReplyDelete