Autoshrink yes, no, may be?

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.

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.

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.

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

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

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.

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).

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

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.

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">

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

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

<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

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

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

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

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.

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

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

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

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 />