How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?
stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How can a SP to handle a "TempDB Full" error ?

I always need to create a temporary table and insert a large amount of
records into it within a stored procedure, my problem is : even I have
already set the growth rate of the TempDB to 100%, but once all the free
TempDB disk space is used up, the stored procedure will fail and terminated
no matter how much free hard disk space outside the TempDB is.
Could anybody tell me is it possible to handle this error within a stored
procedure ?
For example, will the SP generate an error code for such a suitation ? Or
can I add some commands with a SP so that the SP can increase or shrink the
TempDB dynamically ?stuff I can recommend:
1. find out what has caused the tempdb to grow so much? uncommitted
transactions during a long period of time? try to reduce the transaction
size.
2. Alter the tempdb to larger size so that when it gets recreated upon
server restart, it doesn't have to grow as much.
3. don't set autogrowth by 100%. That may take too long to expand and cause
procs to time out.
4. Set up an alert on tempdb size exceeding a threshold. Run dbcc
shrinkdatabase or dbcc shrinkfile to reduce tempdb.
richard
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c60sfj$ns41@.imsp212.netvigator.com...
> I always need to create a temporary table and insert a large amount of
> records into it within a stored procedure, my problem is : even I have
> already set the growth rate of the TempDB to 100%, but once all the free
> TempDB disk space is used up, the stored procedure will fail and
terminated
> no matter how much free hard disk space outside the TempDB is.
> Could anybody tell me is it possible to handle this error within a stored
> procedure ?
> For example, will the SP generate an error code for such a suitation ? Or
> can I add some commands with a SP so that the SP can increase or shrink
the
> TempDB dynamically ?
>
>

How big SQL can handle

I plan to put all data into one database table. My observation of data file
increamental is about 1M/month. The life span of the database won't be more
than 10 years. So it can be up to 120M. Is it too big for a table to handle?|
| I plan to put all data into one database table. My observation of data
file
| increamental is about 1M/month. The life span of the database won't be
more
| than 10 years. So it can be up to 120M. Is it too big for a table to
handle?
--
SQL Server can handle 1,048, 516 terrabytes of data.
Hope this helps,
--
Eric Cárdenas
SQL Server support