1

I've recently set some traces and extended events up and running in SQL on our new virtual server to show the access that users have to each database and whether they have logged in recently, and have set the file to save as a physical file on the server rather than writing to a SQL table to save resource. I've set the traces as jobs running at 8am each morning with a 12-hour delay so we can record as much information as possible.

Our IT department ideally don't want anything other than the OS on the C drive of the virtual server, so I'd like to be able to write the trace from my SQL script either to a different partition or to another server altogether.

I have attempted to insert a direct path to a different server within my code and have entered a different partition to C, however unless I write the trace/extended event files to the C drive I get an error message.

CREATE EVENT SESSION [LoginTraceTest] ON SERVER    
ADD EVENT sqlserver.existing_connection(SET collect_database_name= 
(1),collect_options_text=(1)      
ACTION(package0.event_sequence,sqlos.task_time,sqlserver.client_pid,
sqlserver.database_id,sqlserver.
database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_nt_username,
sqlserver.sql_text,sqlserver.username)),  
ADD EVENT sqlserver.login(SET collect_database_name= 
(1),collect_options_text=(1)      
ACTION(package0.event_sequence,sqlos.task_time,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.
session_nt_username,sqlserver.sql_text,sqlserver.username)      )   
ADD TARGET package0.asynchronous_file_target    (      
SET FILENAME = N'\\SERVER1\testFolder\LoginTrace.xel',      
METADATAFILE = N'\\SERVER1\testFolder\LoginTrace.xem'      );

The error I receive is this:

Msg 25641, Level 16, State 0, Line 6 For target, "package0.asynchronous_file_target", the parameter "filename" passed is invalid. Target parameter at index 0 is invalid

If I change it to another partition rather than a different server:

SET FILENAME = N'D:\Traces\LoginTrace\LoginTrace.xel',      
METADATAFILE = N'D:\Traces\LoginTrace\LoginTrace.xem'      );

SQL server states that the command completed successfully, but the file isn't written to the partition.

Any ideas please as to what I can do to write the files to another partition or server?

Chris Sumner
  • 11
  • 4
  • No file will be created until the session is actually started (with `ALTER EVENT SESSION [LoginTraceTest] ON SERVER STATE = START`). In my tests, file shares *are* supported, provided the path resolves. Double check that the share `\\SERVER1\testfolder` is visible to the account SQL Server runs under (not your own account). – Jeroen Mostert May 23 '19 at 11:30
  • Thanks for your help, looks like I've managed to solve the issue with your help. I also realised I had to map the other server as a drive on SQL as per this article: https://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-in-ssms/ It'll keep our IT department happy that I can save there now - thanks again for your help! – Chris Sumner May 23 '19 at 13:49

0 Answers0