x

SQL Server profiler

Please, can anyone help me with this problem? I have mssqlserver 2008 r2 standard edition, database 123, many tables with data, 5logins defined in security...and here is my problem - I have to continuous saving TXT (or something like that) log, with information, WHO and WHAT he write or login/out or edit what table... I found something like SQL Server profiler, but its seems to be comlicated for me...

Any help or guide? Thanks!!
more ▼

asked Apr 29, 2012 at 05:30 PM in Default

hepios gravatar image

hepios
20 2 2 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

For what you are describing, I wouldn't use profiler. It sounds like you will be doing this report gathering often and you don't want profiler up & running all the time. Rather I would look towards using a server side trace or extend events. Both of these tools allow you to gather your data behind the scenes and are much less intrusive to sql server.

Jonathan Kehayias did an excellent series of blog posts regarging Extended Events: http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/an-xevent-a-day-1-31-an-overview-of-extended-events.aspx

I will warn you that extended events are complicated (at least they are for me) so it might take some research to make it do what you want. From what I've know, Profiler and Server Side traces will be going away in future releases of SQL Server in favor of Extended Events, which is another reason that I mention the tool.

Server side traces are little bit easier to understand (in my opinion) and there are a number of excellent articles out on the web that describe how to use them.

Here's a starting point though: http://msdn.microsoft.com/en-us/library/ms191443%28v=sql.105%29.aspx

You can also use Profiler to generate your server side traces, which might things easier to get up and running.

Hope this helps!!

more ▼

answered Apr 29, 2012 at 07:46 PM

JohnM gravatar image

JohnM
6.6k 1 3 7

(comments are locked)
10|1200 characters needed characters left
how are the logins connecting to the data? I would recommend the application they are using is adapted to audit the user activity if possible. If not then Extended Events is the best way to go (as suggested by @john morehouse) as you are on SQL 2008 R2. They are much less burden on your server performance. How are you going to store the data collected, for how long, with what security? There are lots of questions you probably want to consider along with the method of collection
more ▼

answered Apr 30, 2012 at 12:30 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

+1 Good point. Didn't think about modifying the application to capture that information. I would agree that would be best if possible.
Apr 30, 2012 at 01:50 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x34

asked: Apr 29, 2012 at 05:30 PM

Seen: 701 times

Last Updated: Apr 30, 2012 at 01:50 PM