Archive | SQL Profiler RSS for this section

Trace Your Server – Automating Profiler!

Need to find out who is accessing your server?  How about what is being accessed?  Need to work on compliance issues?  Just need to see what is being run?

Have no fear, SQL Profiler is here!

Open up SSMS, go to tools and then click on SQL Profiler.

There are an abundance of options but I will walk you through my typical setup.

Go to file – New Trace and connect to the database you are wanting to run your trace against.

Personally, I uncheck audit login/logout, and existing connections.  I then [usually] do Show All Columns and then make sure I display HostName.  This will display where the database calls are being called from.  Let’s take for example you want to see who is hitting a certain database from SSMS [get these people off of production!].  If you go to your filters section [Column Filters] click on Application Name.  Under “like” enter in %Management Studio% and press enter.  This will now show you every command being run on this instance through Management Studio.  Want to reduce it a little more … perhaps to just a single database?

Click on your column filters again, navigate to Database Name, go to your like section and put the name of the database you want to watch.  As always, do this on a development server so that you can test things out first.   Click on Run and watch the commands fly [unless the server isn’t used very much … if it isn’t then open up SSMS, connect to the server in question and issue some commands to verify your code is working!].

The next part is where it gets more entertaining… obviously you don’t want to just be running Profiler all the time.  Well guess what?  You can call all of this from a SQL Agent Job.

The first step is to script the trace that you just created — go to Profiler and click File -> Export -> Script Trace Definition -> For SQL Server 2005 – SQL Server …

Next, edit the file in notepad just so you can look at it.

You will see some variables declared — then a bunch of sp_trace_setevents.  Near the bottom you will see your filters that you setup.

One *KEY* thing to note is that Profiler seems to drop database restrictions.  Don’t ask me why … but it does.  So before you run anything make sure you add the following command under all of the other setfilter’s:

exec sp_trace_setfilter @TraceID, 35, 0, 6, N’DATABASE_NAME_HERE’

We are going to make a couple of other changes before we test out our work.  First declare a couple variables at the top of the code — I do:

declare @datetime datetime

declare @filename nvarchar(100)

then afterwards I set these values:

set @Datetime = DateAdd(hh, 1, getdate())
set @FileName = N’F:\Trace\CollectionData_’ + CONVERT(VARCHAR(8), GETDATE(), 112)

set @maxfilesize = 150

you want to do this before the command exec @rc = sp_trace create, because you need to modify this one as well to look like:

exec @rc = sp_trace_create @TraceID output, 0, @Filename, @maxfilesize, @DateTime

Now copy everything over to your SSMS and connect to your development server — and run it.  Come back in an hour [or whatever you set @dateTime to] and copy the file to your desktop, open up Profiler and open the file.  Voila, you have data!  You can now export it to a different database server if you want to run queries against the data you have collected.

Everything look ok?  Ready to automate this?

Open up your SQL Agent — create a new job.  Add a job step and name it whatever you want … copy in your code and then schedule it.

Always start small and know that you can add rollover files and change your maximum file sizes.  I like to keep my file sizes fairly small but make sure if you are using roll-over that you know pretty closely how much space you have on your server to save these files.

Rejoice in the knowledge that you can now keep track of all sorts of fun things.  Please be sure not to go overboard as there is definitely a performance hit to your server if you have a lot of tracing going on — depending on what you are doing one or two small ones should not provide much of a noticeable impact, however.

Questions?  Comments?  Let me know!  I will hopefully be updating this post soon, having an images hiccup.  My apologies but you will have to do without the supporting images for now, I’m sorry!