June 17, 2015 at 3:01 am
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
June 17, 2015 at 3:14 am
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
June 17, 2015 at 3:45 am
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...
June 17, 2015 at 3:49 am
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
June 17, 2015 at 5:11 am
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.
June 17, 2015 at 5:38 am
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