Problem setting up data collection for a performance problem RRS feed

  • Question

  • We have a performance problem on our production environment: there is a query that is long to display specially on specific records involving a lot of détails. I had the idea to set up data collection to investigate the problem.

    I used the wizard to set up data collection with default settings.

    I had created a database PERF_DW on our test server for that purpose.

    I didn't have any problem with the wizard: it created data collection sets and SQL Server agents tasks and probably many objects. But I don't know where I missed something  but I didnt, have the chance to specify the database I wanted to tune and even when I started data collection, I couldn't figure out where were the reports.

    Then I tought that I did it all wrong so I stopped data collection and dropped the database and tried to deleted the jobs created and I had the following message:

    TITRE : Microsoft SQL Server Management Studio

    Échec de Supprimer pour Travail « collection_set_1_noncached_collect_and_upload ».  (Microsoft.SqlServer.Smo)

    Pour obtenir de l'aide, cliquez sur :


    Une exception s'est produite lors de l'exécution d'une instruction ou d'un lot Transact-SQL. (Microsoft.SqlServer.ConnectionInfo)


    The DELETE statement conflicted with the REFERENCE constraint "FK_syscollector_collection_sets_collection_sysjobs". The conflict occurred in database "msdb", table "dbo.syscollector_collection_sets_internal", column 'collection_job_id'.
    The statement has been terminated. (Microsoft SQL Server, Erreur : 547)

    Pour obtenir de l'aide, cliquez sur :



    Now I understand that the collectors prevent the jobs from being deleted but I can't delete the collectors.

    What should I do?

    Any help is appreciated.

    Thank you for your advice.

    Sylvie P

    Friday, March 13, 2015 3:41 PM


All replies