• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 868
  • Last Modified:

SQL Server Hotfix issue

Hello All,

We recently upgraded our SQL 2000 datbases to SQL 2005.  In researching 2005, I came across KB 958004 which addresses a filesize growth issue.  The essence is that a file set to grow in MB will actually grow by a massive (640, 1280...) percent.  I saw that it was part of Service Pack 2, so I, being a newbie to Microsoft hotfixes, thought it would be handled in SP3 which we installed.  Of course, the fix was in an update to SP2 after SP3.  

The problem was addressed in SP3 Cumulative Package 1.  I have installed SP3 Cumulative Package 7 expecting it to solve the problem.  It did not.  I performed the recommended steps after applying the fix with no luck.  I uninstalled and reinstalled the cum package and it still does not work.  

I know we could just use % growth settings, but I would prefer to have it fixed so we don't have to remember that issue when adding new files.

Has anyone else had a problem solving this filesize issue?

Do you have any recommendations on what my next steps are?

I am new to both EE (as a poster) and Microsoft hotfixes, so please ask for clarification as needed.

Thanks for the help,

  • 5
  • 4
1 Solution
Jim P.Commented:
Your best bet is to change your log file size to a MB  growth setting vs a percentage. The percentage growth is a self-fulfilling nightmare.

As far as log file sizes, make sure that you aren't in Full recovery model (default) unless you need to be.
Mark WillsTopic AdvisorCommented:
Well that only happens (in theory) when you restore a 2000 database to 2005.

It shouldnt happen  for "new" files (assume you mean database).

If you go in and change the growth to percent. Stop sql server, restart it and then change to megabytes it should start to behave.

You could also update your database to SQL2005 (compatability level) - should not really impact on your current use - everything in 2000 is handled in 2005. Might need to double check the way thrid party applications are connecting, but should be OK.

Make sure you do a full backup after making any such changes so if a restore is required you will have the correct settings.

Best thing to do is to accurately size your database (and log files) so it doesnt need to "autogrow" and definitely disable autoshrink.

have a read of : http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/Managing-Fragmentation-for-the-Accidental-DBA.html
and http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Managing-the-Transaction-Log-for-the-Accidental-DBA.html

for a couple of discussion about database sizing... (which reminds me - I need to write the database sizing article).
WvrLthrIT ManagerAuthor Commented:

Thanks for the reply.  The problem exists when the file is in MB setting not percentage setting.  I agree with you on not using the percentage setting in most situations.  We need to be in full recovery mode so that we can restore if a failure occurs.  We currently backup the transaction logs every half hour during the day to capture everything being entered in the system which would be completely lost in a failure.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

WvrLthrIT ManagerAuthor Commented:

Ok.  That makes sense that new files would not be a problem.  I still need a fix for the current files.  I followed your steps and here are the results.  The 1280's should be 10's as I set them to be 10 MB.  The other files were 1280 until I set them to 10% which is accurately reflected in these results.  

JDE_DV7334_Data                       1280      0
JDE_DV7334_log                       1280      0
JDE_DEVELOPMENT_data      10      1
JDE_DEVELOPMENT_log      10      1

When we did the upgrade we moved the compatibility levels on all the databases to 90.

I agree with you on having the files sized to not grow very often, that is our intention.  Is your recommendation to regularly look at the file sizes and manually add space to them by changing the filesize?  I would like the autogrow to be there as a tool to ensure the database cannot crash due to a space issue.

I actually read your article on Transaction Logs yesterday as I was researching this issue, nicely done.  I will read the fragmentation article this morning.

I think there are manual ways to handle filesize growth and I will take all the tips I can get. Mostly, I am disappointed with my first experience with a SQL Server Bug fix and am looking to see if this is a common situation where the stated fix does not work and I should not trust cumulative packages (remember, this fix was originally in cum 1 and I applied cum 7 so there should be plenty of time to "fix the fix").  Or, Is this a unique situation and I should be contacting Microsoft for an explanation?

Thanks again for your time,

Mark WillsTopic AdvisorCommented:
The issue is common enough for Microsoft to warrant a KB article. So, you are not alone on that front.

It should however have fixed the problem. Gnerally speaking the incremental fixes are good and work without too many questions. Occassionally we do get situations where it doesnt seem to have worked for whatever reason. I do think that is a rarity and not the common experience.

I have not had the problem, so cannot honestly say just how persistant the thing is. But would have no problems setting it and leaving it at 10% either. Depends a little bit on how big your files are at the moment, and how much space they actually consume, and how full that space is.

Autogrow should be a "safety net" in the off chance that the system does need that extra space. It will happen from time to time, but ideally is an exception, not a frequent event.

It probably takes observing a few cycles of a "period" - the definition of which may vary but essentially takes in a quantum of new activity combined with any archiving activty to get the net movement. The measurements should be at the start of the period, and then just prior to archiving and then just after archiving. Your file size needs to take into acount the largest period of growth without the archive, and the archive amount gives you that extra buffer zone. Of course, if there is no archive process then you will need to accommodate growth over the life of the database, and that will likely lead you into quite extraordinary numbers and not so practical. In that case, you look at a controllable space to allow growth over a period that you are prepared to manually reasses for the next "period" of activity (period then becomes a little more arbitrary, but suggest monthly to coincide with other traditional monthly activity often associated with JDE). It is a very crude rule but has served me very well.

When first converting a database to a higher compatability level, I normally run all the maintenance routines I can. Like index rebuilds, reorganise pages, update statistics, shrinking (once off if doing a restore), running a full backup, then run a restore. Finally, set up the real size, and set up the maintenance plans for full backups, log backups (if full recovery mode) and periodic index rebuilds, stats etc (especially if archiving is involved, and do it after archiving), I normally dont worry about reindexing unless I do not have a time frame / window of opportunity for a full rebuild.

Ideally the only time after that that a file size changes is because I want or need it to (for all individual files in a database). But that "ideally" does require a bit of homework in terms of what your business does (with regard the database) and the nature of activity or use of database (OLTP versus data warehouse for example).

Whenever Microsoft advertise a fix for a situation that you believe you have and the fix does not work, then I would have no qualms about talking to Microsoft. There can be a lot of different factors affecting a database, and the one thing you need to do is to remove the "mysteries" of managing that database, otherwise you will never feel in full control, and that element of doubt is not worth losing sleep over if it can be eliminated.

And of course we are here to help answer any questions whatsoever - if we can that is ;)  So if any of the above doesnt make sense or is not what you are after, then keep on asking - we are here to help :)
WvrLthrIT ManagerAuthor Commented:

Thank you for the insight.  I think your philosophy tracks well with how I want to do things.  Our primary Production database is set up pretty well and shouldn't need to autogrow anytime soon (we do some monthly archiving and monthly index optimizations).  I guess you are right in that I should just set the other databases to 10% and keep an eye on them.

Depending on if we have some free support available through our licensing, I may work with Microsoft on this, if for no other reason than to learn their process in case I really need help in the future.

I read your article on Fragmentation and found it helpful.   It made me look at the optimzation maintenance job on our production database.  If you have a minute, I would like your quick thought on this, even though it is off topic.  

We run the optimization job monthly.  We have it set to 'Reorganize data and index pages with original amount of free space' which is good.   There is also a setting for 'Remove unused space from database files' which is turned on.  Is this useful from a defrag point of view?  Or is it really just an autoshrink type function that should be turned off?  I don't believe it actually shrinks the file since the file is 340 GB (initial planned size) and has 97 GB free.

Thank you again for your advise on this case,

Mark WillsTopic AdvisorCommented:
OK, you are using Database Maintenance Plans as distinct from Maintenance Plans. Small difference but Database maintenance Plans are used in conjunction with SQL2000 databases - and the optimisation page you are referring to.

The remove unused space should be shrinking the files and returning it to the op sys. However there are added constraints such as amount to retain, minimum size and so forth.

The defrag is really the reorganise pages option.

Have a look at : http://technet.microsoft.com/en-us/library/ms188964(SQL.90).aspx

Then note on the left hand side the "Maintenance Plans Node (Object Explorer)" entry which is the one to use with 2005 databases, and it doesnt have that option unless you include a "shrink" task.

WvrLthrIT ManagerAuthor Commented:
Oh yeah, sorry about that.  I have just started looking at migrating our SQL 2000 plans and jobs to SQL 2005.  The important thing is that we are defragging with the reorganize option.

Thanks again, you have been exceedingly helpful.

WvrLthrIT ManagerAuthor Commented:
The solution went above and beyond what was asked in providing context and additional issues to consider.
Mark WillsTopic AdvisorCommented:
A pleasure, and very happy to have been of some help to you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now