Capture the workload Error

[JakubKad]

I am feeling like a common visitor at this point.

We have this error: Before pasting it:

**1. We checked that no instance of workload is running

2. We have all permissions (tried few accounts with different permissions)
3. Directory is in exceptions
4. Both files are good, they ran before, this is the first time that we got this error**

2023-05-01 01:20:06.7604 - Info - SqlWorkload.Program : Reading configuration from 'd:\WorkloadTools\Workload-Prod.json' 2023-05-01 01:20:06.8852 - Info - WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener : Reading Extended Events session definition from C:\Program Files\WorkloadTools\Listener\ExtendedEvents\sqlworkload.sql 2023-05-01 01:20:06.9009 - Error - WorkloadTools.WorkloadController : Uncaught Exception 2023-05-01 01:20:06.9009 - Error - WorkloadTools.WorkloadController : The event, "sqlserver.attention", can not be added to an event session that specifies no event loss. Cannot alter the event session 'sqlworkload', because it does not exist or you do not have permission. 2023-05-01 01:20:06.9477 - Error - WorkloadTools.WorkloadController :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener.Initialize() in C:\github\WorkloadTools\WorkloadTools\Listener\ExtendedEvents\ExtendedEventsWorkloadListener.cs:line 135    at WorkloadTools.WorkloadController.Run() in C:\github\WorkloadTools\WorkloadTools\WorkloadController.cs:line 42 2023-05-01 01:20:06.9477 - Info - SqlWorkload.Program : Controller stopped. 2023-05-01 01:20:07.0884 - Info - WorkloadTools.Consumer.WorkloadFile.WorkloadFileWriterConsumer : Closing the connection to the output file 2023-05-01 01:20:07.0884 - Info - WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener : Disposing ExtendedEventsWorkloadListener. 2023-05-01 01:20:07.0884 - Warn - WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener : Error disposing ExtendedEventWorkloadListener: Object reference not set to an instance of an object. 2023-05-01 01:20:07.0884 - Info - WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener : Extended Events session [sqlworkload] stopped successfully. 2023-05-01 01:20:07.0884 - Info - SqlWorkload.Program : Controller disposed. 2023-05-01 01:21:11.6626 - Info - SqlWorkload.Program : Reading configuration from 'd:\WorkloadTools\Workload-Prod.json' 2023-05-01 01:21:11.7564 - Info - WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener : Reading Extended Events session definition from C:\Program Files\WorkloadTools\Listener\ExtendedEvents\sqlworkload.sql 2023-05-01 01:21:11.7564 - Error - WorkloadTools.WorkloadController : Uncaught Exception 2023-05-01 01:21:11.7564 - Error - WorkloadTools.WorkloadController : The event, "sqlserver.attention", can not be added to an event session that specifies no event loss. Cannot alter the event session 'sqlworkload', because it does not exist or you do not have permission. 2023-05-01 01:21:11.7720 - Error - WorkloadTools.WorkloadController :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()    at WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener.Initialize() in C:\github\WorkloadTools\WorkloadTools\Listener\ExtendedEvents\ExtendedEventsWorkloadListener.cs:line 135    at WorkloadTools.WorkloadController.Run() in C:\github\WorkloadTools\WorkloadTools\WorkloadController.cs:line 42 2023-05-01 01:21:11.7720 - Info - SqlWorkload.Program : Controller stopped. 2023-05-01 01:21:11.7720 - Info - WorkloadTools.Consumer.WorkloadFile.WorkloadFileWriterConsumer : Closing the connection to the output file 2023-05-01 01:21:11.7720 - Info - WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener : Disposing ExtendedEventsWorkloadListener. 2023-05-01 01:21:11.7720 - Warn - WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener : Error disposing ExtendedEventWorkloadListener: Object reference not set to an instance of an object. 2023-05-01 01:21:11.7720 - Info - WorkloadTools.Listener.ExtendedEvents.ExtendedEventsWorkloadListener : Extended Events session [sqlworkload] stopped successfully. 2023-05-01 01:21:11.7720 - Info - SqlWorkload.Program : Controller disposed.

Capture JSON:

{ "Controller": {

    "Listener":
    {
        "__type": "ExtendedEventsWorkloadListener",
        "ConnectionInfo":
        {
            "ServerName": "ServerName",
            "UserName": "",
            "Password": ""

        },
        "TimeoutMinutes": 480,
        "DatabaseFilter": "DB"
    },

    "Consumers":
    [

        {
            "__type": "WorkloadFileWriterConsumer",
            "OutputFile": "D:\\WorkloadTools\\File_Trace1.sqlite"
        }
    ]
}

}

CMD File:

"%programfiles%\WorkloadTools\sqlworkload.exe" --File "Workload-Prod.json"

Have no idea what to do with it. Restarting server did not help, reinstall did not help, shutting down antivirus did not help. I have checked all files but i do not see any problem, as said, this is first encounter with this error. I would like to ask for some guidance. The last time we ran this was it went as expected (start of April). Now it does not work, so maybe some Microsoft patches of server could interfere with this.

[JakubKad]

The patch after successful run: https://support.microsoft.com/en-gb/topic/april-11-2023-kb5025230-os-build-20348-1668-28a5446e-6389-4a5b-ae3f-e942a604f2d3

That might cause some problems, but I am not sure.

[spaghettidba]

What is your SQL Server @@version?

[JakubKad]

SQL Server version is => SQL Server 2019 CU20 15.0.4312.2

[JakubKad]

We have changed the EVENT_RETENTION_MODE = NO_EVENT_LOSS to ALLOW_SINGLE_EVENT_LOSS and it will connect. I am just wondering why? We had not changed any files to this point.

[spaghettidba]

I'm sorry, I didn't have the time to investigate this problem. I will reopen this temporarily to see if I can understand what is going on

[hakob14]

Hi I want to use this tool too and I have the same problem and I can't understand how to resolve that question. Do you have a solution for this?

[JakubKad]

Hi I want to use this tool too and I have the same problem and I can't understand how to resolve that question. Do you have a solution for this?

Navigate to your installation folder of WorkloadTools (C:/ProgramFiles/WorkloadTools) and i think it is in one of the subfolders, there you will find .sql file and almost down there is EVENT_RETENTION_MODE, change the value to ALLOW_SINGLE_EVENT_LOSS (more on this here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-event-session-transact-sql?view=sql-server-ver16)

[hakob14]

Thank you so much

[hakob14]

Hi colleagues. I have a problem with workloadtools. I want to except the capturing of system databases. How can I organize it? Warn - WorkloadTools.Consumer.Replay.ReplayWorker : Worker [234] - Sequence[1921] - Error: Could not find stored procedure '#am_generate_waitstats'.

Thanks in advance.

[spaghettidba]

Sorry, I'm not sure what's your problem. Can you please open a new issue and provide more context? Thank you

[hakob14]

I capture the trace from my server and replay it on the same server. but I have some errors about the temporary objects. So I want to capture traces without system databases.

[JakubKad]

I capture the trace from my server and replay it on the same server. but I have some errors about the temporary objects. So I want to capture traces without system databases.

In your json file for capture, type this parameter: { // This section is fixed "Controller": {

    // The Listener section describes how to capture the events.
    // There is always one listener and zero or more consumers
    "Listener":
    {
        // The main parameter here is the class type of the Listener
        // At the moment, four Listener types are supported
        // - ExtendedEventsWorkloadListener
        // - SqlTraceWorkloadListener
        // - ProfilerWorkloadListener
        // - FileWorkloadListener
        "__type": "ExtendedEventsWorkloadListener",

        // Each Listener type has its own set of Properties
        // that you can set on the .JSON file
        // See the documentation of each listener type
        // for the list and description of the supported Properties.

        // The ConnectionInfo describes how to connect the Listener
        // Many (but not all) listener types support this property
        "ConnectionInfo":
        {
            "ServerName": "SQLDEMO\\SQL2014",
            // If you omit the UserName/Password, Windows authentication
            // will be used
            "UserName": "sa",
            "Password": "P4$$w0rd!"
        },

        // Filters for the workload
        // These are not mandatory, you can omit them
        // if you don't need to filter.
        // Prepend the '^' character to exclude the value
        **### "DatabaseFilter": "DS3",** ----------------------->this one

        You can find it here: https://github.com/spaghettidba/WorkloadTools/wiki/SqlWorkload

[hakob14]

Hi colleagues. Thank you for your answers. I have some problems. When I replay my captured traces on the server, this really runs queries on the server which is not normal for me. I just want to have the same traffic on the test server from the production server.

[spaghettidba]

When I replay my captured traces on the server, this really runs queries on the server which is not normal for me.

Not sure what you mean. It's the whole point of replaying a workload. What would you expect instead?

I just want to have the same traffic on the test server from the production server.

That's what the replay does.

[hakob14]

I want to test the performance of the testing server without running any changes and I want to try the performance of new queries in the testing server before deploying to the production server.

[spaghettidba]

Not sure what's your question here, sorry.