Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    6

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

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Look at
    http://msdn.microsoft.com/library/de...qlquerproc.asp

    It explains what sp_execute does.
    Do you have a lot of dynamic sql or embedded sql rather than SPs?

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426

  4. #4
    Join Date
    Dec 2002
    Posts
    6
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •