Dynamics GP
Dynamics GP
Organizer:
- JOSE Organized by
-
-
GP Upgrade Duration Tool
-
GP Upgrade Duration Tool
So, many years ago, I got bored and a little frustrated watching the GP upgrade progress. I had recently moved from a 4 GP Company installation to a 32 Company site and practiced the upgrade several times to catch issues, and to estimate completion time. This would have been around 2012 or so, I think.
If you have watched the GP upgrade appear to pause or get stuck during the upgrade, you have felt the pain.
So, I did what any geeky SQL guy would do and set about creating my own measurement tool. I reached out to the community to better understand the phases of the process and came up with a basic script to capture steps and durations.
Since then, I have added little refinements and fixes to get a more accurate progress snapshot. There are still plenty of ideas, enhancements and fixes left to try as my time allows.
I posted early versions on this and other forums over the years, but I noticed my code got lost as the community features were pared back from the old days. Back when we got daily or weekly notices on questions and there was a regular place to post shared code.
Feel free to use this during your upgrade testing or production roll-out. Share with others.
I just ask that you keep the comments and my name in place.
————————————————————————————————————-
Use Dynamics
DECLARE @db_verMajor AS INT = 18
DECLARE @db_verBuild AS INT = 1756
/*
Change the variable(s) to your upgrade version. Execute as needed to get the latest stats.
*/
–GP Version (RTM) Major Version Build Version Release Date
–GP 2018 18.8.1904 18 1904 10/1/2025
–GP 2018 18.7.1756 18 1756 10/1/2024
–GP 2018 18.6.1685 18 1685 10/2/2023
–GP 2018 18.5.1556 18 1556 10/3/2022
–GP 2018 18.4.1361 18 1361 10/1/2021
–GP 2018 18.3.1173 18 1173 10/5/2020
–GP 2018 18.2.1013 18 1013 10/7/2019
–GP 2018 R2=704 18 0628 10/2/2018
–GP 2018 R2=400 18 0400 12/1/2017
/*
So many years ago I got bored
*/
— GP Upgrade Duration script – Version 4.64
declare @ScriptVersion AS char(5) = ‘4.64’
— Dynamics..DB_Upgrade Table
— Dynamics..SY01500
— Written by David Morinello
— Updated 12/08/2016
— Updated 09/19/2018 -Improved Elapsed time calculations
— Updated 01/23/2019 – Fixed Variable type bug
— Updated 05/21/2019 – Added CPU & RAM numbers
— Updated 02/10/2025 – Added Additional Version Variable Values
— Tells you you current status when run during a GP upgrade
— Update the db_verBuild = 1860 to the version you are upgrading to
— Added Script to get CPU and Memory Info
— Added Release Dates
— Added New GP build version
–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
–Table Conversion Step 23
–DU_STATUS_POST_CONVERT 30
— 43
— 48
— 49
— 52
— 53
— 54
–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
–DU_STATUS_CONVERT 23
–DU_STATUS_POST_CONVERT 30
–Step Description
–1 Start of Process
–2 If an install, load Defaults (directory\DEFAULTS.extension)
–3 If an install, create Tables (directory\TABLES.extension)
–4 If an install, create Indexes (directory\INDEXES.extension)
–5 If an install, create Views (directory\VIEWS.extension)
–6 If an install, create DexProcs (directory\DEXPROCS.extension)
–7 If an upgrade, drop changed DexProcs (directory\DEXPROCS.DRP)
–8 If an upgrade, drop changed App Procs (directory\APPPROCS.DRP)
–9 If an upgrade, drop changed Indexes (directory\INDX.DRP)
–10 If an upgrade, drop changed Views (directory\VIEWS.DRP)
–11 If an upgrade, drop changed Triggers (directory\TRIGGERS.DRP)
–12 If an upgrade, drop changed Rules (directory\RULES.DRP)
–13 If an upgrade, drop deleted Tables (directory\TABLES.DRP)
–14 If an upgrade, run any SQL code updates (directory\SQL.NEW)
–15 If an upgrade, add new Tables (directory\TABLES.NEW)
–16 If an upgrade, add new Indexes (directory\INDX.NEW)
–17 If an upgrade, add new DexProcs (directory\DEXPROCS.NEW)
–18 If Lesson DB and an install, create Stubs (directory\STUBS.extension)
–19 If Lesson DB and an install, create Procs (directory\PROCS.extension)
–20 If Lesson DB and an install, create Tables (SQL\LESSON\TWO.CMP)
–21 If Lesson DB and an install, BCP in data (SQL\LESSON\DATA\BCPTEMP.BAT)
–21 If (not System DB) and an upgrade, start conversion/synchronization process
–21 If System DB, BCP in data (SQL\SYSTEM\DATA\BCPTEMP.BAT)
–30 End of conversion/synchronization process
–41 If an upgrade, add new Views (directory\VIEWS.extension)
–42 Create Triggers (directory\TRIGGERS.extension)
–43 Create Rules (directory\RULES.extension)
–44 If not Lesson DB, create Stubs (directory\STUBS.extension)
–45 If not Lesson DB, create Procs (directory\PROCS.extension)
–46 If (not System DB) and an install, create FRx data (directory\FRXDATA.SQL)
–47 If (not System DB), grant access (EXEC DBName..smGrantAccessOnAccountMSTR)
–48 Bind table defaults (EXEC DBName..smBindTableDefaults)
–49 Recompile procs (SQL\UTIL\RECOMP.SQL)
–50 If Company DB and an install, run Dynamics front-end logic to create the company
–51 If not System DB, create Business Alerts (SQL\COMPANY\ALERTS.SQL)
–0 Process completed
— Look up and calulate the DB Sizes
if object_id(‘tempdb..#spacetable’) is not null
drop table tempdb..#spacetable
create table #spacetable
(
database_name varchar(50) ,
[total db size] bigint
)
insert into #spacetable
execute master.sys.sp_MSforeachdb ‘USE [?];
select x.[DATABASE NAME],
y.[total size log]+x.[total size data] ”total db size”–,
from (select DB_NAME() ”DATABASE NAME”, sum(size*8/1024) ”total size data”,sum(FILEPROPERTY(name,”SpaceUsed”)*8/1024) ”space util”
,case when sum(size*8/1024)=0 then ”divide by zero” else
substring(cast((sum(FILEPROPERTY(name,”SpaceUsed”))*1.0*100/sum(size)) as CHAR(50)),1,6) end ”percent fill”
from sys.master_files where database_id=DB_ID(DB_NAME()) and type=0
group by type_desc ) as x ,
(select
sum(size*8/1024) ”total size log”,sum(FILEPROPERTY(name,”SpaceUsed”)*8/1024) ”space util”
,case when sum(size*8/1024)=0 then ”divide by zero” else
substring(cast((sum(FILEPROPERTY(name,”SpaceUsed”))*1.0*100/sum(size)) as CHAR(50)),1,6) end ”percent fill”
from sys.master_files where database_id=DB_ID(DB_NAME()) and type=1
group by type_desc )y’
——
declare @CompanyCount as int
select @CompanyCount = count(*) from Dynamics..SY01500
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 ‘0 – DU_STATUS_DONE’
when db_status = 1 then ‘2 – In Process: 1 – DU_STATUS_START’
when db_status = 2 then ‘2 – In Process: 2 – DU_STATUS_INSTALL’
when db_status = 3 then ‘2 – In Process: 3 – DU_STATUS_UPGRADE’
when db_status = 7 then ‘2 – In Process: 7 – DU_STATUS_BIND_DEFAULTS’
when db_status = 8 then ‘2 – In Process: 8 – DU_STATUS_RECOMPILE’
when db_status = 9 then ‘2 – In Process: 9 – Drop changed Indexes (directory\INDX.DRP)’
when db_status = 10 then ‘2 – In Process: 10 – Drop changed Views (directory\VIEWS.DRP)’
when db_status = 15 then ‘2 – In Process: 15 – Add new Tables (directory\TABLES.NEW)’
when db_status = 16 then ‘2 – In Process: 16 – Add new Indexes (directory\INDX.NEW)’
when db_status = 23 then ‘2 – In Process: 23 – DU_STATUS_CONVERT’
when db_status = 30 then ‘2 – In Process: 30 – DU_STATUS_POST_CONVERT’
when db_status = 43 then ‘2 – In Process: 43 – Create Rules (directory\RULES.extension)’
when db_status = 48 then ‘2 – In Process: 48 – Bind table defaults (EXEC DBName..smBindTableDefaults)’
when db_status = 49 then ‘2 – In Process: 49 – Recompile procs (SQL\UTIL\RECOMP.SQL)’
when db_status = 51 then ‘2 – In Process: 51 – Create Business Alerts (SQL\COMPANY\ALERTS.SQL)’
when db_status = 52 then ‘2 – In Process: 52’
when db_status = 53 then ‘2 – In Process: 53’
when db_status = 54 then ‘2 – In Process: 54’
else ‘2 – In Process’
end as Status,
format(#spacetable.[total db size] , ‘N0’) as [Total_Database_Size]
from DB_Upgrade inner join #spacetable on DB_Upgrade.db_name = #spacetable.database_name
where PRODID = 0 and db_verBuild = @db_verBuild
union all
select db_name, PRODID, db_verMajor, db_verBuild, db_status,
” as StartTime, ” as StopTime,
” as Duration,
‘3 – Not Upgraded’ as status,
format(#spacetable.[total db size] , ‘N0’) as [Total_Database_Size]
from DB_Upgrade inner join #spacetable on DB_Upgrade.db_name = #spacetable.database_name
where PRODID = 0 and (db_verBuild < @db_verBuild or db_verMajor < @db_verMajor)
order by status, StartTime asc
— Percentage Complete
select count(*) as ‘Completed’, @CompanyCount as ‘Total Company DBs’,
rtrim(cast(cast(cast(count(*)as decimal)/@CompanyCount as decimal(3,2)) * 100 as char)) as ‘Percent Complete’
from DB_Upgrade where PRODID = 0 and db_verBuild = @db_verBuild and db_status = 0 and DB_Name <> ‘Dynamics’
declare @Percentage as float = 0.00
declare @Duration as bigint = 0
declare @Duration2 as bigint = 0
declare @Duration3 as bigint = 0
declare @DurationH as bigint = 0
declare @DurationM as bigint
declare @StartTime as datetime –Char(50)
declare @DurationEstimate as bigint = 0–DateTime –Char(50)
–***************************************************
select @Percentage = rtrim(cast(cast(cast(count(*)as decimal)/@CompanyCount as decimal(3,2)) as char))
from DB_Upgrade where PRODID = 0 and db_verBuild = @db_verBuild and db_status = 0 and DB_Name <> ‘Dynamics’
select @DurationH = sum(Duration) / 3600,
@DurationM = (sum(Duration) / 3600) / 60
from
(
select datediff(second, min(Start_Time), max(Stop_time)) as Duration
from DB_Upgrade
where PRODID = 0 and db_verBuild = @db_verBuild and db_status = 0
) y
–Improve Duration calc (I hope ;-})
SELECT @Duration = Duration
FROM
(
SELECT DATEDIFF(SECOND, MIN(Start_Time), MAX(Stop_time)) AS Duration
FROM DB_Upgrade
WHERE PRODID = 0 AND db_verBuild = @db_verBuild AND db_status = 0
)x
SELECT
CONVERT(VARCHAR(12), @Duration /60/60/24) + ‘ Day(s), ‘
+ CONVERT(VARCHAR(12), @Duration /60/60 % 24) + ‘ Hour(s), ‘
+ CONVERT(VARCHAR(2), @Duration /60 % 60) + ‘ Minute(s), ‘
+ CONVERT(VARCHAR(2), @Duration % 60) + ‘ Second(s).’ AS [Elapsed Time]
— Estimate Time Calculation
IF @Percentage = 0
BEGIN
SELECT 0 AS [Percentage]
END
ELSE BEGIN
SELECT @Duration2 = (@Duration * 100) / (@Percentage*100)
SELECT @Duration3 = @Duration * (100/@Percentage)
END
SELECT @StartTime = LEFT(CONVERT(VARCHAR(11) ,start_time, 0),11)+’ ‘ + RIGHT(CONVERT(VARCHAR ,start_time, 8), 8)
FROM DB_Upgrade
WHERE PRODID = 0 AND db_verBuild = @db_verBuild AND db_name = ‘Dynamics’
— Times Calculations were broken for over 24 hours [Estimated Completion time], Now fixed(I hope)
–select @DurationEstimate = @Duration2 * (100/@Percentage)
SELECT
CONVERT(VARCHAR(12), @Duration2 /60/60/24) + ‘ Day(s), ‘
+ CONVERT(VARCHAR(12), @Duration2 /60/60 % 24) + ‘ Hour(s), ‘
+ CONVERT(VARCHAR(2), @Duration2 /60 % 60) + ‘ Minute(s), ‘
+ CONVERT(VARCHAR(2), @Duration2 % 60) + ‘ Second(s).’ AS [Estimate Time to Complete]
SELECT @StartTime AS [Start Time], DATEADD(ss, @Duration2, @StartTime) AS [Estimate Date/Time to Complete]
–Drop Temp Table that looks up the DB Sizes
DROP TABLE #spacetable
— Script to get CPU and Memory Info
SELECT
cpu_count AS [Number of Logical CPU]
,hyperthread_ratio
,cpu_count/hyperthread_ratio AS [Number of Physical CPU]
,physical_memory_kb/1048576 AS [Total Physical Memory IN GB]
FROM sys.dm_os_sys_info OPTION (RECOMPILE);
SELECT ‘GP Upgrade Duration SQL script written by David Morinello’ AS [Notice], @ScriptVersion as [Version]
/*
So, many years ago, I got bored and a little frustrated watching the GP upgrade progress. I had recently moved
from a 4 GP Company installation to a 32 Company site and practiced the upgrade several times to catch issues,
and to estimate completion time. This would have been around 2012 or so, I think.
If you have watched the GP upgrade appear to pause or get stuck during the upgrade, you have felt the pain.
So, I did what any geeky SQL guy would do and set about creating my own measurement tool. I reached out to the
community to better understand the phases of the process and came up with a basic script to capture steps and
durations.
Since then, I have added little refinements and fixes to get a more accurate progress snapshot. There are still
plenty of ideas, enhancements and fixes left to try as my time allows.
I posted early versions on this and other forums over the years, but I noticed my code got lost as the community
features were pared back from the old days. Back when we got daily or weekly notices on questions and there was
a regular place to post shared code.
Feel free to use this during your upgrade testing or production roll-out. Share with others.
I just ask that you keep the comments and my name in place.
*/–*********************************************************************
-
2 Replies
Log in to reply.
