{"id":2150,"date":"2014-10-06T01:01:50","date_gmt":"2014-10-06T05:01:50","guid":{"rendered":"https:\/\/www.tmurgent.com\/TMBlog\/?p=2150"},"modified":"2014-11-09T19:14:39","modified_gmt":"2014-11-10T00:14:39","slug":"dont-let-your-app-v-5-reporting-database-grow-out-of-control","status":"publish","type":"post","link":"https:\/\/www.tmurgent.com\/TmBlog\/?p=2150","title":{"rendered":"Don’t let your App-V 5 Reporting Database grow out of control!"},"content":{"rendered":"

\"ImageThe App-V Reporting database for App-V 5 and above is great for monitoring all usage of your virtual applications.\u00a0 This is quite helpful when it comes to license compliance.\u00a0 While it won’t prevent (accidental) usage of unlicensed apps, it is very useful for detecting usage as part of a program to “true up” your license allocations.<\/p>\n

The Reporting Server is independent of how you deploy the virtual apps.\u00a0 It doesn’t matter whether you use the App-V Server System Center Configuration Manager, or deploy using the stand-alone client.\u00a0 You can still use the reporting server.<\/p>\n

If you do, I highly recommend that you use\u00a0the App-V Client GPO to configure the clients to use the reporting server.\u00a0 Setting this as an enforceable policy makes sure that every client will report in usage data each night.\u00a0 Only with non-persistent clients (like some VDI clients or PVS booted XenApp servers) is there a concern of not getting the data, but those can be solved by adding a report sync to the user logoff scripts using the App-V Client Powershell API.<\/p>\n

There is always a but…<\/h2>\n

But this blog post is to warn you about a small flaw in the App-V Reporting Database setup.\u00a0 By default, it will not clean out old data.\u00a0 EVER<\/strong>!\u00a0 I am not referring to the temporary table data (removed when records get consolidated each night), but the record data on each and every virtual application run by a user. The App-V 4.* server would automatically clean this out at some point.\u00a0 But not version 5. While Microsoft added logic in the form of stored procedures to clean old data out, it isn’t used by default.<\/p>\n

The stored procedure, spEnforceSizeLimit, is designed to check the size of the current database and compare it to a configurable maximum size. There are also two additional configured settings, a low water mark and high water mark.\u00a0 These are\u00a0stored as percentage factors and are\u00a0interpreted as relative to the configurable max size. So the current value can be in one of thee states against these settings:<\/p>\n

    \n
  1. \u00a0If the current size of the database is below the low water mark it decides that things are OK. If logs a WARNING message to the Sql database log (“WARNING The database size limit cannot be enforced<\/span>“) \u00a0and exits. I guess it is a warning that maybe you aren’t collecting any data and maybe you should check on that.<\/li>\n
  2. When above the low water mark and below the high mark\u00a0, it will seek to purge the oldest application usage data.\u00a0 The logic is simple (much simpler than the extensive logic used in the original App-V server) but sufficient.\u00a0 It will take a ratio of the current size to that of the low water mark, look at the date of the record about that far into the usage table (when sorted by date) and remove anything older than that date.\u00a0 It adds a message to the Sql database log (“WARNING: The database size limit has been enforced<\/span>“).\u00a0 This message is a good thing and indicates that the purging is working.<\/li>\n
  3. When above the high water mark, it\u00a0logs a different message to the Sql database log (“WARNING: The database is cleaning up a large percentage of data<\/span>“), and then processes just as in the previous case.<\/li>\n<\/ol>\n

    So here are the two problems in the default install preventing this purging from working:<\/p>\n

      \n
    1. It depends on the maximum size, and low and high water marks being present in the SystemOptions table of the database, and these are not set by default, as shown in the image below.\"ImageThis is treated by the stored procedure as an error, however no error message is logged and no purging occurs. This means that the stored procedure will do nothing\u00a0when called of there isn’t a row in this table.<\/li>\n
    2. There is nothing that triggers the stored procedure to run anyway.\"Image
      \nThe image above shows the default jobs in the database, one to handle the daily processing of received client reports, and the other a standard database job. No job to run the cleanup script though.<\/li>\n<\/ol>\n

      And I wouldn’t be writing this if there wasn’t a solution…<\/h2>\n

      To solve these issues and ensure that your database doesn’t infinitely grow (well, or until your DBA notices), the DBA should do the following:<\/p>\n

        \n
      1. Ensure that Sql Server Agent is running on the database server.\u00a0 (If it isn’t, the stored procedure to process the client data from the temporary tables isn’t being triggered to run and you won’t get reporting data anyway).<\/li>\n
      2. Add a row to the empty SystemOptions table.\u00a0 Set the max_db_size column with a value for the maximum database size in Megabytes.\u00a0 Each record in the Application Usage table takes around 200bytes of storage.\u00a0 Multiply that by the number of users, then by the number of average app launches per day, then by the number of days of data you want to keep.\u00a0 Then add some overhead for the temporary\u00a0and other tables (maybe 50MB?) and spare capacity and you have your setting for the maximum size.\u00a0 Set the low and high water percentage mark appropriately, but be aware that in spite of the name they are really factors, so 0.80 and 0.90 might be good numbers.\u00a0 Make sure to monitor the logs after a while.\u00a0 The warnings I mentioned provide details on the numbers calculated to help you adjust these settings.<\/li>\n
      3. Add a SQL job to run that calls the spEnforceSizeLimit stored procedure.\u00a0\u00a0 There is already a job to call the spProcessClientReport stored procedure once a day, so mimic that job.<\/li>\n
      4. Play the logic game on when things happen, and possibly adjust timers.\n