• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

Permanent Trace

How do I configure a server so that it records all the info in a normal trace and logs it into the database?  I don't want to use profiler because that requires someone logged in.  I'm only tracking 1 application and one database so I won't get into self-tracking tracking.  The most important data is execution time, the command, and the date/time it was executed.
0
SkipFire
Asked:
SkipFire
  • 5
  • 5
  • 3
1 Solution
 
ispalenyCommented:
SQL Server has built-in C2 support including black-box logging into files.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can do a job to launch SQL Profiler and save results to a file or a database table.
0
 
SkipFireAuthor Commented:
SQL Profiler requires someone logged.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What's the problem with that?
Just use filter to record transactions over the database you want.
0
 
SkipFireAuthor Commented:
I said I didn't want someone required to be logged in, it's a large security issue.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Not even your user?
0
 
ispalenyCommented:
It is not necessary to be logged on. You can create a auto-startup stored procedure in master database.

use master
GO
/*
create disk trace file c:\SecureMSSQLTrace.trc, using template SQLProfilerStandard, roll over 200 MB, shutdown when the disk is full
autostart with SQL Server  
*/
create proc usp_SQLProfilerStandard AS begin
declare @P1 int
exec sp_trace_create @P1 output, 6, 'c:\SecureMSSQLTrace.trc', 200, NULL
exec sp_trace_setevent @P1, 10, 1, 1
exec sp_trace_setevent @P1, 10, 6, 1
exec sp_trace_setevent @P1, 10, 9, 1
exec sp_trace_setevent @P1, 10, 10, 1
exec sp_trace_setevent @P1, 10, 11, 1
exec sp_trace_setevent @P1, 10, 12, 1
exec sp_trace_setevent @P1, 10, 13, 1
exec sp_trace_setevent @P1, 10, 14, 1
exec sp_trace_setevent @P1, 10, 16, 1
exec sp_trace_setevent @P1, 10, 17, 1
exec sp_trace_setevent @P1, 10, 18, 1
exec sp_trace_setevent @P1, 10, 27, 1
exec sp_trace_setevent @P1, 12, 1, 1
exec sp_trace_setevent @P1, 12, 6, 1
exec sp_trace_setevent @P1, 12, 9, 1
exec sp_trace_setevent @P1, 12, 10, 1
exec sp_trace_setevent @P1, 12, 11, 1
exec sp_trace_setevent @P1, 12, 12, 1
exec sp_trace_setevent @P1, 12, 13, 1
exec sp_trace_setevent @P1, 12, 14, 1
exec sp_trace_setevent @P1, 12, 16, 1
exec sp_trace_setevent @P1, 12, 17, 1
exec sp_trace_setevent @P1, 12, 18, 1
exec sp_trace_setevent @P1, 12, 27, 1
exec sp_trace_setevent @P1, 14, 1, 1
exec sp_trace_setevent @P1, 14, 6, 1
exec sp_trace_setevent @P1, 14, 9, 1
exec sp_trace_setevent @P1, 14, 10, 1
exec sp_trace_setevent @P1, 14, 11, 1
exec sp_trace_setevent @P1, 14, 12, 1
exec sp_trace_setevent @P1, 14, 13, 1
exec sp_trace_setevent @P1, 14, 14, 1
exec sp_trace_setevent @P1, 14, 16, 1
exec sp_trace_setevent @P1, 14, 17, 1
exec sp_trace_setevent @P1, 14, 18, 1
exec sp_trace_setevent @P1, 14, 27, 1
exec sp_trace_setevent @P1, 15, 1, 1
exec sp_trace_setevent @P1, 15, 6, 1
exec sp_trace_setevent @P1, 15, 9, 1
exec sp_trace_setevent @P1, 15, 10, 1
exec sp_trace_setevent @P1, 15, 11, 1
exec sp_trace_setevent @P1, 15, 12, 1
exec sp_trace_setevent @P1, 15, 13, 1
exec sp_trace_setevent @P1, 15, 14, 1
exec sp_trace_setevent @P1, 15, 16, 1
exec sp_trace_setevent @P1, 15, 17, 1
exec sp_trace_setevent @P1, 15, 18, 1
exec sp_trace_setevent @P1, 15, 27, 1
exec sp_trace_setevent @P1, 17, 1, 1
exec sp_trace_setevent @P1, 17, 6, 1
exec sp_trace_setevent @P1, 17, 9, 1
exec sp_trace_setevent @P1, 17, 10, 1
exec sp_trace_setevent @P1, 17, 11, 1
exec sp_trace_setevent @P1, 17, 12, 1
exec sp_trace_setevent @P1, 17, 13, 1
exec sp_trace_setevent @P1, 17, 14, 1
exec sp_trace_setevent @P1, 17, 16, 1
exec sp_trace_setevent @P1, 17, 17, 1
exec sp_trace_setevent @P1, 17, 18, 1
exec sp_trace_setevent @P1, 17, 27, 1
exec sp_trace_setstatus @P1, 1
end
GO
exec sp_procoption @ProcName= 'usp_SQLProfilerStandard' , @OptionName =  'startup' , @OptionValue = 'on'
GO
exec proc usp_SQLProfilerStandard
GO
0
 
SkipFireAuthor Commented:
VMontalvao  - It's a big security issue to leave soemone logged in on a machine when they have the rights to do anything, especially admin things like running trace.  Plus Query Analyzer requires too much user stuff so I doubt it would work right for what I am needing.

ispaleny - Is it possible for me to set up filters?  I want it to not show SQL Server and SQL Agent tasks, but to show the .NET Data Provider tasks (that's how the web app I want to track shows up).  I do like your suggestion so far though, it looks like the best option without going to the massive overkill of C2 logging.
0
 
ispalenyCommented:
BOL example:

This example sets three filters on Trace 1. The filters N'SQLT%' and N'MS%' operate on one column (AppName, value 10) using the "LIKE" comparison operator. The filter N'joe' operates on a different column (UserName, value 11) using the "EQUAL" comparison operator.

sp_trace_setfilter  1, 10, 0, 6, N'SQLT%'
sp_trace_setfilter  1, 10, 0, 6, N'MS%'
sp_trace_setfilter  1, 11, 0, 0, N'joe'

And my comment:

You can start 2 SQL Profilers, in the first one change condition "not like SQL Profiler" to "like SQL Profiler" and start trace, in the second one define a desired trace and start it. Now you have all you need.  
0
 
SkipFireAuthor Commented:
Sweet, that's all I needed, thanks.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like I couldn't explain to you SkipFire! :(

ispaleny gave you the solution that I wanted to tell you. The difference is that mines was a SQL job that runs the SP and he placed at startup. They do the same, run the SP that creates the trace. But there's always a user logged it (system user, SQL Server account user, or other) to run the SP.

Cheers
0
 
SkipFireAuthor Commented:
You said SQL Profiler, you didn't say anything about sp_trace stuff.  Sorry.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No problem SkipFire. I know it's my fault because I didn't explain well.

sp_trace it's a SQL Server stored procedure that SQL Profiler use.
SQL Profiler is just a friendly front-end to activate the traces.

Regards
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now