SQL Profiler - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Profiler

Author

Topic

lorellana
Starting Member

25 Posts

Posted - 2011-08-18 : 18:26:55

I would like to know if there is way to create a trace without having the window open of SQL Profiler on the server, given that I have saved traces to a file.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 18:41:08

see http://msdn.microsoft.com/en-us/library/ms191006.aspx

KH
[spoiler]Time is always against us[/spoiler]

lorellana
Starting Member

25 Posts

Posted - 2011-08-19 : 09:50:51

I know how to create traces in SQL Server. I want to know how trace can work without have open the window SQL Profiler, because in my server, some people close this windows and trace not continue registering.
In other words, I would like know how to create in background or as a service.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-19 : 10:15:47

This is server side trace. You don't need to use SQL Profiler

KH
[spoiler]Time is always against us[/spoiler]

lorellana
Starting Member

25 Posts

Posted - 2011-08-19 : 11:29:00

I create a trace based on a script, I run it in SQL Server Management Studio, it creates the file 'trace.trc' in D:\temp, if I execute 'SELECT * FROM sys.traces' I see, id = 2, status = 1, max_size = 250, stop_time = NULL, max_files = 1, is_rowset = 1, is_rollover = 0, is_shutdown = 0, is_default = 0, among other fields.

If I close the SQL Server Management Studio and SQL Server Profiler, The size of file trace.trc does not increase. But if I open the file, its size increases, I see that there are some records of the trace, and get a message "Failed to read file data. File May be corrupt or wrong it May Have A data format"

I close, and the size of the trace does not increase, then reopened, and increases the size and see more records. (48,408 rows) If I click 'Refresh Window' (63,273 rows) If I click again 'Refresh Window' (66 465 rows)

Then, there would have to have the window open of SQL Server Profiler and be refreshing to take information. I wonder if there is way this file is updated without having the window open SQL Server Profiler.

So I was wondering if you tell me, how can I create a trace as background or as a service, or how to increase the size of the file without opening it from SQL Profiler.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-19 : 11:43:39

quote:
But if I open the file

How do you open the file ? Is the trace still on ? You can read the trace using fn_trace_gettable()

quote:
how can I create a trace as background or as a service

you create the trace using sp_trace_create. The trace is not execute as a separate service it is handle by SQL Server Engine
See How to: Create a Trace (Transact-SQL) in the earlier link that i posted.

KH
[spoiler]Time is always against us[/spoiler]

lorellana
Starting Member

25 Posts

Posted - 2011-08-19 : 14:07:47

Yes, I can read the information using:
SELECT * FROM ::fn_trace_gettable('C:\temp\trace.trc', 2)

And, I created the trace using:
sp_trace_create

lorellana
Starting Member

25 Posts

Posted - 2011-08-19 : 14:09:24

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 250

exec @rc = sp_trace_create @TraceID output, 0, N'D:\temp\trace', @maxfilesize, NULL

if (@rc != 0) goto error

declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 22, 7, @on
exec sp_trace_setevent @TraceID, 22, 31, @on
exec sp_trace_setevent @TraceID, 22, 8, @on
exec sp_trace_setevent @TraceID, 22, 64, @on
exec sp_trace_setevent @TraceID, 22, 1, @on
exec sp_trace_setevent @TraceID, 22, 9, @on
exec sp_trace_setevent @TraceID, 22, 41, @on
exec sp_trace_setevent @TraceID, 22, 49, @on
exec sp_trace_setevent @TraceID, 22, 6, @on
exec sp_trace_setevent @TraceID, 22, 10, @on
exec sp_trace_setevent @TraceID, 22, 14, @on
exec sp_trace_setevent @TraceID, 22, 26, @on
exec sp_trace_setevent @TraceID, 22, 3, @on
exec sp_trace_setevent @TraceID, 22, 11, @on
exec sp_trace_setevent @TraceID, 22, 35, @on
exec sp_trace_setevent @TraceID, 22, 51, @on
exec sp_trace_setevent @TraceID, 22, 4, @on
exec sp_trace_setevent @TraceID, 22, 12, @on
exec sp_trace_setevent @TraceID, 22, 20, @on
exec sp_trace_setevent @TraceID, 22, 60, @on
exec sp_trace_setevent @TraceID, 33, 7, @on
exec sp_trace_setevent @TraceID, 33, 31, @on
exec sp_trace_setevent @TraceID, 33, 8, @on
exec sp_trace_setevent @TraceID, 33, 64, @on
exec sp_trace_setevent @TraceID, 33, 1, @on
exec sp_trace_setevent @TraceID, 33, 9, @on
exec sp_trace_setevent @TraceID, 33, 41, @on
exec sp_trace_setevent @TraceID, 33, 49, @on
exec sp_trace_setevent @TraceID, 33, 6, @on
exec sp_trace_setevent @TraceID, 33, 10, @on
exec sp_trace_setevent @TraceID, 33, 14, @on
exec sp_trace_setevent @TraceID, 33, 26, @on
exec sp_trace_setevent @TraceID, 33, 30, @on
exec sp_trace_setevent @TraceID, 33, 50, @on
exec sp_trace_setevent @TraceID, 33, 3, @on
exec sp_trace_setevent @TraceID, 33, 11, @on
exec sp_trace_setevent @TraceID, 33, 35, @on
exec sp_trace_setevent @TraceID, 33, 51, @on
exec sp_trace_setevent @TraceID, 33, 4, @on
exec sp_trace_setevent @TraceID, 33, 12, @on
exec sp_trace_setevent @TraceID, 33, 20, @on
exec sp_trace_setevent @TraceID, 33, 60, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 10, 64, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 41, @on
exec sp_trace_setevent @TraceID, 10, 49, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 50, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 51, @on
exec sp_trace_setevent @TraceID, 10, 4, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 60, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 11, 7, @on
exec sp_trace_setevent @TraceID, 11, 8, @on
exec sp_trace_setevent @TraceID, 11, 64, @on
exec sp_trace_setevent @TraceID, 11, 1, @on
exec sp_trace_setevent @TraceID, 11, 9, @on
exec sp_trace_setevent @TraceID, 11, 41, @on
exec sp_trace_setevent @TraceID, 11, 49, @on
exec sp_trace_setevent @TraceID, 11, 2, @on
exec sp_trace_setevent @TraceID, 11, 6, @on
exec sp_trace_setevent @TraceID, 11, 10, @on
exec sp_trace_setevent @TraceID, 11, 14, @on
exec sp_trace_setevent @TraceID, 11, 26, @on
exec sp_trace_setevent @TraceID, 11, 34, @on
exec sp_trace_setevent @TraceID, 11, 50, @on
exec sp_trace_setevent @TraceID, 11, 3, @on
exec sp_trace_setevent @TraceID, 11, 11, @on
exec sp_trace_setevent @TraceID, 11, 35, @on
exec sp_trace_setevent @TraceID, 11, 51, @on
exec sp_trace_setevent @TraceID, 11, 4, @on
exec sp_trace_setevent @TraceID, 11, 12, @on
exec sp_trace_setevent @TraceID, 11, 60, @on
exec sp_trace_setevent @TraceID, 43, 7, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 48, @on
exec sp_trace_setevent @TraceID, 43, 64, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 41, @on
exec sp_trace_setevent @TraceID, 43, 49, @on
exec sp_trace_setevent @TraceID, 43, 2, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 34, @on
exec sp_trace_setevent @TraceID, 43, 50, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 35, @on
exec sp_trace_setevent @TraceID, 43, 51, @on
exec sp_trace_setevent @TraceID, 43, 4, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 28, @on
exec sp_trace_setevent @TraceID, 43, 60, @on
exec sp_trace_setevent @TraceID, 43, 5, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 29, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 22, @on
exec sp_trace_setevent @TraceID, 43, 62, @on
exec sp_trace_setevent @TraceID, 42, 7, @on
exec sp_trace_setevent @TraceID, 42, 8, @on
exec sp_trace_setevent @TraceID, 42, 64, @on
exec sp_trace_setevent @TraceID, 42, 1, @on
exec sp_trace_setevent @TraceID, 42, 9, @on
exec sp_trace_setevent @TraceID, 42, 41, @on
exec sp_trace_setevent @TraceID, 42, 49, @on
exec sp_trace_setevent @TraceID, 42, 2, @on
exec sp_trace_setevent @TraceID, 42, 6, @on
exec sp_trace_setevent @TraceID, 42, 10, @on
exec sp_trace_setevent @TraceID, 42, 14, @on
exec sp_trace_setevent @TraceID, 42, 22, @on
exec sp_trace_setevent @TraceID, 42, 26, @on
exec sp_trace_setevent @TraceID, 42, 34, @on
exec sp_trace_setevent @TraceID, 42, 50, @on
exec sp_trace_setevent @TraceID, 42, 62, @on
exec sp_trace_setevent @TraceID, 42, 3, @on
exec sp_trace_setevent @TraceID, 42, 11, @on
exec sp_trace_setevent @TraceID, 42, 35, @on
exec sp_trace_setevent @TraceID, 42, 51, @on
exec sp_trace_setevent @TraceID, 42, 4, @on
exec sp_trace_setevent @TraceID, 42, 12, @on
exec sp_trace_setevent @TraceID, 42, 28, @on
exec sp_trace_setevent @TraceID, 42, 60, @on
exec sp_trace_setevent @TraceID, 42, 5, @on
exec sp_trace_setevent @TraceID, 42, 29, @on
exec sp_trace_setevent @TraceID, 45, 7, @on
exec sp_trace_setevent @TraceID, 45, 55, @on
exec sp_trace_setevent @TraceID, 45, 8, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 48, @on
exec sp_trace_setevent @TraceID, 45, 64, @on
exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 25, @on
exec sp_trace_setevent @TraceID, 45, 41, @on
exec sp_trace_setevent @TraceID, 45, 49, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 18, @on
exec sp_trace_setevent @TraceID, 45, 26, @on
exec sp_trace_setevent @TraceID, 45, 34, @on
exec sp_trace_setevent @TraceID, 45, 50, @on
exec sp_trace_setevent @TraceID, 45, 3, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 35, @on
exec sp_trace_setevent @TraceID, 45, 51, @on
exec sp_trace_setevent @TraceID, 45, 4, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 28, @on
exec sp_trace_setevent @TraceID, 45, 60, @on
exec sp_trace_setevent @TraceID, 45, 5, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 29, @on
exec sp_trace_setevent @TraceID, 45, 61, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 22, @on
exec sp_trace_setevent @TraceID, 45, 62, @on
exec sp_trace_setevent @TraceID, 45, 15, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 48, @on
exec sp_trace_setevent @TraceID, 12, 64, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 41, @on
exec sp_trace_setevent @TraceID, 12, 49, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 50, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 51, @on
exec sp_trace_setevent @TraceID, 12, 4, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 60, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 4, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 60, @on
exec sp_trace_setevent @TraceID, 13, 64, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 41, @on
exec sp_trace_setevent @TraceID, 13, 49, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 50, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 51, @on

declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - d94edef3-311b-4a5c-9b08-de93bd5fd372'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

lorellana
Starting Member

25 Posts

Posted - 2011-08-19 : 14:12:27

I want to create the trace as a service or as background, for not to have raised the SQL Profiler or the Management.

lorellana
Starting Member

25 Posts

Posted - 2011-08-19 : 16:56:05

Please, someone help me how create a trace in background, in other words that I can see the file of size *.trc increase by himself as SQL Server saves traces.

lorellana
Starting Member

25 Posts

Posted - 2011-08-19 : 17:05:27

Alguien sabe cómo crear una traza en background?

Es decir, que se vea que va aumentando el tamaño del archivo *.trc a medida que el SQL Server va guardando las trazas en el archivo.

Yo creo la traza con sp_trace_create, pero no se aumenta el tamaño, a menos que yo abra con SELECT * FROM ::fn_trace_gettable('D:\temp\trace.trc', 2) o con el SQL Profiler.

Por favor, alguien me puede ayudar? Les agradezco su ayuda.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-19 : 23:14:39

This IS a background trace. Once you have created the trace with sp_trace_create, you can close off your SSMS. And you don't need to use SQL Profiler to access to the trace file. You can use fn_trace_gettable to do that.

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-19 : 23:56:49

Don't worry about the file size of an active trace. You won't see the file size increase until the trace stops OR it rolls to the next file (if you set it to do that).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lorellana
Starting Member

25 Posts

Posted - 2011-08-31 : 12:56:05

Ok, thank you. It's true.

lorellana
Starting Member

25 Posts

Posted - 2011-08-31 : 13:03:43

Sorry, Can you give me a link or can indicate which command I use to save the trace directly to a database table and no longer in a file.
Thanks in advance for your help.

If I use SQL Server Profiler,
File --> New Trace --> I chose Save to table, I clic on Run.
I can't close the window of SQL Profiler because the trace stop.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-31 : 13:29:27

SELECT *
INTO trace_table
FROM ::fn_trace_gettable('c:\my_trace.trc', default)

From: http://support.microsoft.com/kb/270599

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lorellana
Starting Member

25 Posts

Posted - 2011-08-31 : 14:03:39

No, so continue generating a file and then the trace must be stopped to save a table. I do not want to generate files but are stored directly in a table, as if it did with SQL Profiler. "File --> New Trace --> Save to table"

lorellana
Starting Member

25 Posts

Posted - 2011-08-31 : 14:16:05

The link http://support.microsoft.com/kb/270599 refers to SQL 2000, in SQL 2005 or 2008, you can load directly into a base?

lorellana
Starting Member

25 Posts

Posted - 2011-08-31 : 14:27:13

http://www.techrepublic.com/article/step-by-step-an-introduction-to-sql-server-profiler/5054787
The same, but instead of loading a file, loaded into a table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-31 : 14:35:29

The link applies to all of them. I'm using it right now on my 2005 system.

I don't know what you mean by "base", but the query I posted loads a trace file into a table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lorellana
Starting Member

25 Posts

Posted - 2011-08-31 : 15:38:38

I dont' want load a trace file into a table. I want save in a table directly without use a file.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources