SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Dropping and Recreating Sproc

Dropping and Recreating Sproc

SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 12
Hello, I have a unique problem that started happening recently. In sql server 2005 when i run a script to drop and stored procedure and re-create it using the script genereated from the script generator that sproc doesn't work properly.

It's a fairly complex sproc with lots of temp tables and joing and such. Everything in the sproc works except my keyword search where it's only doing a LIKE statement in a column (title). If anything but the keyword is searched such as topics, members, etc. etc., the sproc works fine. It's the simplest inclusion of keyword using a LIKE statemnt that seems to cause the hang.

Running the sproc in query analyzer under the context of the web account works perfect and fast. However, the same statement run from my command object in asp times out.

What's interesting is running the scripts on my development site works great. It's only on the Production servers that things get screwy. The only difference between the two environments is the Production box has been hardened security-wise.

If I create a new sproc with a different name and update my code it works perfect again. I do this by right clicking on the non-working sproc and selecting SCRIPT STORED PROCEDURE AS --> CREATE TO and then just change the name from SearchMedia1 to SearchMedia2 and execute and all is Great. It's just very sketchy doing updates with the hope that this techniqe works and I obviously can't be changing storeprocedure names with every update. Again, if i just drop it and recreate it with the same name, it behaves as described above. It only just recently started behaving this way. I would typically do a drop/create a weekly basis without any issues.

Additionally, the cpu spikes and hangs high (in the 80-90%).

After giving a new name to the spoc the cpu is normal again (in the 1-5% range) and all is great. It's a high traffic website which I'm sure is causing the cpu spike with all the timeouts.

I've tried searching for resolutions but it's a difficult problem to search for and get relevent results.

I don't think this is part of the issue but worth throwing out. The same day I discovered the above probelem with the keyword searching we discovered we were victom of a sql injection attack which updated a few tables and all their columns by appending a js script include tag. This was fairly easily fixed and the holes where the problem arose were fixed.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220858 Visits: 33550
I'm not sure if the injection attack is related or not.

It sounds like you recompiled after dropping and creating the script and got an execution plan that didn't run well. I've seen that type of thing occur with my stuff.

It's also possible that the ANSI_NULLS or some other settings generated by the script were not the same as they had been and were incompatible with your web app.

Those are my best two guesses.

The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum