Auto Shrink - pros & cons

Hi all,
Can anyone give me some info/feedback on any experiences
with the autoshrink feature on a Db.
We need to exercise more control on the Db's &
environments we have and it would seem that most of our
Db's ( & Log files ) are only using a fraction of their
related disk files, so we want to claim back the space for
other projects, but we are wary of 'auto' features on any
product !!!
Thanks in advance.
jHi Keith,
Thanks for the info, i was opposed to setting
the 'autoshrink' feature as i had read that it was very
processor intensive when it kicked in and would obviously
adversly affect performance.
So your suggestion is that on 'production' db's it's best
to manually reclaim space but to let the Db's grow
automatically?, (which sounds a whole lot safer to me).
Is there a large overheard to setting your Db to grow
automatically at say 1 to 5%,(i suppose it depends on the
volume of data that is being inserted into the Db & the
size of the Db to begin with ?).
Thanks in advance & for your previous reply.
j
>--Original Message--
>I understand your concern with "auto" features.
>Feel free to read up on some of the following commands
within Books Online (within the SQL Server program group):
>dbcc shrinkfile
>dbcc shrinkdb
>Most times you would not want to shrink space -- since
the database may need to grow again to store additional
data -- but if you need the space for other projects, I
would start with the commands that I listed.
>--
>Keith, SQL Server MVP
>"Jim A" <jim.armsworth@.lombard.lu> wrote in message
news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
>> Hi all,
>> Can anyone give me some info/feedback on any
experiences
>> with the autoshrink feature on a Db.
>> We need to exercise more control on the Db's &
>> environments we have and it would seem that most of our
>> Db's ( & Log files ) are only using a fraction of their
>> related disk files, so we want to claim back the space
for
>> other projects, but we are wary of 'auto' features on
any
>> product !!!
>> Thanks in advance.
>> j
>.
>|||If you need to reclaim space on a production box, yes, perform the steps =manually. However, I do not feel that it is necessary to attempt to =reclaim space within a production server because the database will most =likely need this space again -- and it will have to grow to reclaim the =space.
However, if you have lots of unused space within your databases and you =need to reclaim disk space for other databases/projects you might want =to look into reclaiming disk space. You might also be able to add disks =to the server, but this would have a cost associated with it.
Regarding the overhead question, there is some overhead involved when =SQL Server grabs more disk space during its autogrow. This overhead is =not any more than you would experience if you performed the growth =yourself. The only advantage of doing it yourself is that you can =schedule it for off hours. The negative is that you might run out of =room within the database during the production day if you forget to =monitor the available free space. -- Keith, SQL Server MVP
"Jim A" <jim.armsworth@.lombard.lu> wrote in message =news:094101c34a18$be49e450$a401280a@.phx.gbl...
> Hi Keith,
> > Thanks for the info, i was opposed to setting > the 'autoshrink' feature as i had read that it was very > processor intensive when it kicked in and would obviously > adversly affect performance. > > So your suggestion is that on 'production' db's it's best > to manually reclaim space but to let the Db's grow > automatically?, (which sounds a whole lot safer to me).
> > Is there a large overheard to setting your Db to grow > automatically at say 1 to 5%,(i suppose it depends on the > volume of data that is being inserted into the Db & the > size of the Db to begin with ?).
> > Thanks in advance & for your previous reply.
> > j
> > > >--Original Message--
> >I understand your concern with "auto" features. > >Feel free to read up on some of the following commands > within Books Online (within the SQL Server program group):
> >dbcc shrinkfile
> >dbcc shrinkdb
> >
> >Most times you would not want to shrink space -- since > the database may need to grow again to store additional > data -- but if you need the space for other projects, I > would start with the commands that I listed.
> >
> >-- > >Keith, SQL Server MVP
> > > >"Jim A" <jim.armsworth@.lombard.lu> wrote in message > news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
> >> Hi all,
> >> > >> Can anyone give me some info/feedback on any > experiences > >> with the autoshrink feature on a Db. > >> > >> We need to exercise more control on the Db's & > >> environments we have and it would seem that most of our > >> Db's ( & Log files ) are only using a fraction of their > >> related disk files, so we want to claim back the space > for > >> other projects, but we are wary of 'auto' features on > any > >> product !!!
> >> > >> Thanks in advance.
> >> > >> j
> >.
> >|||Hi Jim,
I think the following link would be of some interest to you. It does a good
job of explaining how auto shrink works and what to expect if you use this
feature.
http://visualkb/express/viewer/?ID=SOX010828700125&title=How+auto+shrink+works+(autoshrink)
Arvind Ranasaria
SQL Server Development
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Jim A" <jim.armsworth@.lombard.lu> wrote in message
news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
> Hi all,
> Can anyone give me some info/feedback on any experiences
> with the autoshrink feature on a Db.
> We need to exercise more control on the Db's &
> environments we have and it would seem that most of our
> Db's ( & Log files ) are only using a fraction of their
> related disk files, so we want to claim back the space for
> other projects, but we are wary of 'auto' features on any
> product !!!
> Thanks in advance.
> j|||Arvind,
Is that an internal viewer? The link does not work for me.
--
Andrew J. Kelly
SQL Server MVP
"SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in message
news:O6ldvFjSDHA.3188@.tk2msftngp13.phx.gbl...
> Hi Jim,
> I think the following link would be of some interest to you. It does a
good
> job of explaining how auto shrink works and what to expect if you use this
> feature.
>
http://visualkb/express/viewer/?ID=SOX010828700125&title=How+auto+shrink+works+(autoshrink)
> Arvind Ranasaria
> SQL Server Development
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "Jim A" <jim.armsworth@.lombard.lu> wrote in message
> news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
> > Hi all,
> >
> > Can anyone give me some info/feedback on any experiences
> > with the autoshrink feature on a Db.
> >
> > We need to exercise more control on the Db's &
> > environments we have and it would seem that most of our
> > Db's ( & Log files ) are only using a fraction of their
> > related disk files, so we want to claim back the space for
> > other projects, but we are wary of 'auto' features on any
> > product !!!
> >
> > Thanks in advance.
> >
> > j
>|||Hi Andrew,
Sorry for the late response. Last couple of days had been hectic. Here is a
copy of the article that I referred to.
**Problem** SQL Svr Enterprise Edtn 2000 [2000 -W_2392 ]
ID: SOX010828700125 CRT: Aug 28 2001 MOD: Aug 28 2001 STS:Customer Verified
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
<>
*** Problem Description ***
This is a source code analysis on how auto shrink (auto_shrink, autoshrink)
works
on SQL Server 2000.
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
<>
*** Resolution *** Aug 28 2001 3:27PM jackli
Here is my source code research, testing with profiler findings in order to
answer
customer's question in log ( *** Log # 39).
Here is how auto shrink works:
1) When SQL Server starts, it Starts StartGlobalTaskProc which starts a
Timer
Object which set intervals of TaskAutoShrink.
2) Then we set auto shrink task to kick within 5 mins. (const
SHORT_SHRINK_PERIOD =5*60; // seconds)
3) All sebequent auto shrink will be 30 min (const SHRINK_PERIOD = 30*60;).
The
Timer is only resposible to queue the work item.
But there are two very important findings that I will explain right after
this.
There are 3 factors that may affect the effectiveness of shrink
1) DB not set to auto shrink. We simply do not do that.
2) We don't do shrinking on these databases with these conditions either
DBT_RDONLY | DBT_OFFLINE | DBT_SUSPECT | DBT_DETACHED | DBT_USE_NOTREC |
DBT_SHUTDOWN)
4) We set up a timer to queue the ShrinkTask for the database.
Two very important facts:
1) We take round robin to make an attempt to shrink a database which has
auto
shrink set on. Basically, we are make a sweep on database for 30 min
intervals.
Example: Say if pubs (5), northwind (6) and testdb (7) are set to auto
shrink, it
will take 1 and half hours before it will try to shrink pubs again. As you
can see
it will have impact on server with large number of databases
Round robin always starts with master during startup even though you can't
really
set auto shrink on master.
2) Profiler trace will only capture when an shrink is actually taken place
(not
only attempting). Because profiler generate Datbase shrinking events in the
FileMgr::ShrinkFile code. The fact that Timer sets the shrink the db doesn't
mean
it will be successfully run.
I found out a trace flag 662. This trace flag 662 will log an entry when
shrinking is attempted meaning scheduled work triggered. It will log entry
of
"Autoshrink database Dbid <db_id>". But it will log ghost message very
frequently.
It will fill out error log with theses messages
2001-08-28 16:10:41.37 spid8 Suspending task manager. Currently queued: 1
2001-08-28 16:10:51.37 spid8 Continuing ghost cleanup processing. System
idle.
Currentlly queued: 1
every 30 min you will get this in errorlog:
2001-08-28 16:11:01.39 spid11 Autoshrink database Dbid 12
I used this to do a test for 2 hours and found out the output to support the
above
claim. (see more in the details).
Now, customer's 128 databases are by design behavior. It means it will take
at
least 64 hours before we even attempt to shrink last database if every db is
set to
auto shrink.
Workaround would be:
1) use dbcc shrinkdatabase scheduled
2) He can turn off auto shrink on all databases that are not necessary. This
will
short the cycle.
3) You can use this script detect how many db are like that on customer's
server.
select case when status & 4194304 = 0 then 'No' else 'yes' end as 'auto
shrink', *
from sysdatabases order by dbid
I put this into an SO in case some one or myself needs the analysis. I will
contact CPR to see if a KB is necessary.
</TL:Summary>
<TL:ACTION_PLAN>
</TL:ACTION_PLAN>
<TL:DetailedNotes>
note: you don't have to read this part if you don't want to:
StartGlobalTaskProc
<<\\cprsrcwa\sql\sql80.rtm\ntdbms\storeng\dfs\access\access.c>>
class TaskAutoShrink : public TaskReqPkt
<<\\cprsrcwa\sql\sql80.RTM\ntdbms\ntinc\dbcc.h>>
class TaskAutoShrink : public TaskReqPkt
<<\\cprsrcwa\sql\sql80.RTM\starfter\sqltrace\comnevnt\resource\events.rc>>
DATAFILE_AUTOSHRINK_EVENT_CLASS
BOOL
FileMgr::ShrinkFile
TaskAutoShrink::ProcessTskPkt ()
Trace flag 662 (ACCESS,TRCFLG_ACCESS_GHOSTPAGE_REMOVED)
TaskAutoShrink::SetNextDb gets next elligible db for shrinking
Call tree
Sqlservr_main
StartUp::StartDBMSThread
StartGlobalTaskProc
TimerTask
CTimerTask::Execute (submits autoshrink task).
TaskReqPkt::Submit
</TL:DetailedNotes> </TLREVIEW>
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
<>
*** Resolution *** Mar 15 2002 2:39PM JPilov
--In SQL Server 7.0 there does not seem to be an interval based on the code
research. We seem to simply run through the databases one at a time and do
the
autoshrink.
<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
<>
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
Arvind.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uwsMB8jSDHA.3192@.tk2msftngp13.phx.gbl...
> Arvind,
> Is that an internal viewer? The link does not work for me.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "SQL Server Development Team [MSFT]" <sqldev@.microsoft.com> wrote in
message
> news:O6ldvFjSDHA.3188@.tk2msftngp13.phx.gbl...
> > Hi Jim,
> >
> > I think the following link would be of some interest to you. It does a
> good
> > job of explaining how auto shrink works and what to expect if you use
this
> > feature.
> >
> >
>
http://visualkb/express/viewer/?ID=SOX010828700125&title=How+auto+shrink+works+(autoshrink)
> >
> > Arvind Ranasaria
> >
> > SQL Server Development
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > Use of included script samples are subject to the terms specified at
> > http://www.microsoft.com/info/cpyright.htm.
> >
> >
> >
> > "Jim A" <jim.armsworth@.lombard.lu> wrote in message
> > news:093e01c34a0a$c5747d20$a101280a@.phx.gbl...
> > > Hi all,
> > >
> > > Can anyone give me some info/feedback on any experiences
> > > with the autoshrink feature on a Db.
> > >
> > > We need to exercise more control on the Db's &
> > > environments we have and it would seem that most of our
> > > Db's ( & Log files ) are only using a fraction of their
> > > related disk files, so we want to claim back the space for
> > > other projects, but we are wary of 'auto' features on any
> > > product !!!
> > >
> > > Thanks in advance.
> > >
> > > j
> >
> >
>