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