SQL Server 2005 Database sudden Growth

tim6661234
tim6661234 Member Posts: 12
edited 2009-01-31 in SQL Performance
Happy New Year all!

Hope some can shed some light on this problem.

I've been investigating a NAV 4.02 SQL server system (2005). As a first pass I altered the Maintance Plan.

I added an Index defrag nightly task (and update stats), also rebuild the index with 10% free space.

The database has since gone from about 30Gb to over 200Gb!

I did ready of a bug with some SQL Server 2005 rebuild plans where it uses the free space parameter as Fill Factor. This made sense for why the increase happened but since I've reverted it back and it's not reducing the size of the database.

I just can't work out how I can reduce the size again (it's not backing up due to lack of disk space).

Thanks in advance.

Tim

Answers

  • stryk
    stryk Member Posts: 645
    Hi Tim,

    yes, there was such a bug in SQL 2005 RTM Build 1399. With NAV it is highly recommended to use at least SQL Server 2005 SP2 Update 4 (Build 3200) or higher - currently SP3 is already available.

    But once you have reset the Fill-Factors to a reasonable value this would not automatically shrink the physical database files! After rebuilding the indexes with the correct FF you might consider to shrink the database; e.g. in "Management Studio", right-click on the database and select "Shrink" and "Database" or "File" (here you could also see the amount of space used within the physical files).

    As you mentioned you cannot backup the database since it grew that much, I guess the fillfactors are still wrong! You could check e.g. with 
    select * from sysindexes
    What's the value of "OrigFillFactor"? Should be 0 (zero = default) or 90 (if you applied correctly)

    Last but not least - as this is an frequently discussed issue here -: make sure that it is not just the "Transaction Log" file which is the one causing the super-size! If your Recovery Model is not SIMPLE and if you don't run Backups this TLog file will grow and grow and grow ...

    Regards,
    Jörg
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • tim6661234
    tim6661234 Member Posts: 12
    Thanks for your prompt reply.

    The SQL version is Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    I'm a little confused by the builds although I have asked the client to put the latest version on. Why does it report 1399 then Build 3790? Does this mean that the tools have been updated but not the database engine?

    The select * from sysindexes returned mixed results. Some were 0 some 10. I had rebuild the indexes specifying to use the default (currently set up as 0) but this has not reset them. So I have set a job for tonight set at 90% as a starting point.


    As you say the database size will not shrink but I still have 120GB of of data so need to get this down first.

    Thanks for the advice

    PS Great book "NAV/SQL Performance Field Guide" once the database is back down to a manageable size I can start working out why the performance is so poor.
  • stryk
    stryk Member Posts: 645
    tim6661234 wrote:
    The SQL version is Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
    I'm a little confused by the builds although I have asked the client to put the latest version on. Why does it report 1399 then Build 3790? Does this mean that the tools have been updated but not the database engine?
    Yeah could be, sometimes an error occurs during an update (and nobody really reads the summary report :wink: )
    tim6661234 wrote:
    The select * from sysindexes returned mixed results. Some were 0 some 10. I had rebuild the indexes specifying to use the default (currently set up as 0) but this has not reset them. So I have set a job for tonight set at 90% as a starting point.
    That was my guess, pity. Thus if you run that tonight with build 1399 (and only with that buggy one!) you probably have to set "Free Space" = 90% which should result in this case to a Fill-Factor of 90% (you could check the TSQL generated from the MP task)
    tim6661234 wrote:
    PS Great book "NAV/SQL Performance Field Guide" once the database is back down to a manageable size I can start working out why the performance is so poor.
    Great, thanks a lot for your praise. Hope it will help you! And feel free to come up with any comments, suggestions or maybe errata - and if you have questions: "see" you online then!
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • tim6661234
    tim6661234 Member Posts: 12
    As Jörg said this is a bug in that SQL build. The client loaded service pack 3 but we rebuilt the indexes with 90% Fillfactor. After deteting a custom audit table and moving to 5.0 SP1 (removing the SIFT tables) the database now sits at 11 GB with plenty of room.

    Performance is also imported with a mixture of the database size (more in the cache) and the index views rather than SIFT tables. I also followed Jörg book to change some of the SQL options to improve thing.

    Thanks for you help on this. :D

Categories