Saturday, February 25, 2012

Process Blocking Problem

We are running SQL Server 7.0 Service Pack #4, running on Windows 2000 Server.
We have a problem with process blocking. At some points the T-SQL statement runs restricting all our users from accessing the DB.
The statement is:

SET FMTONLY ON
exec sp_execute 1
SET FMTONLY OFF

Only when it finishes running, then users are allowed to access the db again.
It is triggered automatically. Does any know what condition on the server is causing this statements to run and what can be done to prevent it. Thanks so much for your help.Look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp

It explains what sp_execute does.
Do you have a lot of dynamic sql or embedded sql rather than SPs?|||Also look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/adoapp.asp|||What I found out from the Microsoft website that sp_execute is a command that runs a prepared statement using the sp_prepare command.
So what happened is I've watched the SQL for the condition to repeat but it did not. However what I've noticed that our of nowhere at time sp_prepare runs without any parameters, causing the CPU utilization go up to high 90%.
What is causing this sp_prepate to run, as it done automatically.
Thanks for your help.

No comments:

Post a Comment