I prefer to truncate as well. And SQL2005 does not destroy temp tables correctly (don't remember if it was all temp tables or ones in SPROCs, but it is a bug) which can build up in the tempDB. If you are using hundreds a day, could be an issue, but DBCC FreeProCache dumps them. I don't have a big problem, so I periodically do this. Truncating the temp tables will minimize the problem.
Stephen Dyckes
-----Original Message-----
From: Richard Douglas <mssqldba-ezmlmshield-x30781098.[Email address protected]
To: LazyDBA Discussion <[Email address protected]
Sent: Mon Apr 28 18:12:47 2008
Subject: RE: Temp tables
I personally tend to truncate. I know there is a Knowledge Base article
on one of the versions of SQL Server that says if you drop and create
temp tables too many times it causes all sorts of problems.
Rich
-----Original Message-----
From: Jonathan Kerr
[mailto:mssqldba-ezmlmshield-x26527120.[Email address protected]
Sent: 28 April 2008 22:52
To: LazyDBA Discussion
Subject: Temp tables
Just wondering on proples preference here, when you use # or ## tables
and you need to clear all the data out of them to re use in a script do
you:
- ever bother using Truncate?
- Or just drop and recreate?
Cheers,
JK..
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality: This e-mail and any attachments are confidential and intended solely for the addressee(s) and may also be legally privileged or exempt from disclosure under applicable law. If you are not the intended addressee, or have received this email in error, please do not read the contents of the email or open attachments, notify the sender or email [Email address protected] immediately, delete it from your system and do not copy, disclose or otherwise act upon any part of this e-mail or its attachments.
Security Warning: Internet communications are not guaranteed to be secure or virus free. Except to the extent Equidebt may not exclude its liability under law, Equidebt Limited does not accept responsibility for any loss arising from unauthorised access to, or interference with, any internet communications by any third party, or from the transmission of any viruses.
Monitoring: Replies to this e-mail may be monitored by Equidebt Limited for operational or business reasons, within the scope of the law.
Opinions: Any opinion or other information in this e-mail or its attachments that does not relate to the business of Equidebt Limited or is personal to the sender is not given or endorsed by Equidebt Limited.
Information: Equidebt Limited. Registered in England and Wales (registered no. 2686796). Registered Office: Equity House, Ettington Road, Wellesbourne, Warwickshire, CV35 9GA
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
The information contained in this e-mail message is intended only
for the personal and confidential use of the recipient(s) named
above. This message may be an attorney-client communication and/or
work product and as such is privileged and confidential. If the
reader of this message is not the intended recipient or an agent
responsible for delivering it to the intended recipient, you are
hereby notified that you have received this document in error and
that any review, dissemination, distribution, or copying of this
message is strictly prohibited. If you have received this
communication in error, please notify us immediately by e-mail, and
delete the original message.
MS Sql Server LazyDBA home page