Resizing TempDB From a Trigger

I understand that a SQL Server can slow down if TempDB runs out of space and
must be resized. So, towards keeping TempDB "big enough" I'm wondering if it
would be a great idea to do the following:
1. Create a stored procedure that checks the size of TempDB and causes it to
increase in size if necessary
2. Call that stored procedure from a trigger (Before Insert)
Generally speaking, is this a reasonable course of action?Egads, the subject alone is giving me shivers. {shudder}

> 1. Create a stored procedure that checks the size of TempDB and causes it
> to increase in size if necessary
I'd be curious to see the algorithm you expect to perform to determine if
the not-yet-committed transaction is going to require more space in tempdb.

> 2. Call that stored procedure from a trigger (Before Insert)
How is this supposed to speed up your insert?

> Generally speaking, is this a reasonable course of action?
No, how about specifying a size for tempdb that is large enough to account
for this kind of activity?
A|||RE:
<< How is this supposed to speed up your insert? >>
It's not supposed to speed up the insert. The idea is that if TempDB is too
small, then *all* operations on the server would slow down while TempDB is
expanding (e.g., Auto growth by 10%). So the point would be to increase the
size, on demand, at the earliest time we know TempDB is running low on
available space.
-J
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OPjfVFWkGHA.976@.TK2MSFTNGP02.phx.gbl...
> Egads, the subject alone is giving me shivers. {shudder}
>
> I'd be curious to see the algorithm you expect to perform to determine if
> the not-yet-committed transaction is going to require more space in
> tempdb.
>
> How is this supposed to speed up your insert?
>
> No, how about specifying a size for tempdb that is large enough to account
> for this kind of activity?
> A
>|||> It's not supposed to speed up the insert. The idea is that if TempDB is
> too small, then *all* operations on the server would slow down while
> TempDB is expanding (e.g., Auto growth by 10%). So the point would be to
> increase the size, on demand, at the earliest time we know TempDB is
> running low on available space.
I still think it would be better to just allocate enough space in the first
place, and / or reduce the operations that demand so much from tempdb,
rather than increasing the size on demand.
Whether your insert implicitly causes tempdb to grow, or whether you force
tempdb to grow because of the insert, the impact on the server is the same.
A|||Jeff,
just a thought.

> 1. Create a stored procedure that checks the size of TempDB and causes it
to
> increase in size if necessary
Isnt this what auto groth option does but only when it is required to do it.
If you think your server needs bigger tempdb why not provide it at the
begining itself and not worry about it.
abhishek
"Jeff" wrote:

> I understand that a SQL Server can slow down if TempDB runs out of space a
nd
> must be resized. So, towards keeping TempDB "big enough" I'm wondering if
it
> would be a great idea to do the following:
> 1. Create a stored procedure that checks the size of TempDB and causes it
to
> increase in size if necessary
> 2. Call that stored procedure from a trigger (Before Insert)
> Generally speaking, is this a reasonable course of action?
>
>|||Jeff wrote:
> I understand that a SQL Server can slow down if TempDB runs out of space a
nd
> must be resized. So, towards keeping TempDB "big enough" I'm wondering if
it
> would be a great idea to do the following:
> 1. Create a stored procedure that checks the size of TempDB and causes it
to
> increase in size if necessary
> 2. Call that stored procedure from a trigger (Before Insert)
> Generally speaking, is this a reasonable course of action?
>
How is this different than letting SQL grow the database for you?
It would be FAR better, in my opinion, to determine an appropriate
TEMPDB size and stick to that, no growing or shrinking. Control the
size of your insert/import/sort operations so that TEMPDB isn't abused.
Import records in batches of 1000, 10000, whatever works for you, keep
the size of the transactions under control.