locked
After adding seperate performance collections to MAP 9.1 database, the database has become corrupted RRS feed

  • Question

  • Hi,

    I have installed MAP 9.1(9.1.265.0) on my notebook, with Windows 7 Enterprise. The inventory of our environment was successfull and I have successfully added some performance collections to the database.

    First I ran the performance collection for one hour, then added a performance collection of one week. this was okay.

    Then I waited one week and added another collection of about five days. That collection would not stop: It was scheduled to run between 2014-07-28 12:49:31 until 2014-08-01 05:00:04. But it was still running at 2014-08-01 05:30:25. I waited for a little bit longer, but the collection kept on running according to the status screen.

    So I cancelled out of the collection at 2014-08-01 05:45. In the performance data it said that the colection ran from Jul 14 2014 08:04 AM until Aug 1 2014 4:57AM. So that looked okay.

    But now, when I try to Get the performance metrics data from MAP, it states that I have to do a "Refressh Assessment", because I problably have cancelled out of a collection. This "Refresh Assessment" wil run for about an hour and than completes with a message "Failed"

    I get these errors in the MapToolkit.log

    <2014-08-05 05:14:51.09 AssessInventoryWorker@StoredProcAssessment,I> RunAssessment() - [Perf] [[Perf_Assessment].[ClearPerfdata]] : 125 ms
    <2014-08-05 05:14:56.13 AssessInventoryWorker@StoredProcAssessment,I> RunAssessment() - [Perf] [[Perf_Assessment].[CreateTimeIntervals]] : 5039 ms
    <2014-08-05 05:22:47.76 AssessInventoryWorker@StoredProcAssessment,I> RunAssessment() - [Perf] [[Perf_Assessment].[CreateMetricsPerTimeInterval]] : 471591 ms
    <2014-08-05 05:52:54.66 AssessInventoryWorker@DataAccessCore,W> DoWorkInTransaction<T>() - Caught InvalidOperationException trying to roll back the transaction: This SqlTransaction has completed; it is no longer usable.
    <2014-08-05 05:52:54.79 AssessInventoryWorker@DataAccessCore,W> DoWorkInTransaction<T>() - Caught a SQL transaction timeout exception. Will retry 3 more time(s). Retrying in 5000 milliseconds.
    <2014-08-05 05:53:15.86 AssessInventoryWorker@DataAccessCore,W> OpenConnection() - Caught a SqlException trying to connect to the database.  Will retry connection 3 more time(s).  Retrying in 5000 milliseconds.
    <2014-08-05 05:53:20.99 AssessInventoryWorker@DataAccessCore,W> OpenConnection() - Caught a SqlException trying to connect to the database.  Will retry connection 2 more time(s).  Retrying in 10000 milliseconds.
    <2014-08-05 05:53:45.00 AssessInventoryWorker@DataAccessCore,W> OpenConnection() - Caught a SqlException trying to connect to the database.  Will retry connection 1 more time(s).  Retrying in 15000 milliseconds.
    <2014-08-05 06:24:03.69 AssessInventoryWorker@DataAccessCore,W> DoWorkInTransaction<T>() - Caught a SQL transaction timeout exception. Will retry 2 more time(s). Retrying in 10000 milliseconds.
    <2014-08-05 06:54:13.91 AssessInventoryWorker@DataAccessCore,W> DoWorkInTransaction<T>() - Caught a SQL transaction timeout exception. Will retry 1 more time(s). Retrying in 15000 milliseconds.
    <2014-08-05 07:24:28.99 AssessInventoryWorker@Analyzer,E> RunAssessments() - Assessment threw an exception:
    <2014-08-05 07:24:29.03 AssessInventoryWorker@AssessInventoryWorker,I> AssessmentCompletedEventHandler: Assessment completed event.
    <2014-08-05 07:24:29.09 AssessInventoryWorker@TaskProcessor,I> WorkerCompleted: Worker: 'AssessInventoryWorker'
    <2014-08-05 07:24:29.15 TID-16@TaskProcessor,I> Run: Completed. Status: Failed

    Is there maybe a restriction to the intervalls of adding performance collection data, or is there something else I am doing wrong?

    (I made a backup of the database after the first week of performance data, that database is still usable, so I can try to add more performance collections to that version of the database)

    I hope someone has an idea what is going on.

    Thanks!

    Tuesday, August 5, 2014 6:33 AM

Answers

  • Time between isn't the problem. If you look in the log file, SQL is timing out. I think the problem is machine resources and time related. After the performance data collection has run for the predefined amount of time, MAP has SQL execute various assessments on the data to aggregate the raw data into something MAP can use. The more raw data that exists, the longer SQL will take and the more CPU and memory resources SQL will need.

    I would recommend that you have at least 4 cores or vCPU's and 6-8 GB of memory dedicated to the machine on which MAP is running. I would also follow the directions in this Wiki article to increase the timeout in MAP so that MAP will give SQL the time it needs to complete the job. http://social.technet.microsoft.com/wiki/contents/articles/10397.map-toolkit-increasing-the-sql-database-timeout-value.aspx



    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. Please VOTE as HELPFUL if the post helps you. This can be beneficial to other community members reading the thread.

    • Marked as answer by AndrevdL Thursday, August 7, 2014 5:07 AM
    Wednesday, August 6, 2014 6:01 PM

All replies

  • How big is the database currently? To check, look for the .mdf file with the name you gave your database in this folder: "%LocalAppData%\Microsoft\Microsoft SQL Server Local DB\Instances\MAPToolkit"



    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. Please VOTE as HELPFUL if the post helps you. This can be beneficial to other community members reading the thread.

    Tuesday, August 5, 2014 5:36 PM
  • The size of de database is 1.85 GB (1,994,457,088 bytes)

    My PC has 4.00 GB internal memory, it is 32bit Windows 7.

    I did a restore from my backup and try to add another hour of performance data, this collection also stayed running after the "scheduled until" time, so I had to cancel. And this time the "Refresh Assessment" also failed in the end.

    I will also now try to start a "clean" performance collection in this database, see how that goes.

    Wednesday, August 6, 2014 3:32 AM
  • Starting a new performance collection, by deleting the old performance data when asked after starting the "Collect performance data" give a working database. So maybe it has to do with the time between the collections.

    Another thing. The first performance collection was on all the machines inventoried and reachable (614 machines), for two days. Then a performance collection of only the production database servers (27 machines) for a day, then the test and dev database servers (31 machines) for a day. Then add a performance collection of all the machines (614).  

    Then wait for about a week and then add a performance collection on all the (614) machines again for about five days. After adding that last collection it is not possible to use the performance data in the database any more. I can still generate inventory reports, it is only the performance data that is no longer valid.

    Wednesday, August 6, 2014 4:49 AM
  • Time between isn't the problem. If you look in the log file, SQL is timing out. I think the problem is machine resources and time related. After the performance data collection has run for the predefined amount of time, MAP has SQL execute various assessments on the data to aggregate the raw data into something MAP can use. The more raw data that exists, the longer SQL will take and the more CPU and memory resources SQL will need.

    I would recommend that you have at least 4 cores or vCPU's and 6-8 GB of memory dedicated to the machine on which MAP is running. I would also follow the directions in this Wiki article to increase the timeout in MAP so that MAP will give SQL the time it needs to complete the job. http://social.technet.microsoft.com/wiki/contents/articles/10397.map-toolkit-increasing-the-sql-database-timeout-value.aspx



    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. Please VOTE as HELPFUL if the post helps you. This can be beneficial to other community members reading the thread.

    • Marked as answer by AndrevdL Thursday, August 7, 2014 5:07 AM
    Wednesday, August 6, 2014 6:01 PM
  • Thanks,

    The timeout value as suggested in the wiki article did the trick.

    The SQL Express only uses on of the 4 cores on my laptop, and the installed 4Gbyte of memory seems to be enough for the assessment in our environment.

    Thursday, August 7, 2014 5:13 AM
  • SQL Express can use multiple cores.

    MSDN Article: "Limited to lesser of 1 Socket or 4 cores".



    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. Please VOTE as HELPFUL if the post helps you. This can be beneficial to other community members reading the thread.

    Thursday, August 7, 2014 5:22 PM
  • I saw on my PC that the SQL process only had 25% max CPU during the Refresh Assessment. Taskmanager only showed one core at about 100%. Maybe that has to do with the version of SQLExpress with MAP toolkit.

    I tried to find out what version I am running, but that is difficult I find. I do not use SQL Server Management Studio. And I cannot find any errorlog files of the server.

    Monday, August 11, 2014 5:55 AM
  • I found the version: "Microsoft SQL Server 2012 Express LocalDB   11.0.2100.60", using Powershell. Funny that I only saw it use one Core of my PC.
    Monday, August 11, 2014 7:30 AM