Drop SP TemDB

  • Hello

    I have installed a trial version of the red gate tools, and suddenly the server start to decrease the performance, after some checks i found out that there's one sp from the red gate tools that was the responsible...

    After uninstalling the tools, i checked and the sp continues in the server, in the tempDB.

    I tried to delete manually, DROP, tried with SSMS, but i get the error saying that it doesn't exist or i don't have permission...

    I need some help.

    The script name is, '#RG_DefaultTraceQuery____ ... ___XXXXXX' like i said it's located in the tempDB.

    How can i delete this sp?

    Thanks

  • Find the SPID running the procedure and kill it. There's no other way to drop a temp object without being able to run an explicit DROP from the connection that created it.

    -- Gianluca Sartori

  • Thanks for the quick reply.

    How can i do that?

    Just to put the things in perspective i have my login, the same login that created the sp in the production server, how can i found the valid SPID?

    In the properties of the sp i can see the creation date (15-06-2015 09:09), if that helps...

  • If it's causing perf issues it's because it's running.

    Look at the activity monitor or sys.dm_exec_* DMVs to identify the SPID and kill it.

    -- Gianluca Sartori

  • Use something like this to query the DMVs:

    SELECT SPID = r.session_id, s.host_name, s.login_name, s.program_name, t.text

    FROM sys.dm_exec_requests r

    INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id

    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

    ORDER BY r.session_id;

    You can then kill the session.

  • Thanks for all the replies.

    I solved the problem in the SSMS GUI, Activity Monitor, Processes tab, filter the TempDB, and then selected the process and Kill 🙂

    THanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply