Check Temp table size?

I have a tempdb which fills up the disk frequently. I want to determine the
size of each object in the tempdb.
I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
not exist error. I got the temp table names from INFORMATION_SCHEMA.TABLES.
Since the temp tables only valid for their own session, how can I check its
size of temp tables through Query Analyzer?
Another question is how to analyze the log file space usage? The log file
for my TempDB grows dramatically fast too. I need understand the reason.
Thanks a lot,
FL
EXEC tempdb..spaceused '#temptablename'
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> I have a tempdb which fills up the disk frequently. I want to determine
the
> size of each object in the tempdb.
> I used EXEC sp_spaceused '#temptablename'. I got the "#temptablename" does
> not exist error. I got the temp table names from
INFORMATION_SCHEMA.TABLES.
> Since the temp tables only valid for their own session, how can I check
its
> size of temp tables through Query Analyzer?
> Another question is how to analyze the log file space usage? The log file
> for my TempDB grows dramatically fast too. I need understand the reason.
> Thanks a lot,
> FL
>
>
>
>
|||Thanks.
It did not solve my problem. Maybe my question was not clear. The
#temptablename was created in the stored procedure. Since it is a local temp
table, I can not access it from the Query Analyzer. What is the workaround?
I know if it is a global temp table I can access it.
FL
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> EXEC tempdb..spaceused '#temptablename'
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:e$uxjy91EHA.3368@.TK2MSFTNGP10.phx.gbl...
> the
does[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES.
> its
file
>
|||You want to check the size of a temp table created in a stored procedure,
from query analyzer OUTSIDE the scope of the stored procedure?
I don't think this is possible, unless you manually inspect
tempdb..sysobjects and guess which table is from the specific stored
procedure scope you are interested in. What do you expect will happen when
the stored procedure is being executed by 12 different people
simultaneously? Since the temp table only lives for the scope of the stored
procedure, why do you care aout its size OUTSIDE of that scope?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:u46sZM$1EHA.4004@.tk2msftngp13.phx.gbl...
> Thanks.
> It did not solve my problem. Maybe my question was not clear. The
> #temptablename was created in the stored procedure. Since it is a local
temp
> table, I can not access it from the Query Analyzer. What is the
workaround?[vbcol=seagreen]
> I know if it is a global temp table I can access it.
> FL
>
>
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23sIQO391EHA.1396@.tk2msftngp13.phx.gbl...
determine[vbcol=seagreen]
> does
check[vbcol=seagreen]
> file
reason.
>
|||> I don't think this is possible, unless you manually inspect
> tempdb..sysobjects and guess which table is from the specific stored
> procedure scope you are interested in.
This is unfortunate.

>What do you expect will happen when
> the stored procedure is being executed by 12 different people
> simultaneously?
I expect 12 temp tables with unique Table Names stored in the database.
Let's say there is a temp table "#tempTable" is created in the stored
procedure. Two people simultaneously execute the stored procedure. When I
ran the following query,
use tempdb
go
select *
FROM INFORMATION_SCHEMA.TABLES
In the tablename column, I saw:
#tempTable________________________________________ __________________________
_________________________________________000000058 617
#tempTable________________________________________ __________________________
_________________________________________000000058 628
Please notice the appendix are different.

>Since the temp table only lives for the scope of the stored
> procedure, why do you care aout its size OUTSIDE of that scope?
My tempdb is too large and I want to figure out the cause. One obvious
reason is that we used too many temp tables created in the stored
procedures. I know theoritically the temp table will be automatically
dropped when the stored procedure exits. However, I don't why my tempdb size
keeps growing and can not be shrinked.
Thanks a lot.
|||>
#tempTable________________________________________ __________________________
> _________________________________________000000058 617
>
#tempTable________________________________________ __________________________
> _________________________________________000000058 628
> Please notice the appendix are different.
Yes, those suffixes are generated by SQL Server internally to track #temp
tables from different sessions. Again, you would have to guess which one is
which.

> However, I don't why my tempdb size
> keeps growing and can not be shrinked.
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
|||> > However, I don't why my tempdb size
> http://www.aspfaq.com/2446
> http://www.aspfaq.com/2471
>
Very helpful link. However, when I ran the following query:
USE tempdb
GO
EXEC sp_spaceused @.updateusage = 'TRUE'
I got the result like:
database_name
database_size unallocated space
--- --
-- --
tempdb
4425.94 MB 4140.27 MB
reserved data index_size unused
-- -- -- --
624 KB 216 KB 320 KB 88 KB
My questions are:
1. What does the unallocated space mean? Free space? Unused space? Why I can
not reclaim the such space by shrinking db?
2.The data and index add up together are only 216 + 320 = 536 KB. It is less
than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
Thanks
|||The possible reasons for tempdb growth are listed in that article. Why is
your tempdb so large? I have no idea. Probably one or more of those
reasons. Why can't you shrink it? I have no idea. What else is going on
in the system? What command are you using to shrink the database? Does it
complete successfully, or do you get an error? If you get an error, what is
it?
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:uDHsq7H2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Very helpful link. However, when I ran the following query:
> USE tempdb
> GO
> EXEC sp_spaceused @.updateusage = 'TRUE'
> I got the result like:
> database_name
> database_size unallocated space
> --- --
--
> -- --
> tempdb
> 4425.94 MB 4140.27 MB
>
> reserved data index_size unused
> -- -- -- --
> 624 KB 216 KB 320 KB 88 KB
>
> My questions are:
> 1. What does the unallocated space mean? Free space? Unused space? Why I
can
> not reclaim the such space by shrinking db?
> 2.The data and index add up together are only 216 + 320 = 536 KB. It is
less
> than 1 MB. Why my tempdb is so large (> 4GB)? Any hint?
> Thanks
>
>
>