-1

We have 100s of proc which are creating temp tables and Not dropping them. Many of the builds are in production. We would like to have SQL query to drop these after N (=2) days and schedule it. Is there any way to do that? Below is sample code to iterate from old temp tables but its not able to delete the tables if I run it.

USE tempdb;
DECLARE @SQL VARCHAR(MAX) 

set @SQL = 
(
    SELECT   STUFF(
    (
        SELECT ' DROP TABLE ' + cast(name as varchar(200))
        FROM tempdb.sys.tables where create_date < '2019-12-10 00:00:00.000'
        FOR XML PATH('')
    ),1, 1, '')
);

PRINT @SQL;
abksharma
  • 576
  • 7
  • 26
  • Does your sample code work for a fixed date? you can just add something like `DATEADD(d,-2,GETADATE())` to it to make it dynamic – Nick.Mc Dec 16 '19 at 07:06
  • Yes bro! its implied. :) – abksharma Dec 16 '19 at 07:08
  • 4
    Never assume or imply anything when you are coding. – Nick.Mc Dec 16 '19 at 07:10
  • 2
    #temp and ##temp tables wind up in tempdb but they're managed and disposed of by SQL Server. Creating your own tables in tempdb is not the same thing as creating temp tables. Don't do that. – AlwaysLearning Dec 16 '19 at 09:32
  • @Nick.McDermaid I have edited the question for correctness. and it did not work for older temp tables. even "SA" user has Access denied to tempdb. – abksharma Dec 16 '19 at 11:48
  • What does "not able to delete" mean? That code doesn't do anything it just prints out some code that you're meant to run. Are you runnign the code that it prints out? Does it list the tables you expect? Do you get an error when you run the printed code? Please take the time to explain exactly what is going on. Please also clarify whether these tables exist in `tempdb` or a normal db. In fact posting some of the code that generates these tables is necessary. – Nick.Mc Dec 16 '19 at 12:24
  • +1 what @AlwaysLearning said. what you are doing should not be necessary if you create # and ## temp tables. – robbpriestley Dec 16 '19 at 19:50

2 Answers2

0

A temp table is dropped by engine when the connection is closed and recycled.

So We are not required to drop the #table ##table as it can be done automatically by the Engine itself. However as a best practice manually deleting the table in the script which created it will help Sql server decide Garbage collection.

Deleting Global Temporary Tables (##tempTable) in SQL Server

Pls check below In latest versions of SQL Server https://codingsight.com/introduction-to-temporary-tables-in-sql-server/

abksharma
  • 576
  • 7
  • 26
0

The moment the query is finished with it, it can never be accessed again, so it doesn't matter if the table is 2 days old or 2 seconds old, it should be deleted.

If some query is using the table, it would be locked and you would be unable to delete it.

One simple method is to reboot the SQL server, this clears all temp tables.

OwlFace
  • 31
  • 5