I know its impossible to get everything covered, but I am trying to get things as much automated as possible. Something like alerts, say if a process has been blocking...

Btw, we have transactional replication, log shipping in our environment. It would be great to know what to monitor for these.

I found it inconsistent, it could return e.g. EXECUTE GetProductOptions 'LNX3252' at some time or 'CREATE PROCEDURE.....' other time.

It gets the sql_handle from sysprocesses and use fn_get_sql(sql_handle) to get the sql thats running.

I was able to capture it this morning. It was just trying to execute the proc instead of 'create proc', somehow a proc I created to monitor blocking shows 'create...

plus information_schema.routines also shows the modifieddate as a few months back ago too.

I tried tracing it yesterday, but I started too late. And today, I did not see any blocking. Its weird though that in the sysobjects table, the crdate shows a...

What kind of indexes do you use on the order table? Is the identity field (OrderID, I suppose) clustered or non-clustered? I think the reason is contention on page allocation...

Thanks for the reply.

I did a little experiment this morning. I thought I would just go forward with the default value because it is a lot of hassle running...

Thanks!

In our case, the hostname is always the web server name and application name is iis. So seems like there is no way to tell which user machine the query...

P.S. we are only using sql server authentication. So in profiler, it seems there is no way to track down which computer/user the query is running from. So how do...

Thanks for the reply.

I guess I am still not quite clear yet. This is what I am trying to find out. We have an internal web site where all...

Sorry it has to be wrapped up in one transaction.

Thanks for the reply.

It works now with the index! Thanks for the help!

Thanks!

I do not have index on orderid, let me add it and see it fix it.