How can we help you today? How can we help you today?

Changing SQL Server drives causes Monitoring Error

We use Redgate Monitor on a number of servers.   On one server we had to change the data and log drives for SQL Server from D: and E: to E: and F: to match other servers.    This included moving the Master database and all other databases.   

All the databases were moved and everything on the SQL server works, but now we get this monitoring error from Redgate. 

"For target, "5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file", the parameter "filename" passed is invalid. Target parameter at index 0 is invalid"

We think Redgate Monitor is unable to create the extended event session on the sql server because it may be trying to go to the old path on the D: drive that no longer exists.   But other extended events sessions work correctly on the server.   We even removed and deleted all previous data for this server from Redgate Monitor because we thought it might be saving the errorlog path where extended events files are stored.   Still this monitoring error occurs when the server is added back to Redgate Monitor.   

Redgate monitor is capturing data from the server and everything else seems to be working other than this extended event session it is unable to create.    Any ideas if there is a way to see what path Redgate Monitor is trying to use to setup the extended events session or to change it?


mbrlink
0

Comments

2 comments

Sort by
  • Ben_P
    To resolve the error that you are seeing, please connect to the SQL Server instance using SSMS and delete the sql_monitor XE session:
     

     
    Redgate Monitor should automatically recreate the session
    In case this does not exist in the first place or if Redgate Monitor does not recreate it, the following query should help to do this:
     
    DECLARE @target NVARCHAR(50)<br><br>SELECT @target = p.name +'.' + o.name<br>FROM sys.dm_xe_objects o INNER JOIN sys.dm_xe_packages p ON o.package_guid = p.guid<br>WHERE o.object_type = 'target' AND (p.capabilities IS NULL OR p.capabilities <> 1) AND o.name IN ('asynchronous_file_target', 'event_file')<br><br>DECLARE @sessionName NVARCHAR(100) = N'sqlmonitor_session'<br><br>DECLARE @errorLogPath NVARCHAR(1000), @errorLogFolder NVARCHAR(1000)<br>SET @errorLogPath = CAST(SERVERPROPERTY('ErrorLogFileName') AS NVARCHAR(1000))<br>SET @errorLogFolder = LEFT(@errorLogPath,LEN(@errorLogPath) - charindex('\',reverse(@errorLogPath),1) + 1)<br><br>DECLARE @xePath NVARCHAR(1000), @xeWildCard NVARCHAR(1000), @xemWildCard NVARCHAR(1000)<br><br>SET @xePath = @errorLogFolder + @sessionName + '.xel'<br>SET @xeWildCard = @errorLogFolder + @sessionName + '*.xel'<br>SET @xemWildCard = @errorLogFolder + @sessionName + '*.xem'<br><br>DECLARE @sql NVARCHAR(MAX)<br><br>IF NOT EXISTS(SELECT 1 FROM master.sys.server_event_sessions WHERE [name] = @sessionName) BEGIN<br>&nbsp; &nbsp; BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; SET @sql = '<br>&nbsp; &nbsp; &nbsp; &nbsp; CREATE EVENT SESSION [' + @sessionName + '] ON SERVER<br>&nbsp; &nbsp; &nbsp; &nbsp; ADD EVENT sqlserver.xml_deadlock_report<br>&nbsp; &nbsp; &nbsp; &nbsp; ADD TARGET ' + @target + '(SET filename=N''' + @xePath + ''', max_file_size=(5))<br>&nbsp; &nbsp; &nbsp; &nbsp; WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)<br>&nbsp; &nbsp; &nbsp; &nbsp; '<br>&nbsp; &nbsp; &nbsp; &nbsp; EXEC(@sql)<br>&nbsp; &nbsp; END<br>END
     
    Please let us know if this helps.
    Ben_P
    0
  • mbrlink
    Thanks, removing the sqlmonitor_session fixed the issue.
    mbrlink
    0

Add comment

Please sign in to leave a comment.