locked
Home » SQL Server 2012 » SQL 2012 - General » Collection Set Failure Collection Set Failure RRS feed

Answers

  • I personally use my own custom scripts, Adam Machanic's sp_WhoIsActive, some of Glenn Berry's diagnostic scripts, some of Paul Randal's scripts and so on (you may also want to take a look at Brent Ozar's scripts) and most people out there do so. The ones with less experience or with a complex environment may prefer some third party monitoring tools from companies such as SQL Sentry and Idera.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    • Marked as answer by KhurramKZ Friday, March 10, 2017 5:29 AM
    Wednesday, March 8, 2017 6:32 AM
    Answerer

All replies

  • Hello KhurramKZ,

    My guess is that, your collection datawarehouse database is located on another instance of SQL Server and (obviously) your collection set SQL Server agent jobs are executed using a local virtual account and they are failing to access the remote database.

    Or the datawarehouse database is located on the same instance where these jobs are executed, but NT SERVICE\SQLSERVERAGENT virtual account does not have necessary permissions to access the database and execute the jobs.

    Can you please re-consider your situation from this point of view?


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    • Proposed as answer by Teige Gao Monday, March 6, 2017 8:27 AM
    Saturday, March 4, 2017 8:48 AM
    Answerer
  • Hi Ekrem,

    They're in same instance.

    I've not changed anything away from default permissions set on Agent virtual account. Do I need to assign some permissions on it please?

    Thanks


    • Edited by KhurramKZ Tuesday, March 7, 2017 10:12 AM
    Tuesday, March 7, 2017 8:38 AM
  • Here's the screenshot of error messages. It doesn't feel like before of permissions?

    I've googled to find solution for this but I'm not coming across something that helps me fix this.

    I'm using SQL Server 2014. I'm able to get Disk Usage statistics since it is working on/off now, but other two are completely blank.

    Thanks

    Tuesday, March 7, 2017 10:12 AM
  • The error message says the resource file is corrupted. At this point, I'm not really sure what a data collector resource file is, but somehow I think you need to rebuild it somehow. Have you tried recreating the whole thing, I mean the data collector configuration with the datawarehouse database of the data collector?

    I searched the official documentation, but could end up with some information about what a data collector resource file could be.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    Tuesday, March 7, 2017 11:38 AM
    Answerer
  • Phew, it is difficult to remove DCW!! I've recreated everything and still doesn't show data, maybe needs to wait day or so?

    By the way, I'm new to SQL Server and I feel not many people out there use MDW. What is the other/better alternative to these reports??
    I see Dashboard Performance popping up somewhere, is it a good alternative? or some other custom report etc. please?

    Thanks

    Wednesday, March 8, 2017 6:25 AM
  • I personally use my own custom scripts, Adam Machanic's sp_WhoIsActive, some of Glenn Berry's diagnostic scripts, some of Paul Randal's scripts and so on (you may also want to take a look at Brent Ozar's scripts) and most people out there do so. The ones with less experience or with a complex environment may prefer some third party monitoring tools from companies such as SQL Sentry and Idera.


    http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy

    • Marked as answer by KhurramKZ Friday, March 10, 2017 5:29 AM
    Wednesday, March 8, 2017 6:32 AM
    Answerer
  • Thanks Ekrem.
    Friday, March 10, 2017 5:29 AM