Hi experts,
i am working with SQL-Server 2000 and have a special problem in one of my
stored procedures. To be as fast as possible i use a temp table defined as
variable. And i want to calculate a value in this table depending on values
in the same table. But i can't make it work. Here my problem:
-- can be copied into QueryAnalizer --
declare @.tbl table (
id1 int,
id2 int,
w1 int,
w2 int )
insert into @.tbl values (1, 2, 12, null)
insert into @.tbl values (2, 3, 10, null)
insert into @.tbl values (3, null, 7, null)
-- this is my wish. It doesn't work, because alias t1 is not valid, but i
think you can see what i want to do:
-- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0) from
@.tbl t2 where t2.id1 = t1.id2),0)
-- and this i all i can do, but with wrong result:
update @.tbl set w2 = w1 - IsNull((select IsNull(w1,0) from @.tbl t2 where
t2.id1 = id2),0)
select * from @.tbl
----
gives me:
1 2 12 12
2 3 10 10
3 NULL 7 7
and this is what it want to have:
1 2 12 2
2 3 10 3
3 NULL 7 7
I think, the problem is because in ... where t2.id1 = id2) the value of id2
is not the value of the atually updating record, it stays on id2 of the
last insert, which means, id2 is always NULL. And i can't use an alias (why
is an alias here not possible? This is not clear to me). What can i do?
thanks,
Helmuthelmut woess wrote:
> Hi experts,
> i am working with SQL-Server 2000 and have a special problem in one
> of my stored procedures. To be as fast as possible i use a temp table
> defined as variable. And i want to calculate a value in this table
> depending on values in the same table. But i can't make it work. Here
> my problem:
> -- can be copied into QueryAnalizer --
> declare @.tbl table (
> id1 int,
> id2 int,
> w1 int,
> w2 int )
> insert into @.tbl values (1, 2, 12, null)
> insert into @.tbl values (2, 3, 10, null)
> insert into @.tbl values (3, null, 7, null)
> -- this is my wish. It doesn't work, because alias t1 is not valid,
> but i think you can see what i want to do:
> -- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
>
update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
from @.tbl t2 where t2.id1 = t1.id2),0)
from @.tbl t1
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Am Thu, 15 Dec 2005 09:16:21 -0500 schrieb Bob Barrows [MVP]:
> helmut woess wrote:
> update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
> from @.tbl t1
Incredible, how fast you came up with the solution!
thousand thanks, Bob, you saved me a lot of time!
Helmutsql
Showing posts with label temp. Show all posts
Showing posts with label temp. Show all posts
Monday, March 26, 2012
Wednesday, March 7, 2012
how to know when to restart SQL Services
Hello:
We all know that, when you want to get rid of temp tables that are clogging
databases, the easiest and usually the most foolproof way of doing so is to
stop and restart the SQL Server Services.
Aside from the times when temp table errors actually happen, are there any
perfmon, db objects, and/or services that can be monitored to determine when
a restart of Services should be accomplished?
I'm considering setting up a job of some sort to stop and restart the
services as part of monthly maintenance, and I would like for the stopping
and starting to occur before the errors happens--as a way of being proactive.
Thanks!
childofthe1980s
> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
I did not know this. Can you elaborate on how temp tables clog databases?
Temp tables are created in tempdb and are dropped automatically when 1) the
stored procedure that created the table completes, 2) when the connection
closes or 3) when explicitly dropped.
Hope this helps.
Dan Guzman
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:8BCE818E-081F-405C-857B-C63840849ABE@.microsoft.com...
> Hello:
> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
> Aside from the times when temp table errors actually happen, are there any
> perfmon, db objects, and/or services that can be monitored to determine
> when
> a restart of Services should be accomplished?
> I'm considering setting up a job of some sort to stop and restart the
> services as part of monthly maintenance, and I would like for the stopping
> and starting to occur before the errors happens--as a way of being
> proactive.
> Thanks!
> childofthe1980s
|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> I did not know this. Can you elaborate on how temp tables clog
> databases? Temp tables are created in tempdb and are dropped
> automatically when 1) the stored procedure that created the table
> completes, 2) when the connection closes or 3) when explicitly
> dropped.
There is a bug in SQL 2005 where temp tables are not dropped if a
procedure is shut down and there is no transaction in progress. There
is a fix for it, but the fix is not included in the latest service pack,
but well in the latest cumultative update, see
http://support.microsoft.com/kb/933097.
As far as I know, if you are victim to this problem, restarting the server
is about your only option.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
I think installing the update would be a better option in most cases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99766B7B02541Yazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> There is a bug in SQL 2005 where temp tables are not dropped if a
> procedure is shut down and there is no transaction in progress. There
> is a fix for it, but the fix is not included in the latest service pack,
> but well in the latest cumultative update, see
> http://support.microsoft.com/kb/933097.
> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> Temp tables are created in tempdb and are dropped automatically when 1)
> the stored procedure that created the table completes, 2) when the
> connection closes or 3) when explicitly dropped.
As Erland mentioned elsewhere, there are a couple of exceptions where tempdb
itself does not clean itself up so gracefully:
http://support.microsoft.com/kb/931843/
And there is a similar situation that I have come across, for which I am
still waiting for a fix (I have an active case with PSS).
Aaron Bertrand
SQL Server MVP
|||> I think installing the update would be a better option in most cases.
In my case, I am still experiencing similar symptoms on 9.00.3175 ...
Aaron Bertrand
SQL Server MVP
We all know that, when you want to get rid of temp tables that are clogging
databases, the easiest and usually the most foolproof way of doing so is to
stop and restart the SQL Server Services.
Aside from the times when temp table errors actually happen, are there any
perfmon, db objects, and/or services that can be monitored to determine when
a restart of Services should be accomplished?
I'm considering setting up a job of some sort to stop and restart the
services as part of monthly maintenance, and I would like for the stopping
and starting to occur before the errors happens--as a way of being proactive.
Thanks!
childofthe1980s
> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
I did not know this. Can you elaborate on how temp tables clog databases?
Temp tables are created in tempdb and are dropped automatically when 1) the
stored procedure that created the table completes, 2) when the connection
closes or 3) when explicitly dropped.
Hope this helps.
Dan Guzman
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:8BCE818E-081F-405C-857B-C63840849ABE@.microsoft.com...
> Hello:
> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
> Aside from the times when temp table errors actually happen, are there any
> perfmon, db objects, and/or services that can be monitored to determine
> when
> a restart of Services should be accomplished?
> I'm considering setting up a job of some sort to stop and restart the
> services as part of monthly maintenance, and I would like for the stopping
> and starting to occur before the errors happens--as a way of being
> proactive.
> Thanks!
> childofthe1980s
|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> I did not know this. Can you elaborate on how temp tables clog
> databases? Temp tables are created in tempdb and are dropped
> automatically when 1) the stored procedure that created the table
> completes, 2) when the connection closes or 3) when explicitly
> dropped.
There is a bug in SQL 2005 where temp tables are not dropped if a
procedure is shut down and there is no transaction in progress. There
is a fix for it, but the fix is not included in the latest service pack,
but well in the latest cumultative update, see
http://support.microsoft.com/kb/933097.
As far as I know, if you are victim to this problem, restarting the server
is about your only option.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
I think installing the update would be a better option in most cases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99766B7B02541Yazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> There is a bug in SQL 2005 where temp tables are not dropped if a
> procedure is shut down and there is no transaction in progress. There
> is a fix for it, but the fix is not included in the latest service pack,
> but well in the latest cumultative update, see
> http://support.microsoft.com/kb/933097.
> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> Temp tables are created in tempdb and are dropped automatically when 1)
> the stored procedure that created the table completes, 2) when the
> connection closes or 3) when explicitly dropped.
As Erland mentioned elsewhere, there are a couple of exceptions where tempdb
itself does not clean itself up so gracefully:
http://support.microsoft.com/kb/931843/
And there is a similar situation that I have come across, for which I am
still waiting for a fix (I have an active case with PSS).
Aaron Bertrand
SQL Server MVP
|||> I think installing the update would be a better option in most cases.
In my case, I am still experiencing similar symptoms on 9.00.3175 ...
Aaron Bertrand
SQL Server MVP
how to know when to restart SQL Services
Hello:
We all know that, when you want to get rid of temp tables that are clogging
databases, the easiest and usually the most foolproof way of doing so is to
stop and restart the SQL Server Services.
Aside from the times when temp table errors actually happen, are there any
perfmon, db objects, and/or services that can be monitored to determine when
a restart of Services should be accomplished?
I'm considering setting up a job of some sort to stop and restart the
services as part of monthly maintenance, and I would like for the stopping
and starting to occur before the errors happens--as a way of being proactive
.
Thanks!
childofthe1980s> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
I did not know this. Can you elaborate on how temp tables clog databases?
Temp tables are created in tempdb and are dropped automatically when 1) the
stored procedure that created the table completes, 2) when the connection
closes or 3) when explicitly dropped.
Hope this helps.
Dan Guzman
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:8BCE818E-081F-405C-857B-C63840849ABE@.microsoft.com...
> Hello:
> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
> Aside from the times when temp table errors actually happen, are there any
> perfmon, db objects, and/or services that can be monitored to determine
> when
> a restart of Services should be accomplished?
> I'm considering setting up a job of some sort to stop and restart the
> services as part of monthly maintenance, and I would like for the stopping
> and starting to occur before the errors happens--as a way of being
> proactive.
> Thanks!
> childofthe1980s|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> I did not know this. Can you elaborate on how temp tables clog
> databases? Temp tables are created in tempdb and are dropped
> automatically when 1) the stored procedure that created the table
> completes, 2) when the connection closes or 3) when explicitly
> dropped.
There is a bug in SQL 2005 where temp tables are not dropped if a
procedure is shut down and there is no transaction in progress. There
is a fix for it, but the fix is not included in the latest service pack,
but well in the latest cumultative update, see
http://support.microsoft.com/kb/933097.
As far as I know, if you are victim to this problem, restarting the server
is about your only option.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
I think installing the update would be a better option in most cases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99766B7B02541Yazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> There is a bug in SQL 2005 where temp tables are not dropped if a
> procedure is shut down and there is no transaction in progress. There
> is a fix for it, but the fix is not included in the latest service pack,
> but well in the latest cumultative update, see
> http://support.microsoft.com/kb/933097.
> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Temp tables are created in tempdb and are dropped automatically when 1)
> the stored procedure that created the table completes, 2) when the
> connection closes or 3) when explicitly dropped.
As Erland mentioned elsewhere, there are a couple of exceptions where tempdb
itself does not clean itself up so gracefully:
http://support.microsoft.com/kb/931843/
And there is a similar situation that I have come across, for which I am
still waiting for a fix (I have an active case with PSS).
Aaron Bertrand
SQL Server MVP|||> I think installing the update would be a better option in most cases.
In my case, I am still experiencing similar symptoms on 9.00.3175 ...
Aaron Bertrand
SQL Server MVP
We all know that, when you want to get rid of temp tables that are clogging
databases, the easiest and usually the most foolproof way of doing so is to
stop and restart the SQL Server Services.
Aside from the times when temp table errors actually happen, are there any
perfmon, db objects, and/or services that can be monitored to determine when
a restart of Services should be accomplished?
I'm considering setting up a job of some sort to stop and restart the
services as part of monthly maintenance, and I would like for the stopping
and starting to occur before the errors happens--as a way of being proactive
.
Thanks!
childofthe1980s> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
I did not know this. Can you elaborate on how temp tables clog databases?
Temp tables are created in tempdb and are dropped automatically when 1) the
stored procedure that created the table completes, 2) when the connection
closes or 3) when explicitly dropped.
Hope this helps.
Dan Guzman
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:8BCE818E-081F-405C-857B-C63840849ABE@.microsoft.com...
> Hello:
> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
> Aside from the times when temp table errors actually happen, are there any
> perfmon, db objects, and/or services that can be monitored to determine
> when
> a restart of Services should be accomplished?
> I'm considering setting up a job of some sort to stop and restart the
> services as part of monthly maintenance, and I would like for the stopping
> and starting to occur before the errors happens--as a way of being
> proactive.
> Thanks!
> childofthe1980s|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> I did not know this. Can you elaborate on how temp tables clog
> databases? Temp tables are created in tempdb and are dropped
> automatically when 1) the stored procedure that created the table
> completes, 2) when the connection closes or 3) when explicitly
> dropped.
There is a bug in SQL 2005 where temp tables are not dropped if a
procedure is shut down and there is no transaction in progress. There
is a fix for it, but the fix is not included in the latest service pack,
but well in the latest cumultative update, see
http://support.microsoft.com/kb/933097.
As far as I know, if you are victim to this problem, restarting the server
is about your only option.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
I think installing the update would be a better option in most cases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99766B7B02541Yazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
> There is a bug in SQL 2005 where temp tables are not dropped if a
> procedure is shut down and there is no transaction in progress. There
> is a fix for it, but the fix is not included in the latest service pack,
> but well in the latest cumultative update, see
> http://support.microsoft.com/kb/933097.
> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Temp tables are created in tempdb and are dropped automatically when 1)
> the stored procedure that created the table completes, 2) when the
> connection closes or 3) when explicitly dropped.
As Erland mentioned elsewhere, there are a couple of exceptions where tempdb
itself does not clean itself up so gracefully:
http://support.microsoft.com/kb/931843/
And there is a similar situation that I have come across, for which I am
still waiting for a fix (I have an active case with PSS).
Aaron Bertrand
SQL Server MVP|||> I think installing the update would be a better option in most cases.
In my case, I am still experiencing similar symptoms on 9.00.3175 ...
Aaron Bertrand
SQL Server MVP
how to know when to restart SQL Services
Hello:
We all know that, when you want to get rid of temp tables that are clogging
databases, the easiest and usually the most foolproof way of doing so is to
stop and restart the SQL Server Services.
Aside from the times when temp table errors actually happen, are there any
perfmon, db objects, and/or services that can be monitored to determine when
a restart of Services should be accomplished?
I'm considering setting up a job of some sort to stop and restart the
services as part of monthly maintenance, and I would like for the stopping
and starting to occur before the errors happens--as a way of being proactive.
Thanks!
childofthe1980s> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
I did not know this. Can you elaborate on how temp tables clog databases?
Temp tables are created in tempdb and are dropped automatically when 1) the
stored procedure that created the table completes, 2) when the connection
closes or 3) when explicitly dropped.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:8BCE818E-081F-405C-857B-C63840849ABE@.microsoft.com...
> Hello:
> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
> Aside from the times when temp table errors actually happen, are there any
> perfmon, db objects, and/or services that can be monitored to determine
> when
> a restart of Services should be accomplished?
> I'm considering setting up a job of some sort to stop and restart the
> services as part of monthly maintenance, and I would like for the stopping
> and starting to occur before the errors happens--as a way of being
> proactive.
> Thanks!
> childofthe1980s|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
>> We all know that, when you want to get rid of temp tables that are
>> clogging databases, the easiest and usually the most foolproof way of
>> doing so is to stop and restart the SQL Server Services.
> I did not know this. Can you elaborate on how temp tables clog
> databases? Temp tables are created in tempdb and are dropped
> automatically when 1) the stored procedure that created the table
> completes, 2) when the connection closes or 3) when explicitly
> dropped.
There is a bug in SQL 2005 where temp tables are not dropped if a
procedure is shut down and there is no transaction in progress. There
is a fix for it, but the fix is not included in the latest service pack,
but well in the latest cumultative update, see
http://support.microsoft.com/kb/933097.
As far as I know, if you are victim to this problem, restarting the server
is about your only option.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
I think installing the update would be a better option in most cases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99766B7B02541Yazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
>> We all know that, when you want to get rid of temp tables that are
>> clogging databases, the easiest and usually the most foolproof way of
>> doing so is to stop and restart the SQL Server Services.
>> I did not know this. Can you elaborate on how temp tables clog
>> databases? Temp tables are created in tempdb and are dropped
>> automatically when 1) the stored procedure that created the table
>> completes, 2) when the connection closes or 3) when explicitly
>> dropped.
> There is a bug in SQL 2005 where temp tables are not dropped if a
> procedure is shut down and there is no transaction in progress. There
> is a fix for it, but the fix is not included in the latest service pack,
> but well in the latest cumultative update, see
> http://support.microsoft.com/kb/933097.
> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> Temp tables are created in tempdb and are dropped automatically when 1)
> the stored procedure that created the table completes, 2) when the
> connection closes or 3) when explicitly dropped.
As Erland mentioned elsewhere, there are a couple of exceptions where tempdb
itself does not clean itself up so gracefully:
http://support.microsoft.com/kb/931843/
And there is a similar situation that I have come across, for which I am
still waiting for a fix (I have an active case with PSS).
--
Aaron Bertrand
SQL Server MVP|||> I think installing the update would be a better option in most cases.
In my case, I am still experiencing similar symptoms on 9.00.3175 ...
--
Aaron Bertrand
SQL Server MVP
We all know that, when you want to get rid of temp tables that are clogging
databases, the easiest and usually the most foolproof way of doing so is to
stop and restart the SQL Server Services.
Aside from the times when temp table errors actually happen, are there any
perfmon, db objects, and/or services that can be monitored to determine when
a restart of Services should be accomplished?
I'm considering setting up a job of some sort to stop and restart the
services as part of monthly maintenance, and I would like for the stopping
and starting to occur before the errors happens--as a way of being proactive.
Thanks!
childofthe1980s> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
I did not know this. Can you elaborate on how temp tables clog databases?
Temp tables are created in tempdb and are dropped automatically when 1) the
stored procedure that created the table completes, 2) when the connection
closes or 3) when explicitly dropped.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:8BCE818E-081F-405C-857B-C63840849ABE@.microsoft.com...
> Hello:
> We all know that, when you want to get rid of temp tables that are
> clogging
> databases, the easiest and usually the most foolproof way of doing so is
> to
> stop and restart the SQL Server Services.
> Aside from the times when temp table errors actually happen, are there any
> perfmon, db objects, and/or services that can be monitored to determine
> when
> a restart of Services should be accomplished?
> I'm considering setting up a job of some sort to stop and restart the
> services as part of monthly maintenance, and I would like for the stopping
> and starting to occur before the errors happens--as a way of being
> proactive.
> Thanks!
> childofthe1980s|||Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
>> We all know that, when you want to get rid of temp tables that are
>> clogging databases, the easiest and usually the most foolproof way of
>> doing so is to stop and restart the SQL Server Services.
> I did not know this. Can you elaborate on how temp tables clog
> databases? Temp tables are created in tempdb and are dropped
> automatically when 1) the stored procedure that created the table
> completes, 2) when the connection closes or 3) when explicitly
> dropped.
There is a bug in SQL 2005 where temp tables are not dropped if a
procedure is shut down and there is no transaction in progress. There
is a fix for it, but the fix is not included in the latest service pack,
but well in the latest cumultative update, see
http://support.microsoft.com/kb/933097.
As far as I know, if you are victim to this problem, restarting the server
is about your only option.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
I think installing the update would be a better option in most cases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99766B7B02541Yazorman@.127.0.0.1...
> Dan Guzman (guzmanda@.nospam-online.sbcglobal.net) writes:
>> We all know that, when you want to get rid of temp tables that are
>> clogging databases, the easiest and usually the most foolproof way of
>> doing so is to stop and restart the SQL Server Services.
>> I did not know this. Can you elaborate on how temp tables clog
>> databases? Temp tables are created in tempdb and are dropped
>> automatically when 1) the stored procedure that created the table
>> completes, 2) when the connection closes or 3) when explicitly
>> dropped.
> There is a bug in SQL 2005 where temp tables are not dropped if a
> procedure is shut down and there is no transaction in progress. There
> is a fix for it, but the fix is not included in the latest service pack,
> but well in the latest cumultative update, see
> http://support.microsoft.com/kb/933097.
> As far as I know, if you are victim to this problem, restarting the server
> is about your only option.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> Temp tables are created in tempdb and are dropped automatically when 1)
> the stored procedure that created the table completes, 2) when the
> connection closes or 3) when explicitly dropped.
As Erland mentioned elsewhere, there are a couple of exceptions where tempdb
itself does not clean itself up so gracefully:
http://support.microsoft.com/kb/931843/
And there is a similar situation that I have come across, for which I am
still waiting for a fix (I have an active case with PSS).
--
Aaron Bertrand
SQL Server MVP|||> I think installing the update would be a better option in most cases.
In my case, I am still experiencing similar symptoms on 9.00.3175 ...
--
Aaron Bertrand
SQL Server MVP
Subscribe to:
Posts (Atom)