MS SQL Server - Server Side Traces in SQL 2005

Author

Server Side Traces in SQL 2005

kunalap

2006-11-09, 7:13 pm

Hello Hi

I had written scripts to run server side traces to capture commands like
'alter, drop, delete' etc on some of our servers in SQL 7 and in SQL 2000
using procedures like xp_trace_setevent, setevent filter etc etc.

Now that we are converting some of the servers to SQL 2005, I need to come
up with scripts for the same in SQL 2005.

So, my questions is. Is there a better way to do this in SQL 2005? And do
these scripts still stand a chance to run on 2005?

I am aware of server level event triggers for statements like drop, alter,
etc in 2005.

Thanks in advance.
Cheers.

Greg Linwood

2006-11-09, 7:13 pm

Hi Kunalap

The SQL Trace api in SQL 2005 is generally the same as with SQL 2000 (SQL 7
was totally different). If you really want to pursue the scripting avenue,
the SQL 2005 Profiler tool allows you to define a trace & use the File menu
to export to a script file. This makes life somewhat easier than
hand-writing the scripts, but there are still plenty of issues with managing
those scripts if you want to ensure constant tracing, which is why we built
a toolset for managing the many associated issues in this area.

You might find this useful:
http://www.GAJSoftware.com

Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood

"kunalap" <kunalap@discussions.microsoft.com> wrote in message
news:9F8B7A39-9C6C-4D65-B070- 8C44A46B6090@microso
ft.com...
> Hello Hi
>
> I had written scripts to run server side traces to capture commands like
> 'alter, drop, delete' etc on some of our servers in SQL 7 and in SQL 2000
> using procedures like xp_trace_setevent, setevent filter etc etc.
>
> Now that we are converting some of the servers to SQL 2005, I need to come
> up with scripts for the same in SQL 2005.
>
> So, my questions is. Is there a better way to do this in SQL 2005? And do
> these scripts still stand a chance to run on 2005?
>
> I am aware of server level event triggers for statements like drop, alter,
> etc in 2005.
>
> Thanks in advance.
> Cheers.
>

Tibor Karaszi

2006-11-09, 7:13 pm

You might want to look into "DDL Triggers" and the EVENTDATA() function.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"kunalap" <kunalap@discussions.microsoft.com> wrote in message
news:9F8B7A39-9C6C-4D65-B070- 8C44A46B6090@microso
ft.com...
> Hello Hi
>
> I had written scripts to run server side traces to capture commands like
> 'alter, drop, delete' etc on some of our servers in SQL 7 and in SQL 2000
> using procedures like xp_trace_setevent, setevent filter etc etc.
>
> Now that we are converting some of the servers to SQL 2005, I need to come
> up with scripts for the same in SQL 2005.
>
> So, my questions is. Is there a better way to do this in SQL 2005? And do
> these scripts still stand a chance to run on 2005?
>
> I am aware of server level event triggers for statements like drop, alter,
> etc in 2005.
>
> Thanks in advance.
> Cheers.
>

Sponsored Links