Professional Web Applications Themes

Drop temp tables in tempdb - Microsoft SQL / MS SQL Server

I see some temp tables in tempdb such as #tablename_________________________X23 as an example when i query tempdb..sysobjects. I noticed the create date of last month . I tried to do a select * from that table and it says invalid object. So how come I see it there ? Also how can i drop it . I am using SQL 2000....

  1. #1

    Default Drop temp tables in tempdb

    I see some temp tables in tempdb such as
    #tablename_________________________X23 as an example when i query
    tempdb..sysobjects. I noticed the create date of last month . I tried to do
    a select * from that table and it says invalid object. So how come I see it
    there ? Also how can i drop it . I am using SQL 2000.


    Hassan Guest

  2. #2

    Default Re: Drop temp tables in tempdb

    May not be the best way, but if you stop the SQL service
    and restart it, then tempdb will be rebuilt and all of the old
    cruft will disappear.


    "Hassan" <com> wrote in message
    news:phx.gbl... 
    do 
    it 


    Jason Guest

  3. #3

    Default Re: Drop temp tables in tempdb

    Try surrounding your table name with brackets. eg:
    select * from [#tablename________________________X23]


    On Sun, 17 Aug 2003 04:43:53 -0400, "Jason W. Paul"
    <com> wrote:
     
    >do 
    >it 
    >[/ref]

    Random Guest

  4. #4

    Default Re: Drop temp tables in tempdb

    [posted and mailed, please reply in news]

    Hassan (com) writes: 

    These tables are temp tables created by some process that lags around.
    The way to get rid of the tables is to kill the process. One way to
    find this process is to do:

    SELECT * FROM master..sysprocess WHERE login_time < crdate

    where crdate is the creation-data for the temp table. But before you
    kill any process, check that it is not doing anything useful.

    Restarting SQL Server as suggested in another posting is a more
    definitive way to get rid of them.
    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

Similar Threads

  1. SP Temp Tables
    By navdeep virk in forum Informix
    Replies: 6
    Last Post: February 18th, 04:30 PM
  2. Location of temp tables
    By Andy in forum Informix
    Replies: 2
    Last Post: February 6th, 03:08 PM
  3. Temp tables and jdbc 2.21.jc3 on IDS 9.4.UC1
    By Rajesh Kapur in forum Informix
    Replies: 0
    Last Post: August 26th, 09:59 PM
  4. Accessing Temp Tables
    By Karthik Nagaraj in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 12th, 11:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139