Explicitly Dropping #temp tables

Does this help in any way to prevent tempdb from filling up too quickly?Hi
I do it but actually local temprary table lives on scope of stored
procedure it was called thus it will be dropped without explicity
droping it.
"MLD" <mldboston@.gmail.com> wrote in message
news:1133874965.478252.146460@.g43g2000cwa.googlegroups.com...
> Does this help in any way to prevent tempdb from filling up too quickly?
>|||Hi,
Yes, I think it will help, because #temp tables are deleted when connection
is closed.
So if you create a lot of #temp tables with lots of users it can grow...so
if you don't need the tables you should delete it...
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"MLD" <mldboston@.gmail.com> wrote in message
news:1133874965.478252.146460@.g43g2000cwa.googlegroups.com...
> Does this help in any way to prevent tempdb from filling up too quickly?
>|||Danijel
> So if you create a lot of #temp tables with lots of users it can grow...so
> if you don't need the tables you should delete it...
I don't understand you. How does it relate how many users do you have ?
Each user will get his/her own "copy" of the table.
and where will it grow?
> if you don't need the tables you should delete it...
Drop or DELETE?
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:uC9u5km%23FHA.1256@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Yes, I think it will help, because #temp tables are deleted when
> connection is closed.
> So if you create a lot of #temp tables with lots of users it can grow...so
> if you don't need the tables you should delete it...
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "MLD" <mldboston@.gmail.com> wrote in message
> news:1133874965.478252.146460@.g43g2000cwa.googlegroups.com...
>> Does this help in any way to prevent tempdb from filling up too quickly?
>|||For each user a copy of table is created when they are using the same stored
procedure, so each user has its own in tempdb.
It is DROP...thanks
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePV2Som%23FHA.2264@.tk2msftngp13.phx.gbl...
> Danijel
>> So if you create a lot of #temp tables with lots of users it can
>> grow...so if you don't need the tables you should delete it...
> I don't understand you. How does it relate how many users do you have ?
> Each user will get his/her own "copy" of the table.
> and where will it grow?
>> if you don't need the tables you should delete it...
>
> Drop or DELETE?
>
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:uC9u5km%23FHA.1256@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> Yes, I think it will help, because #temp tables are deleted when
>> connection is closed.
>> So if you create a lot of #temp tables with lots of users it can
>> grow...so if you don't need the tables you should delete it...
>> --
>> Danijel Novak
>> MCP+I, MCSA, MCSE, MCDBA, MCT
>>
>> "MLD" <mldboston@.gmail.com> wrote in message
>> news:1133874965.478252.146460@.g43g2000cwa.googlegroups.com...
>> Does this help in any way to prevent tempdb from filling up too quickly?
>>
>|||Danijel
It is not from what TEMPDB will be growing .
I copied this info from Aaron Betrand's web site www.aspfaq.com
/*
Usually, tempdb fills up when you are low on disk space, or when you have
set an unreasonably low maximum size for database growth.
Many people think that tempdb is only used for #temp tables. When in fact,
you can easily fill up tempdb without ever creating a single temp table.
Some other scenarios that can cause tempdb to fill up:
a.. any sorting that requires more memory than has been allocated to SQL
Server will be forced to do its work in tempdb;
b.. if the sorting requires more space than you have allocated to tempdb,
one of the above errors will occur;
c.. DBCC CheckDB('any database') will perform its work in tempdb -- on
larger databases, this can consume quite a bit of space;
d.. DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option
set will also potentially fill up tempdb;
e.. large resultsets involving unions, order by / group by, cartesian
joins, outer joins, cursors, temp tables, table variables, and hashing can
often require help from tempdb;
f.. any transactions left uncommitted and not rolled back can leave
objects orphaned in tempdb;
g.. use of an ODBC DSN with the option 'create temporary stored
procedures' set can leave objects there for the life of the connection.
*/
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:Ox20Itm%23FHA.3308@.TK2MSFTNGP11.phx.gbl...
> For each user a copy of table is created when they are using the same
> stored procedure, so each user has its own in tempdb.
> It is DROP...thanks
> --
> Danijel Novak
> MCP+I, MCSA, MCSE, MCDBA, MCT
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ePV2Som%23FHA.2264@.tk2msftngp13.phx.gbl...
>> Danijel
>> So if you create a lot of #temp tables with lots of users it can
>> grow...so if you don't need the tables you should delete it...
>> I don't understand you. How does it relate how many users do you have ?
>> Each user will get his/her own "copy" of the table.
>> and where will it grow?
>> if you don't need the tables you should delete it...
>>
>> Drop or DELETE?
>>
>> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
>> news:uC9u5km%23FHA.1256@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> Yes, I think it will help, because #temp tables are deleted when
>> connection is closed.
>> So if you create a lot of #temp tables with lots of users it can
>> grow...so if you don't need the tables you should delete it...
>> --
>> Danijel Novak
>> MCP+I, MCSA, MCSE, MCDBA, MCT
>>
>> "MLD" <mldboston@.gmail.com> wrote in message
>> news:1133874965.478252.146460@.g43g2000cwa.googlegroups.com...
>> Does this help in any way to prevent tempdb from filling up too
>> quickly?
>>
>>
>|||Uri,
You are absolutely right. A lot of other scenarios are in fact affecting the
size of tempdb along with #temp tables.
My point was if you drop #temp tables after you don't need them any more, it
will leave more space for new temp tables or other scenarios.
--
Danijel Novak
MCP+I, MCSA, MCSE, MCDBA, MCT
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uF3y0wm%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Danijel
> It is not from what TEMPDB will be growing .
> I copied this info from Aaron Betrand's web site www.aspfaq.com
> /*
> Usually, tempdb fills up when you are low on disk space, or when you have
> set an unreasonably low maximum size for database growth.
> Many people think that tempdb is only used for #temp tables. When in fact,
> you can easily fill up tempdb without ever creating a single temp table.
> Some other scenarios that can cause tempdb to fill up:
> a.. any sorting that requires more memory than has been allocated to SQL
> Server will be forced to do its work in tempdb;
>
> b.. if the sorting requires more space than you have allocated to tempdb,
> one of the above errors will occur;
>
> c.. DBCC CheckDB('any database') will perform its work in tempdb -- on
> larger databases, this can consume quite a bit of space;
>
> d.. DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option
> set will also potentially fill up tempdb;
>
> e.. large resultsets involving unions, order by / group by, cartesian
> joins, outer joins, cursors, temp tables, table variables, and hashing can
> often require help from tempdb;
>
> f.. any transactions left uncommitted and not rolled back can leave
> objects orphaned in tempdb;
>
> g.. use of an ODBC DSN with the option 'create temporary stored
> procedures' set can leave objects there for the life of the connection.
> */
>
>
> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
> news:Ox20Itm%23FHA.3308@.TK2MSFTNGP11.phx.gbl...
>> For each user a copy of table is created when they are using the same
>> stored procedure, so each user has its own in tempdb.
>> It is DROP...thanks
>> --
>> Danijel Novak
>> MCP+I, MCSA, MCSE, MCDBA, MCT
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:ePV2Som%23FHA.2264@.tk2msftngp13.phx.gbl...
>> Danijel
>> So if you create a lot of #temp tables with lots of users it can
>> grow...so if you don't need the tables you should delete it...
>> I don't understand you. How does it relate how many users do you have ?
>> Each user will get his/her own "copy" of the table.
>> and where will it grow?
>> if you don't need the tables you should delete it...
>>
>> Drop or DELETE?
>>
>> "Danijel Novak" <danijel.novak@.triera.net> wrote in message
>> news:uC9u5km%23FHA.1256@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> Yes, I think it will help, because #temp tables are deleted when
>> connection is closed.
>> So if you create a lot of #temp tables with lots of users it can
>> grow...so if you don't need the tables you should delete it...
>> --
>> Danijel Novak
>> MCP+I, MCSA, MCSE, MCDBA, MCT
>>
>> "MLD" <mldboston@.gmail.com> wrote in message
>> news:1133874965.478252.146460@.g43g2000cwa.googlegroups.com...
>> Does this help in any way to prevent tempdb from filling up too
>> quickly?
>>
>>
>>
>