This may not look like the best way to solve your problem,
but if you just want to solve it, it works:
If you have SQL2k try to stop the SQL, then rename the
Tempdb datafiles. They will be rebuilded when SQL Starts.

>-----Original Message-----
>I had the size of tempdb set to grow as big as it wanted
to.
>
>A job caused tempdb to consume all the available space
before it quit
>because it could not extend tempdb.
>
>Not wanting to take the server down I tried to shrink
tempdb which did not
>work.
>
>When I then took the server down and restarted it, tempdb
did not go back to
>it's default size it instead was at the very large size
it was before I
>restarted the server.
>
>I did some testing on an offline sever and found that if
you shrink tempdb
>it resets the default size of tempdb to the size at the
end of the shrink
>process. Enterprise manager will not let you set the size
of tempdb smaller
>than it currently is.
>
>Is this good behaviour?
>
>I would have prefered that shrink not reset the default
size of tempdb. If
>it should not work on tempdb it should say so.
>
>It seems that Enterprise Manager should allow one to
change the size of
>tempdb to a smaller size with the warning that it will
not take effect until
>restart.
>
>
>
>
>
>
>
>
>.
>

Report this post to a moderator

Yes they will be rebuilt at start but if you have run shrink db they will be
rebuilt with that great big size.

The only way I found to get tempdb smaller is to:
1. stop the server.
2. restart the server with -f (tempdb in memory)
3. change the size of tempdb (Enterprise manager thinks it;s 1mb now, got
to make it bigger than one for this to work).
4. stop the server.
5. start the server as normal.

I think the fact that shrinkdb when used with tempdb alters the size of
tempdb in the sysfiles table is a bug?

Tom


"Paulo Ferreira" <paulo.r.ferreira@pt.ibm.com> wrote in message
news:997a01c28ffb$057ce670$89f
82ecf@TK2MSFTNGXA01...
> This may not look like the best way to solve your problem,
> but if you just want to solve it, it works:
> If you have SQL2k try to stop the SQL, then rename the
> Tempdb datafiles. They will be rebuilded when SQL Starts.
>
> >-----Original Message-----
> >I had the size of tempdb set to grow as big as it wanted
> to.
> >
> >A job caused tempdb to consume all the available space
> before it quit
> >because it could not extend tempdb.
> >
> >Not wanting to take the server down I tried to shrink
> tempdb which did not
> >work.
> >
> >When I then took the server down and restarted it, tempdb
> did not go back to
> >it's default size it instead was at the very large size
> it was before I
> >restarted the server.
> >
> >I did some testing on an offline sever and found that if
> you shrink tempdb
> >it resets the default size of tempdb to the size at the
> end of the shrink
> >process. Enterprise manager will not let you set the size
> of tempdb smaller
> >than it currently is.
> >
> >Is this good behaviour?
> >
> >I would have prefered that shrink not reset the default
> size of tempdb. If
> >it should not work on tempdb it should say so.
> >
> >It seems that Enterprise Manager should allow one to
> change the size of
> >tempdb to a smaller size with the warning that it will
> not take effect until
> >restart.
> >
> >
> >
> >
> >
> >
> >
> >
> >.
> >

Report this post to a moderator