What the heck is the DMU you might ask ? DMU stands for Dynamics Maintenance Utility and is provided out-of-the-box by Microsoft when you install the GP client (or at least it was until it was gone missing in the 2018 R2 image.. see more here)
In essence, this is what the GP administration guide says about DMU:
There are rare occasions when you’d have to rebuild some of the non-table SQL objects, as directed above, often times by Microsoft’s technical support. Other times it might be when you want to do some system migration from one GP default instance to a named instance (i.e. your system DB goes from DYNAMICS to DYN01 or DYN18 as an example).
Recently I was testing out such a scenarios to not move a GP instance to a new one, but actually merge two instances together. As such, some of the SQL objects in GP do carry the system DB name hard-coded in the core of the object (like views or stored procedures (SP)), and there you’ll have to run the DMU against your GP companies and your system DB to re-create those objects.
Testing on GP 2013 went as expected and completed without an error, at which point you get a nice (hopefully) result window that confirm the entire execution process :
However, as I tried to replicate the same process on a GP 2015 instance, the part that was supposed to cover the DYNAMICS system DB (or whatever yours is named) wasn’t doing anything.. it would just complete with no result to show:
A quick search didn’t reveal anything relevant on the net, but I stumbled across a Dynamics Community post where someone claimed that the DMU was gone missing in GP 2018R2 .. Since my problem was related to the DMU, I just added my remarks about my discovery to the post, not knowing if it was actually a bug or me that was doing something wrong. After all, the DMU isn’t something you’re going to use every day.
It didn’t took long to Derek Albaugh from the Microsoft support team to reply back and actually confirm that this was indeed a bug. The DMU started failing for the system DB with GP 2015 all the way up to 2018 R2. Currently the work-around would the following as listed below, until this gets fixed in an upcoming release of GP. It seems that it was already registered at Microsoft as a bug, but this has been remained unfixed for 2 major updates..
- 1. Log into DB Maintenance.
- 2. Select all company databases and the Dynamics database.
- 3. Select all products
- 4. Select all options
- 5. Stop at this window
- 6. Open SQL Server Management Studio and run the following command in new query: SP_WHO2
- 7. Look through the results and find your Dynamics/system database.
- 8. You will want to look at the SPID column and take note to the second SPID value
- 9. You will then run the following command in a new query, replacing the SPID number replacing 52 in this example: Kill 52
- 10. Then go back to DB Maintenance and click Next.
- 11. You will see it start to go through the progress and then see a results page with green check marks next to all products and databases.
As you can see, this time it works :
Noticed on how the version number disappeared from the window title after GP 2013.. (v12.0).. not a huge deal, but it would be nice to get it back, so it confirms which version of GP you’re actually working on…
Read the full Dynamics Community thread post here : https://community.dynamics.com/gp/f/32/t/305027
Until next post,
EDIT: if unsure about which SQL SPID process # to kill at step 9, use the SQL Activity Monitor and filter out all processes except for the DYNAMICS (or your GP system DB) name and then inspect the Code behind the running processes. The one to kill contains this script : if(IS_SRVROLEMEMBER (‘sysadmin’)) = 1 BEGIN select 1 END ELSE BEGIN Select 0 END