Re: Auditing sql server at runtime
- From: "Russell Fields" <russellfields@xxxxxxxxxx>
- Date: Thu, 4 Jun 2009 10:45:55 -0400
And, if you want to look at what is running at the moment, you might try Adam Machanic's Who Is Active:
http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx
RLF
"Manpreet Singh" <ManpreetSingh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:E5B6F0A3-33AB-4F73-8889-48CE5B19E3F1@xxxxxxxxxxxxxxxx
Its ok , but I wanted to do it in runtime the approach is fine but it
introduce additional latency And I don’t want to pot extra burden on sql
server instance (if possible). Profiler traces will not able to give me
runtime information. Is there any way through which I can find out which
queries are being executed against my Sql instance? Like runtime auditing or
something?
Manpreet Singh
http://crazysql.co.nr
http://crazysql.wordpress.com
MCITP – DB Admin, DB Dev SQL Server 2005
MCTS – MOSS 2007, SQL server 2005
"Russell Fields" wrote:
Well, using SQL Trace you can do the following:
1. Use SQL Profiler to define your trace, then script it out. (Because you
state that you want to see what is being submitted, I would suggest starting
with tracing only SQL batches, not individual stored procedures, statements,
etc.)
2. Create a SQL Agent job to run that trace, paste the script into it. (You
may need to do a couple of manual edits of the script, since file name (if
you trace to file) and end time do not make it into the script.)
3. Create a stored procedure that will start the SQL Agent job.
4. User sp_procoption to mark the stored procedure for autoexecution when
the SQL Server is restarted.
You can send the trace to a SQL Server table, but that adds extra load to
the server. Or you can trace to files. The file size setting will limit
how big a single file is, but the trace can roll over to numbered files.
So, once a file is complete, you could import it to a table for review. (Or
even import it to another server.)
SQL Profiler is just a user interface. Once you automate the trace, you may
not need to use profiler again for this trace.
RLF
"Manpreet Singh" <ManpreetSingh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:13AEF04D-3BF2-49B6-96A8-2DA504C02ED3@xxxxxxxxxxxxxxxx
> Is there a any way through which I can intersect the Sql server 2005
> traffic?
> I know I can use profiler or profiler trace to achieve this task, but > the
> problem is that, I wanted to do it programmatically and at run time. eg
> like
> capturing 1433 port data and getting queries out of them etc . Please > help
> me
> as I wanted to create a application which captures and log all the > queries
> which are fired against sql server 2005
>
> Manpreet Singh
> http://crazysql.co.nr
> http://crazysql.wordpress.com
> MCITP – DB Admin, DB Dev SQL Server 2005
> MCTS – MOSS 2007, SQL server 2005
>
.
- References:
- Auditing sql server at runtime
- From: Manpreet Singh
- Re: Auditing sql server at runtime
- From: Russell Fields
- Re: Auditing sql server at runtime
- From: Manpreet Singh
- Auditing sql server at runtime
- Prev by Date: Re: Auditing sql server at runtime
- Next by Date: Trace Sp.
- Previous by thread: Re: Auditing sql server at runtime
- Next by thread: Trace Sp.
- Index(es):
Relevant Pages
|
