To track the SQL Server process

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a situation like this

A click event which enables a batch process to run in sql server. I will show a pop up to display the progress of the back end activity.(Time duration). Also i can cancel the activity usng the button available in the popup

How can go for this in .NET

Can any one help me to solve this issue

Thanks in advanc
Ama
 
I don't know how good of a solution this is, but if you get nothing else,
then at least you have something.

before starting the transaction that's running on the sqlserver, you can
start a thread locally which continually selects out of the table
master.dbo.sysprocesses based on host computer and app name, and with the
information about the processyou can tell what state it's in (waiting,
running, writing, etc..) and how much cpu time it's had so far.
There will really be no way to tell how much longer it has to go, so you
could possibly keep a running average of how long the process gennerlly
takes, and base some guestimate of completion on that.
However, if the process takes a very different amount of time each time you
run it, this ain't gonna work.

But hey, at least it's a start.

Eric


Amalorpavanathan Yagulasamy (AMAL) said:
I have a situation like this.

A click event which enables a batch process to run in sql server. I will
Click to expand...
show a pop up to display the progress of the back end activity.(Time
duration). Also i can cancel the activity usng the button available in the
popup.
 
Or, suposing the process your running on the server is a stored procedure,
you could have that stored procedure write it's progress out to another
table,
and have your second client thread update the progress bar based on that.
 
Eric,

Although I agree that a suggestion is needed to get the brainstorming
started,
I would not use any of these solutions since it would require you to poll
the db
at a given interval, thus opening and closing connections, providing a
chatty
network etc. I do know know if the SQL Server API:s would be able to help
out in some way, but it is worth a look.

//Andreas
 
well you can't avoid at least a little network traffic here.
and sql server is pretty optimized to handle multiple connections at once.
it's also a very small performance hit to simply select out of a table the
progress status.
i hear what your saying though, it's not a very "clean" solution, so,
Amal, if you figure out something better, please let us know.

/* Eric */
 
Back
Top