April 3, 2009 at 2:51 am
Hi,
I have the following problem:
I have a regularly running stored procedure which makes selects, inserts and updates. When this procedure runs it locks the management studio so no editing, clicking etc. is possible anymore. Even third-party tools like Toad freeze. The db itself works fine and the cpu doesn't show a high workload.
When I click on an entry in the object explorer, e.g. 'Activity monitor', I receive an error dialog (I have a german SQL Server, so here is the translation):
The requested dialog cannot be shown.
Additional info:
The requested command cannot be executed.
Activity monitor can't be started. Yo don't have sufficient permissions (ActivityMonitor)
The Timeout for the lockrequest has been exceeded.
The database context has been changed to 'DBname'
The command has been terminated
(Microsoft SQL Server, Error: 1222)
After restarting the db I didn't notice the problem for a few days (but honestly I didn't investigate, so this might only be a hint), but after that it showed up again. Another strange thing is that if I set a filter to this sp this problem doesn't occur.
Could this be some kind of setting somewhere? Or maybe an issue that has been fixed by a hotfix?
SQL Server 2005 9.00.3054
April 3, 2009 at 3:28 am
May I know the what sp is doing.
May be the sp is putting the some db's in single user mode.
Rajesh Kasturi
April 3, 2009 at 6:06 am
That is strange behavior. I've never seen anything like it. If you can, post the code. If it's huge, post it as an attachment.
Do you have lots of PRINT statements or something that would cause inordinate amounts of stuff returned to the calling app?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2009 at 7:54 am
i've had it happen before, usually when most of a table's rows are affected
April 3, 2009 at 9:00 am
You must mean really big tables.
But even then, the processing should be taking place on the server. I've seen things lock up when the data starts to come back from huge SELECT statements that are returning more data than is right, but not because of something occurring on the server. That's why I was wondering if it's doing something to send lots of information back to the client.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2009 at 4:53 pm
Thanks for your replies!
Unfortunately I don't have the code at home, but I'll post it on monday.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply