Sunday, February 13, 2011

Why shouldn't I shrink my Microsoft Dynamics GP databases?

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
    (DB_ID(N'TWO'), OBJECT_ID(N'dbo.GL00100'), NULL, NULL , 'LIMITED');

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:

Stats after DBCC SHRINKDATABASE
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!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

4 comments:

Anonymous said...

Your blog has all sorts of broken links which is the examples that you give references to. You might want to look into it.

Anonymous said...

Although your theroy is correct, there is nothing wrong with a shrink database command after you run your backups. If it creates fragmentation then write the simple defragmentation code to fix the indexes. The reality is the log file will continue to grow in size until it is shrunk, taking up vital space on a server possibly causing it to ultimately fail.

Mariano Gomez said...

@Anonymous:
I take great pride in my blog and its content. Could you please point out the broken links specifically? I have tried clicking on all 3 pictures and have not found any issues with them. If you are referring to a diffent article, could you please point out the specific article?

MG.-

Mariano Gomez said...

@Anonymous:
I am not sure I agree with you, especially when Paul Randall, the lead developer responsible for the SQL Server storage engine says it's bad. Shrinking a database should only be considered when large amounts of data have been removed from the company database, which is rarely the case with GP databases anyways. If your log files are expanding out of control, maybe it's time to focus your attention on preventing this from happen by focusing on your backup strategy and your auto-growth settings.

MG.-