SQL Server Performance

Autoshrink yes, no, may be?

Discussion in 'Contribute Your Performance and Clustering Tips' started by Luis Martin, May 9, 2004.

  1. Luis Martin Moderator

    I would like to open a debate about this. If fact Big Guru[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] recommend to set on.<br />I'm not quite shure about this, may be because I don't know when autoshrink run.<br />Reading BOL I can't find when, anytime?, on low activity?<br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br />
  2. Raulie New Member

    By default this option is turned off in all editions of SQL Server except Personal Edition. I kept this option turned off on my databases and just schedule the shrinks myself. I dont think SQL Server has a predetermined time when it Shrinks a database BOL Shrinking a Database: shrinking occurs when a significant amount of free space is available in the database. However, if the percentage of free space to be removed cannot be configured, as much free space as possible is removed.
  3. Luis Martin Moderator

  4. satya Moderator

    On the production systems I would suggest and (my experience) suggests to DISABLE AUTO SHRINK option on the databases. On many instances I've performance degradation and database unavailable during this process which sometimes clashes with DBCC process which were scheduled during the slow traffic hours on databases.

    Even this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;Q296386 suggests not to use.

    NO - to auto shrink option on production databases.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. If your database is making lots of autoshrink ops you will get ntfs fragmentation - back in the days of sql 6 devices had to be predefined with adequate space, I still work that way in order to minimise ntfs fragmentation - I've never been able to conclusively test the effects of a contiguous mdf file vs a fragmented one, but logic dictates it must be better. I'd say autoshrink on the mdf at your own risk - ldf's maybe.
  6. satya Moderator

    AUTO SHRINK option is set to database, so its applicable to both files .MDF & .LDF.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. gaurav_bindlish New Member

    I agree with Satya on this.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  8. Karen New Member

    Everything makes sense here. Am I mistaken, or was there a reason to use autoshrink in SQL7? I have a third party app here that we started running in sql7 and then moved to sql2k. The database and log file sizes seemed to be uncontrollable, and so, way back when, I turned autoshrink on. I thought there was another reason to have it on, like you can't shrink the log file manually unless this is on, or something like that. That was a long time ago, and I think I will try turning it off tomorrow to see how the files grow in a day. I do run a shrink process every night, I have to, since there have been mornings when things have grown so much that we could not run the app. The app people have never addressed this since I can "handle" it in overnight shrink jobs.

    Thanks always for the helpful ideas!


    SQLGoddess
    Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  9. tdong New Member

    Like saya says too much time consuming for auto shrink on production. Beside shrinking is to save space. if space is not a problem don't shrink the database. Well the database I am managing is always growing we don't have the delete option on hehehe.(when come to delete we only mark it as deleted) therefore no point of shrink it for me

    May the best cheaters win
  10. Luis Martin Moderator

    Thanks guys, agree with you.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

  11. gaurav_bindlish New Member

    Karen the probelm was evident in 6.5 and I guess 7.0 days but as far as I beleive, it was fixed in SQL 2K.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  12. Karen New Member

    Thanks for all the input. I am putting it to the test today. I will check the results in the morning (of course I will be monitoring it today).

  13. Karen New Member

    So far everything is looking great with autoshrink turned off. Now I will watch to see when the weekly and monthly jobs run to make sure there are no other issues.

    Karen
  14. gaurav_bindlish New Member

    We appreciate your spirit of posting feedback....

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  15. satya Moderator

    I bet this post performance effect of AUTOSHRINK = OFF will be better than ever.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  16. Luis Martin Moderator

    From time to time I look (I gess you too) for old post when I remember some topic what was development before. May we have to move this post to other place, or new one, and put there some tips we agree.
    What you think?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  17. satya Moderator

    I agree if you feel this thread can be transfered to the TIps forum rather than the general one.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  18. gaurav_bindlish New Member

    Actually I was thinking on the same lines.<br /><br />I have been part of the community for quite some time and I have observed that lot of time the first few ansers to some particular type os the problems stay the same. Of course if those tricks do not work, there are always further investigations. SO won't it be nice if all these general answers can be kept in a sincle place in the forum itself. I know most of the answers are in the tips section but form what I have seen, most of us don't look in the tips section before posting. <br /><br />Hey, who wants to work if somebody else is ready to work for him [<img src='/community/emoticons/emotion-5.gif' alt=';)' />].<br /><br />Any thoughts?<br /><br />Gaurav<br /><i>Moderator<br /><font size="1">Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard</i></font id="size1"><br /><font size="1">The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.</font id="size1">
  19. Luis Martin Moderator

    That's exactly what I mean, but in good English.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  20. satya Moderator

    We are aware this forum section is intended to post any tips and tricks but not threads related to an individual problem.
    So in this context I would agree and say no further comments.

    Good job folks.... [8D]

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  21. Karen New Member

    Okay, so now that the weekend jobs ran, the database has increased in size by half a mb. While that's not much, it can easily start to add up. I will review all the weekend jobs to see if there is some way to reorder or fine-tune them to try to avoid unnecessary growth. Then again, it could be that a few of the files in the filegroup were at the point where they needed to grow, so there is not reason to panic!

    I have to thank all in this forum, as I have found some great tips here.



    Token SQL Goddess
    Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  22. Karen New Member

    Sorry, that was half a gig.... definitely will add up...

    Token SQL Goddess
    Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  23. satya Moderator

    See you already began to see the sunshine... I hope this will last.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  24. Karen New Member

    <img src='/community/emoticons/emotion-1.gif' alt=':)' /> My life is in sunshine regardless of the weather... my choice. I love this kind of thing because I get to do some investigating into what happened. Plus it is generally always a great learning experience!<br /><br />It appears as though most of the files that increased in size did so because they needed to. Only one filegroup increased in size without good "reason." This group holds a set of indexes for one of the major tables in the database, and I am guessing that it grew during the weekend reindex job. One of the index files more than doubled in size, with half of it's space available. This may not be a big deal, except that the file is 854 mb now. I will need to manually shrink this file.<br /><br />The only changes I made last week -- I turned off auto-shrink and I rearranged some of the jobs to help out our back up processes. I've reviewed the job order, and it should not have affected the sizing. I have had tight control over growth in this database for about a year, and this change was a surprise I had not seen happen before. If I kept this set up the way the software vendor left it, we would have run out of disk space over a year ago.<br /><br />Today it looks like more growth occurred overnight - more to investigate! <br /><br />If anyone has any ideas, I would be more than happy to hear them.<br /><br />Thanks all!<br /><br /><br />Token SQL Goddess <br />Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  25. satya Moderator

    What kind of DB maintenance plans & DBCC checks set on this database?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  26. Karen New Member

    Sample listing of jobs run against the database in question:

    Reindex the major tables (dbcc reindex fill factor 80)
    Shrink Database (0,truncateonly)
    Update stats on major tables (fullscan)
    Differential Backup
    Optimizations
    Transaction log backup
    Backup database
    Truncate log

    These represent the maintenance jobs that I run against the database. There are other misc table update jobs, and a huge third party table update that runs daily. Most of what I do is keep the sizes in check daily because the third party update will increase the transaction log size to huge proportions.

    Thansk!!




    Token SQL Goddess
    Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  27. satya Moderator

    May be this third party update explains the increase of file size above.
    In general REINDEX, update stats & optimization will have optimum share of increasing file size increase when they're scheduled.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  28. Karen New Member

    Thanks, there was no third party update since last summer, sorry for any confusion. The database is for a third party product, it is not a database developed in-house. The program that uses the database is a critical application in our business.

    All of the jobs I run to keep control of this database. The growth that I am seeing is no reason to panic yet, but I do need to get control of it before it becomes an issue. To do that, I would prefer understanding why it is happening to fix it, rather than mindlessly schedule another job as a quick fix.

    Thanks,
    Karen



    Token SQL Goddess
    Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  29. Karen New Member

    Oh... Duh.. sorry. If the third party program increased the size of the file, then I have to ask why I had not seen it happen over the last 2 years. It has run 5 days a week for 2 years. A year ago I had to really batten down on uncontrolled growth, especially in the log file. I also tried to get reindexing, recompile and stat jobs scheduled to try to keep this database healthy.

    I will keep watching the db to see what happens & what might be triggering the surprise growth.

    Thanks for all ideas and suggestions!
    Karen

    Token SQL Goddess
    Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  30. Argyle New Member

    I would not keep shrinking the files if the reindex procedure needs the space. This will cause file fragmentation of the database files. Better to create them big enough from the beginning and leave them there.
  31. satya Moderator

    True, if the physical space is enough to tackle the database size then better run shrink job whenever the database size is high compared.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  32. Karen New Member

    Good advice, thanks. These were issues to be tackled this year, now that I've had the db under control for a while. I was planning on reevaluating all jobs, because I feel some of them were a "quick fix" approach, and should not be there in the long term. Since my job duties are not solely DBA related, first things first... Besides my programming and support duties, I have 5 SQL servers, 2 of which need my attention and a data warehouse project that I need to prepare for. There's never enough time to do everything that I'd like to accomplish, and, yet, still have a life! <img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />Thanks!!<br />Karen<br /><br /><br /><br />Token SQL Goddess <br />Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  33. Karen New Member

    Okay, question... How does you calculate how large a file should be? Obviously you can't allow files to grow without some control, else the transaction log would end up using all space. I have no problem increasing the size of files, but I prefer making informed decisions, instead of just winging it...

    Thanks!!
    Karen

    Token SQL Goddess
    Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  34. satya Moderator

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4194 thread about calculating data file size.

    To calculate size of table (estimated) refer to books online for Size of Table topic.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  35. rhoffpauir New Member

    I think you have a couple of different problems going on here causing rapid drive space consumption. The first is the 'fill factor 80' on the db reindex job. When this job runs it will calculate how much space is actually needed and then reserve enough space so that only 80% will be occupied. I know this may sound wierd to some when I say this but it adds 25% (yes 25 percent) of the required space on top (20/80=25%, 80/(80+20)=80% the fill factor). So, you are having to shrink the database afterwards to get it back to a controllable size. It would be easier to just raise the fill factor in the first place to 95 or 98 so it will reserve less empty space for future growth.

    Another factor to consider relating to rapid drive consumption is also caused by db reindex. The db reindex job can create a lot of transaction log activity if the database is in full recovery mode (the default). We had similar problems where our 60GB database would fill up a 33GB transaction log partition every time we ran the db reindex job. We had to create a script to put the database in bulk-logged recovery mode before running the db reindex then put it back to full recovery mode afterwards. (Don't forget to adjust your fill factor sufficiently in this script as mentioned above).

    I hope you find this information helpful,
    Ray



    quote:Originally posted by Karen

    Sample listing of jobs run against the database in question:

    Reindex the major tables (dbcc reindex fill factor 80)
    Shrink Database (0,truncateonly)
    Update stats on major tables (fullscan)
    Differential Backup
    Optimizations
    Transaction log backup
    Backup database
    Truncate log

    These represent the maintenance jobs that I run against the database. There are other misc table update jobs, and a huge third party table update that runs daily. Most of what I do is keep the sizes in check daily because the third party update will increase the transaction log size to huge proportions.

    Thansk!!




    Token SQL Goddess
    Life is one fool thing after another whereas love is two fool things after each other.— Oscar Wilde
  36. Karen New Member

    Thank you all for your input! Things have been crazy around here lately with non-sql issues and I have not had a chance to check in. I will be checking all suggestion in the near future, when things slow down! <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Token SQL Goddess<br />

Share This Page