none
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 : http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Supprimer+Job&LinkId=20476

    ------------------------------
    INFORMATIONS SUPPLÉMENTAIRES :

    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 : http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3128&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

    ------------------------------
    BOUTONS :

    OK
    ------------------------------

    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

Answers

All replies