How can I check for the existence of a temp table before I attempt to drop
it?
Something like:
IF EXISTS( SELECT * FROM sysobjects WHERE Name = '#temp' )
DROP TABLE #temp
#temp exists but I cannot find a record for it in sysobjects. Neither can I
find it in tempdb (tempdb.dbo.sysobjects ).
Can anyone recommend how to handle this situation?Try:
if object_id('tempdb..#tmp') is not null
drop table #tmp
--
-Vishal
"David Frick" <dave@.frickcpa.com> wrote in message
news:uIYX89TTDHA.1992@.TK2MSFTNGP12.phx.gbl...
> How can I check for the existence of a temp table before I attempt to drop
> it?
> Something like:
> IF EXISTS( SELECT * FROM sysobjects WHERE Name = '#temp' )
> DROP TABLE #temp
> #temp exists but I cannot find a record for it in sysobjects. Neither can
I
> find it in tempdb (tempdb.dbo.sysobjects ).
> Can anyone recommend how to handle this situation?
>
Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts
Friday, February 24, 2012
Sunday, February 12, 2012
Check to see if a table exists
I am running Sql Server 7.0 and I am using a store procedure to drop a global temp table if it exists.
Currently, it looks like this:
if object_id('##Table') is null
print 'True'
else
print 'False'
Drop Table ##tmpTable
It works fine through Query Analyzer, but when I try to execute this code through ASP code, I get the following error:
Microsoft OLE DB Provider for SQL Server error '80040e09' With the statement printing out TRUE that the table doesn't exist. And if doesn't exist, it should continue on and create the table as instructed.
Any ideas as to what I am doing wrong? Is there a better way to check as to whether or not a table exists? Any help would be appreciated.
Thanks.
Kirk
That will always execute the drop table
if object_id('##Table') is null
print 'True'
else
begin
print 'False'
Drop Table ##tmpTable
end
And think will only work if you are in tempdb - try
if object_id('tempdb..##Table') is null
print 'True'
else
begin
print 'False'
Drop Table ##tmpTable
end
"Kirk" wrote:
> I am running Sql Server 7.0 and I am using a store procedure to drop a global temp table if it exists.
> Currently, it looks like this:
> if object_id('##Table') is null
> print 'True'
> else
> print 'False'
> Drop Table ##tmpTable
> It works fine through Query Analyzer, but when I try to execute this code through ASP code, I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e09' With the statement printing out TRUE that the table doesn't exist. And if doesn't exist, it should continue on and create the table as instructed.
> Any ideas as to what I am doing wrong? Is there a better way to check as to whether or not a table exists? Any help would be appreciated.
> Thanks.
> Kirk
>
Currently, it looks like this:
if object_id('##Table') is null
print 'True'
else
print 'False'
Drop Table ##tmpTable
It works fine through Query Analyzer, but when I try to execute this code through ASP code, I get the following error:
Microsoft OLE DB Provider for SQL Server error '80040e09' With the statement printing out TRUE that the table doesn't exist. And if doesn't exist, it should continue on and create the table as instructed.
Any ideas as to what I am doing wrong? Is there a better way to check as to whether or not a table exists? Any help would be appreciated.
Thanks.
Kirk
That will always execute the drop table
if object_id('##Table') is null
print 'True'
else
begin
print 'False'
Drop Table ##tmpTable
end
And think will only work if you are in tempdb - try
if object_id('tempdb..##Table') is null
print 'True'
else
begin
print 'False'
Drop Table ##tmpTable
end
"Kirk" wrote:
> I am running Sql Server 7.0 and I am using a store procedure to drop a global temp table if it exists.
> Currently, it looks like this:
> if object_id('##Table') is null
> print 'True'
> else
> print 'False'
> Drop Table ##tmpTable
> It works fine through Query Analyzer, but when I try to execute this code through ASP code, I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e09' With the statement printing out TRUE that the table doesn't exist. And if doesn't exist, it should continue on and create the table as instructed.
> Any ideas as to what I am doing wrong? Is there a better way to check as to whether or not a table exists? Any help would be appreciated.
> Thanks.
> Kirk
>
Check This
DECLARE @.Temp int
DECLARE @.FullQry varchar(50)
set @.FullQry='select @.Temp=Emp_ID from Employee where....'
Exec(@.FullQry)
select @.@.ROWCOUNT
My Employee table has 3 records and this query sholud return me @.@.ROWCOUNT=1
but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?The domain of the selection "@.Temp=" is outside the domain of the executed statement so the query as you have written it will return:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Temp'.
(1 row(s) affected)
The correct way to return values from a dynamically executed SQL section is to use sp_executesql with output semantics:
DECLARE @.Temp int
DECLARE @.Rows int
DECLARE @.sql nvarchar(4000)
SET @.sql=N'SELECT @.Rows=@.@.ROWCOUNT, @.Temp=EMp_ID FROM ...'
EXEC sp_executesql @.sql, N'@.Temp int OUTPUT, @.Rows int OUTPUT', @.Temp OUTPUT, @.Rows OUTPUT
SELECT @.Rows, @.Temp
DECLARE @.FullQry varchar(50)
set @.FullQry='select @.Temp=Emp_ID from Employee where....'
Exec(@.FullQry)
select @.@.ROWCOUNT
My Employee table has 3 records and this query sholud return me @.@.ROWCOUNT=1
but it will return 0 why this i am not able to find out.Exec function return ROWCOUNT or not?The domain of the selection "@.Temp=" is outside the domain of the executed statement so the query as you have written it will return:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Temp'.
(1 row(s) affected)
The correct way to return values from a dynamically executed SQL section is to use sp_executesql with output semantics:
DECLARE @.Temp int
DECLARE @.Rows int
DECLARE @.sql nvarchar(4000)
SET @.sql=N'SELECT @.Rows=@.@.ROWCOUNT, @.Temp=EMp_ID FROM ...'
EXEC sp_executesql @.sql, N'@.Temp int OUTPUT, @.Rows int OUTPUT', @.Temp OUTPUT, @.Rows OUTPUT
SELECT @.Rows, @.Temp
Friday, February 10, 2012
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
>
>
>
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
>
>
>
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,
FLEXEC 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...
> 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
> >
> >
> >
> >
> >
> >
> >
>|||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?
> 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...
> > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||> 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__________________________________________________________________
_________________________________________000000058617
#tempTable__________________________________________________________________
_________________________________________000000058628
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__________________________________________________________________
> _________________________________________000000058617
>
#tempTable__________________________________________________________________
> _________________________________________000000058628
> 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
> > keeps growing and can not be shrinked.
> 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...
> > > 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
> >
> 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
>
>
>
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,
FLEXEC 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...
> 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
> >
> >
> >
> >
> >
> >
> >
>|||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?
> 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...
> > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||> 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__________________________________________________________________
_________________________________________000000058617
#tempTable__________________________________________________________________
_________________________________________000000058628
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__________________________________________________________________
> _________________________________________000000058617
>
#tempTable__________________________________________________________________
> _________________________________________000000058628
> 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
> > keeps growing and can not be shrinked.
> 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...
> > > 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
> >
> 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
>
>
>
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,
FLEXEC 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...
> 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[vbcol=seagreen]
>|||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?
> 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.[vbcol=seagreen]
>|||> 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______________________________
____________________________________
________________________________________
_000000058617
#tempTable______________________________
____________________________________
________________________________________
_000000058628
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______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058617
>[/vbcol]
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058628
> Please notice the appendix are different.[/vbcol]
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
>
>
>
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,
FLEXEC 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...
> 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[vbcol=seagreen]
>|||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?
> 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.[vbcol=seagreen]
>|||> 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______________________________
____________________________________
________________________________________
_000000058617
#tempTable______________________________
____________________________________
________________________________________
_000000058628
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______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058617
>[/vbcol]
#tempTable______________________________
____________________________________[vbc
ol=seagreen]
> ________________________________________
_000000058628
> Please notice the appendix are different.[/vbcol]
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
>
>
>
Subscribe to:
Posts (Atom)