Monitoring database activities from outside SQL Server

Hi,

I want to know how can I monitor some operations that are executed on a SQL Server database. Operations like: INSERT, DELETE, UPDATE, transactions, and so on (I know that I can do this using triggers, but doing this way I'll have to fill a table with the information from the triggers and stay loading the table data all the time. I want a way to monitor using other resources, in realtime).

My idea is to build a program to monitor these activities (developed in C# to be more exactly). How can I do this?

Thanks

PS: I know that we already have commercial products to do this, but I need to develop one.

WHy not just use a trace and export it to whatever external app you want to review?

I usually create my traces using sp_**trace, then I've set up external apps to call the trace and view the results.

|||

The problem is...if I create some triggers and Stored Procedures, my program will have to stay selecting data for long cycles. I'd like to have something automated, so when something occurs, automatically my program understands it.

Must have a way to do this, because I think SQL Profiler wouldn't use SELECTs to get Performance and Counting info.

Monitoring database activities from outside SQL Server

Hi,

I want to know how can I monitor some operations that are executed on a SQL Server database. Operations like: INSERT, DELETE, UPDATE, transactions, and so on (I know that I can do this using triggers, but doing this way I'll have to fill a table with the information from the triggers and stay loading the table data all the time. I want a way to monitor using other resources, in realtime).

My idea is to build a program to monitor these activities (developed in C# to be more exactly). How can I do this?

Thanks

PS: I know that we already have commercial products to do this, but I need to develop one.

WHy not just use a trace and export it to whatever external app you want to review?

I usually create my traces using sp_**trace, then I've set up external apps to call the trace and view the results.

|||

The problem is...if I create some triggers and Stored Procedures, my program will have to stay selecting data for long cycles. I'd like to have something automated, so when something occurs, automatically my program understands it.

Must have a way to do this, because I think SQL Profiler wouldn't use SELECTs to get Performance and Counting info.

Monitor SQL agent

Hi,
we know that SQL agent controls the job's schedule & running, but if SQL
agent stop working, all jobs will not be able to be executed, is there any
tool/method to monitor SQL agent if it stopped, hung or wrong from another
SQL server, so the other SQL server will send operator notification?
Thanks in advance.
JackNot directly. But you can code it yourself. Use a combination of xp_cmdshell and netsvc (I believe
that the utility is called with which you can check and start/stop services on another machine). You
can pull the result into a table and then check against that table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <chongh.hc@.gmail.com> wrote in message news:eHqYn73PFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> we know that SQL agent controls the job's schedule & running, but if SQL agent stop working, all
> jobs will not be able to be executed, is there any tool/method to monitor SQL agent if it stopped,
> hung or wrong from another SQL server, so the other SQL server will send operator notification?
> Thanks in advance.
> Jack
>

Monitor SQL agent

Hi,
we know that SQL agent controls the job's schedule & running, but if SQL
agent stop working, all jobs will not be able to be executed, is there any
tool/method to monitor SQL agent if it stopped, hung or wrong from another
SQL server, so the other SQL server will send operator notification?
Thanks in advance.
JackNot directly. But you can code it yourself. Use a combination of xp_cmdshell
and netsvc (I believe
that the utility is called with which you can check and start/stop services
on another machine). You
can pull the result into a table and then check against that table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <chongh.hc@.gmail.com> wrote in message news:eHqYn73PFHA.3076@.tk2msftngp13.phx.gbl...[
vbcol=seagreen]
> Hi,
> we know that SQL agent controls the job's schedule & running, but if SQL a
gent stop working, all
> jobs will not be able to be executed, is there any tool/method to monitor
SQL agent if it stopped,
> hung or wrong from another SQL server, so the other SQL server will send o
perator notification?
> Thanks in advance.
> Jack
>[/vbcol]

Monitor SQL agent

Hi,
we know that SQL agent controls the job's schedule & running, but if SQL
agent stop working, all jobs will not be able to be executed, is there any
tool/method to monitor SQL agent if it stopped, hung or wrong from another
SQL server, so the other SQL server will send operator notification?
Thanks in advance.
Jack
Not directly. But you can code it yourself. Use a combination of xp_cmdshell and netsvc (I believe
that the utility is called with which you can check and start/stop services on another machine). You
can pull the result into a table and then check against that table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <chongh.hc@.gmail.com> wrote in message news:eHqYn73PFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> we know that SQL agent controls the job's schedule & running, but if SQL agent stop working, all
> jobs will not be able to be executed, is there any tool/method to monitor SQL agent if it stopped,
> hung or wrong from another SQL server, so the other SQL server will send operator notification?
> Thanks in advance.
> Jack
>

Momitoring when job has executed

Is there a technique for determining when a job has been executed - using T-SQL? I tried using 'sp_help_job' by inserting the output into a table (e.g. INSERT jobtable EXEC sp_help_job...) but I get the following error:

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 68

An INSERT EXEC statement cannot be nested.

Any ideas anyone?

Rgds

Bertrand

You can all the info from msdb..sysjobs and msdb..sysjobsteps table. I suggest you take a look at the following article (thanks Gert) so you can handle the sqlagent datetime.
http://sqldev.net/sqlagent/SQLAgentDateTime.htm