Drop SP TemDB

Question

Drop SP TemDB

rootfixxxer

SSCrazy

Points: 2918
More actions
June 17, 2015 at 3:01 am

#398610

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

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

rootfixxxer SSCrazy Points: 2918 More actions · Answer 2

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...

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

rootfixxxer SSCrazy Points: 2918 More actions · Answer 5

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

Drop SP TemDB

Cookies on SQLServerCentral