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
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.
ReplyDelete--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
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!
DeleteThank you!
Hi,
ReplyDeleteJust 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
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.
DeleteI'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)
This comment has been removed by the author.
ReplyDeleteUSE DYNAMICS --change to GP system database if not DYNAMICS
ReplyDeleteDECLARE @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
I ran out of space to add this verbiage to the above post w/script, but wanted to add the following:
DeleteThis 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.
@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.
DeleteThis comment has been removed by a blog administrator.
ReplyDeleteRespect and I have a super proposal: How Much Budget For House Renovation updating exterior of home
ReplyDelete