>Why shouldn’t I shrink my Microsoft Dynamics GP databases?

February 13, 2011

>A client recently approached me with the question of whether they should shrink their Microsoft Dynamics GP databases to reclaim hard disk space, but instead of telling you what I think, I will demonstrate some of the issues arising from shrinking your databases:

Now to what I think…I have never been a big fan of shrinking databases to reclaim hard disk space — though, if you are running a dev environment where space is critical, then this may only be the one time. The problem arises from the way the shrink process occurs, and applies to DBCC SHRINKFILE, DBCC SHRINKDATABASE and the Auto Shrink setting in the database properties.

In summary, SQL Server goes to the end of a dabatabase file, picks up each individual page, then moves them to the first available empty space in the file. This process may reverse the order of your pages, turning perfectly defragmented indexes into perfectly fragmented ones.

So, let’s take a look with a test database in one of my client’s environments:

1. The first thing we will do is take a look at the stats on the GL00100 table by running the Microsoft SQL Server sys.dm_db_index_physical_stats function:

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons
-- Attribution-NonCommercial-ShareAlike 2.5 Generic license.
SELECT * FROM sys.dm_db_index_physical_stats

The following are the results from those stats:

Original database stats

 Now, in particular, I have highlighted the Average Fragmentation in Percent and the Average Fragment Size in Pages columns. Also, note that I have executed the function in ‘LIMITED’ mode, which combines information from all the columns that form part of each index. So, while these fragmentation levels would indicate low defragmentation rates — which is always desirable — it means there is still room for improvement. So, let’s see what happens after rebuilding the indexes on GL00100 for our test database, then rerunning the stats:

Stats after rebuilding indexes

As you can tell now, we have no fragmentation and our page size utilization went up – this is what we would expect after rebuilding indexes. So let’s see what happens when we run the shrink process on the database:


You can now tell that perfectly defragmented indexes now appear fragmented and even to a higher degree than what we started out with. These levels of fragmentation can cause serious performance issues in a production envrionment where database maintenance procedures are not properly planned and executed.

If you must reclaim hard disk space in your Microsoft Dynamics GP environment, please consult with your database administrator, but also keep in mind that storage is dirt cheap.

Until next post!

Mariano Gomez, MVP
IntellPartners, LLC


Microsoft Dynamics GP Database Maintenance Utility

July 7, 2009

How many times have you performed an upgrade just to realize with the first posting that some stored procedure is missing or did not — for some misterious reason — get created, yet the upgrade completed with no errors? This is especially true when upgrading from several versions aback, when the upgrade path is not too clear, or you have forgotten to apply that critical service pack that would avoided some of the missing objects issues you are now experiencing.

I recently came across a user who wanted to recreate all SmartList Builder objects because they suspected something was wrong with the installation, however, they did not want to have to use the backend to recreate all the stored procs used by SLB, because of the “fair of missing something or endup damaging something else“… you know what they say… the client is always right! (not always, but that’s the topic of another article).

After scratching my head for a few moments, the answer came in the form of the new Microsoft Dynamics GP Database Maintenance Utility, but first some history…

Before there was the SQL Maintenance utility, back in the old days, the former Great Plains Software provided reams of text files containing scripts to recreate any missing or damaged system and/or company database object. These scripts could be found on the installation CDs and could be accessed via the old SQL Server Query Analyzer tool. All you needed to do was to open one of script files, do a search for the desired stored procedure, copy and paste into Query Analyzer, and execute the script against the appropriate database. This sounded simple enough until system administrators (we will not call them users for fear of retaliation 🙂 ) began corrupting the script files and introducing their own code… It was funny, because Great Plains Support used to asked as part of their support script if you had modified X or Y stored procedure for the problem you were calling for. When Microsoft took over, came the MSIs and the script files were no longer provided. Now what? There comes SQL Maintenance, however, the set of utilities provided with SQL Maintenance did not allow you to recreate stored procedures, functions, or table triggers. These still had to be obtained from Microsoft. I guess, in the end they figured out it wasn’t worth the hazzle and delivered Database Maintenance Utility.

So, lets take a look.

To recreate database objects, you will need to:

1) Launch Database Maintenance Utility. Go to Start > Programs > Microsoft Dynamics > GP 10.0 > Database Maintenance.

Select your server and select an authentication method. Fortunately, Database Maintenance can use Windows Trusted Authentication. Click Next to continue.

2) Choose the company you are going to perform the updates on. My personal preference is to choose ALL databases if you suspect a damage on a stored procedure, function or trigger. Why? Chances are, the stored procedure may be damaged across the board.

Click Next to continue.

3) Select the product for which you will like to rebuild its objects. For this example, I will use SmartList Builder.

Click Next to continue.

4) Mark the objects you would like to recreate. You have the choice of Stored Procedures (includes UDFs too), table Triggers, and Views.

Note that for Tables and table Auto Stored procedures you must use the SQL Maintenance option available within Microsoft Dynamics GP. Click Next to continue.

5) Confirm your selection.

Click on Next to continue.

6) The Utility will run through a progress bar and will return a status of the operations when completed.

If recreating stored procedures, the Database Maintenance Utility will re-grant permissions to all database users in the DYNGRP role, so no need to run the GRANT.SQL utility script.

Hopefully, you use this powerful tool as a first option in recreating any missing or damaged database object, before you call Microsoft or open that support case.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company – The Clear Data process

June 25, 2009

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.

In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my ‘sa’ user and open the Clear Data window and try to do something there.

Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.

If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID’s SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVP

Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.

Until next post!

Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC

Developing for Dynamics GP Weekly Summary

January 17, 2009

After a long absence, my friend David Musgrave is slowly regaining his writing pulse. Please check out some of his cool articles this week, but first and foremost let him know what you think about them. I always encourage everyone to test and play with some of the code we provide in these articles. They are a good way to get acquainted with Microsoft Dynamics GP functionality. So let’s get on with the articles:

1. Microsoft Dynamics GP 10.0 Service Packs, Hotfixes and Payroll Compliance/Year End Updates May Damage the Modified Reports and Forms. After a few of these errors circulating on the newsgroups, it has been confirmed by Microsoft development team. The payroll year end update will in fact damage your modified forms and reports. I have a theory on this one: since the YE update is also inclusive of service pack 3, it will be necessary to take all the precautions layed out when applying a service pack, that is, export ALL your customizations to a package file BEFORE applying any patches. Read more here.

2. Microsoft Dynamics™ GP 10.0 Bootcamp (Australia). Now, if you ever wanted to go to a Dynamics GP training class, won’t it be a dream to do so in the land down under? Now, that’s what I call a Dynamics GP training with style. Read more here.

3. eOne.Dynamics.GP.ExcelBuilder.Engine.dll Exception. If you have received this error after applying Microsoft Dynamics GP 10 Service Pack 2 and above and you are currently using SmartList Builder and Excel Builder, make sure you take a look at this article and the fix by reading more here.

4. Hybrid – Adding Named Printers control to Reports using VBA. Wondering how to get Named Printers to work with unsupported reports? David answers this one with a cool VBA customization. Best of all, you can download sample code! To play with this code, click here.

5. WorldMaps Tracking Added. As if my uber friend wasn’t all that uber geek already, he adds a cool hits tracker on his blog with the cool WorldMaps beta product from Structure Too Big. I have to confess, I will be adding this one pretty soon to my blog! I find it fascinating to know where your readers are concentrated, which helps when developing content for the blog. For more info, click here.

6. Modifier – Reading and Writing Data with ADO Example. It does not matter how many of these we do, I always find Modifier with VBA fascinating and a very good alternative to Dexterity customizations when possible. Now, don’t get me wrong. I love Dexterity, but I also love the portability offered by VBA customizations too. To read more, click here.

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC

New article on MSDynamicsWorld: 5 Tips for a Smooth Upgrade to Microsoft Dynamics GP 10

January 15, 2009

“Avoid the temptation to take shortcuts”

For those of you who follow me on MSDynamicsWorld, after a small writing sabatical I am back with a new article on upgrading to Dynamics GP 10.0. My key recommendations: work from the FP1 DVD, test several times, make sure you know your upgrade path, take advantage of the latest Microsoft OS and database technology, but foremost, work with your partner!

Until next post!

Mariano Gomez, MIS, PMP
Maximum Global Business, LLC

What does SQL Maintenance really do?

November 22, 2008

This one comes straight off the Microsoft Dynamics GP newsgroup (even the title given to this article).

Business Situation

During the course of a test upgrade the user came across a situation where records in a Smartlist table were not upgrading at all. In his attempts to have the upgrade bypass the problem, the user deleted the records from the SmartList table in question and the upgrade was able to succeed.

Nonetheless, the user attempted to run a few of the built-in SQL Maintenance routines found under Microsoft Dynamics GP > Maintenance > SQL and came back empty handed. This series of routines did not correct the damaged records and did nothing in aiding the upgrade, which brings us to the question on the subject:

What does SQL Maintenance really do?

This question can be answered by looking at what SQL Maintenance does not do. SQL Maintenance does not take any actions on table records, except of course, when you drop a table :-).

The routines found under SQL Maintenance are designed to perform preventative database maintenance and each option can be explained as follows:


As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes

Another reason to force a stored procedure to recompile is to counteract, when necessary, the “parameter sniffing” behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.

When the Recompile option is selected in the SQL Maintenance window, GP forces the selected tables auto-stored procedures and triggers to recompile by executing the SQL Server sp_recompile statement.

Update Statistics

Update Statistics updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.

The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

  • If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.
  • If lots of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function from SQL Server.
Drop Table

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Drop table will not remove the associated table views or auto-stored procedures, hence, it is recommended to run the Drop Auto Procedure option in conjunction with this option. The Drop Table option executes SQL Server DROP TABLE statement.

Create Table

The Create Table option creates a Dynamics GP table (that has been dropped with the Drop Table option or dropped from SQL Server) with the structure defined in the DYNAMICS.DIC dictionary file. When this option is executed, GP will check for the table existence and drop if it already exists. In addition, all table auto procedures (zDP_) will be dropped and recreated and security granted to the DYNGRP role.

Drop Auto Procedure

This option will remove all selected tables auto stored procedures. The zDP_ procedures aid Dynamics GP in retrieving, saving, updating, and deleting records from a table.

Create Auto Procedure

This option will create all selected tables auto stored procedures. If the procedures already exist, they are dropped and recreated.

Other Resources

Developing for Dynamics GP – “What do the zDP auto-generated stored procedures do?“, by David Musgrave.

Developing for Dynamics GP – “What is Column desSPRkmhBBCreh?“, by David Musgrave.

Microsoft Developer’s Network (MSDN) – You can always search SQL Server Books Online for all topics outlined in this article.

Until next post!

Mariano Gomez, MVP, MCP, PMP
Maximum Global Business, LLC