CODEWITHSUNDEEP
sql-serversql-server-profiler
Christian29
Christian29

Reputation: 21

Stop SQL Tracing

my MSSQL DB creates every second two SQL Server Profil - tace data file:

Screenshot

How can I stop that, because we don't want to tracing something and it files my memory?

My SELECT * FROM sys.traces is empty. If I execute SELECT * FROM sys.server_event_sessions, the result looks like the following Screenshot:

enter image description here

Additionally, I execute the following command:

SELECT s.name, CAST(st.target_data AS XML).value('(/EventFileTarget/File/@name)[1]', 'NVARCHAR(100)')
FROM sys.dm_xe_sessions s
  INNER JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address  
WHERE target_name = 'event_file

The result looks like this: enter image description here

Next, I open the system_health-file and get this displayed: enter image description here

The hkenginexesession-file is empty.

Upvotes: 1

Views: 420

Answers (1)

Meyssam Toluie
Meyssam Toluie

Reputation: 1071

Do you have enough permission to see trace list?

The visibility of the metadata in catalog views is limited to securable that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

If the list is still empty with sysadmin premission then check if these files are created by Extended events:

USING SSMS:

Server > Management > Session

USING TSQL

SELECT s.name, CAST(st.target_data AS X`enter code here`ML).value('(/EventFileTarget/File/@name)[1]', 'NVARCHAR(100)')
FROM sys.dm_xe_sessions s
  INNER JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address  
WHERE target_name = 'event_file'

The above query return session name and filename of outputs.

Upvotes: 0

Related Questions