Can I kill this process?

Question

Possible Duplicate:
Activity Monitor Problems in SQL Server 2005

I have some processes running on my database server that are taking up enourmous amount of CPU. When I view the detail about the process I get:

set transaction isolation level  read committed

The other details include:

• Status: Sleeping
• Open Transactions: 0
• Command: AWAITING COMMAND
• Login Time: 12+ Hours ago
• Last Batch: Less than 2 minutes ago

I haven't been watching the activity monitor lately but when I got word from my server admins that the CPU was running near 100% with SQL Server taking the majority I got very curious. That command is coming from different (web) applications but none of them ever actually use that code. Some are written in Coldfusion and others are in .NET.

Is this command generated by SQL Server?

What is causing it?

Is it safe to kill those processes?

Answer 1

Can you provide us with more information?

The command only sets the isolation level for the transaction to read commited before the transaction is started and is not the cause of the problem.

I suggest you try SQL profiler to identify the cause. Be careful when you set up filters in case it recieves a large amount of transactions.

Answer 2

Tthe answer from your earlier question still aplies:

No, SQL would not be running statements on its own. Those statements come from the web apps. They may not exist explicitly as SqlCommand text in your app, but there are several tens of thousands of lines of code from various frameworsk and ORMs between your code and what actually reaches SQL Server. A trivial example is the bening SqlConnection.Open() which sends a pretty hefty batch to initialize the session settings, a batch that ends in set transaction isolation level read committed...

Whether you can kill them or not is impossible to tell since they are your applications.

Answer 3

Agree with Remus Rusanu but just to clarify your exact question: "set transaction isolation level read committed" is not a "process". it's a statement which dictates how the statements that do come from your app will be processed.

This is saying that queries run as part of your session (in the case of ColdFusion any particular cfquery unless they are inside a cftransaction) will only read committed data. You would probably notice that if you used a cftransaction you would see a different transaction isolation level which allowed the queries to read local uncommitted data.