RG013 – Reducing the size of the Workspace Manager datastore

By Max Ranzau

 

Warning: The following article contains  SQL actions on the RES Workspace Manager database, so do NOT uwarning, yellowse this on a production system. If you do, it’s at your own risk and peril. If you are unfamiliar with Usage Tracking, it is suggested that you DO NOT PROCEED. Instead, read this article here. If you’re not comfortable hacking away at databases, better take a rain check on this one alltogether.

Out of the box, Usage Tracking is switched off when you install RES Workspace Manager. Even when you switch Usage Tracking on, it’s configured per default only to log detailed information 7 days back. This is with good reason: If you are a large organizations with thousands of users, Usage Tracking can potentialy generate Gigabytes of data on your SQLserver. This is not necessarily a bad thing, if that’s what you want and have prepared for.  How big can it get then, you might ask? That depends on a number of factors. Last month an article was posted with a Usage Tracking DBsize calculation tool, which may help you determine this.

To make a long story short: You might inadvertently find yourself in the situation, where Usage Tracking has been configured to log much more than what you really needed. The symptoms would speak for themselves: Your diskspace on the database server has gone through the floorboards and the SQLserver itself is gasping for air. You need a cleanup and you need it fast!. This is fortunatly not a situation which will happen every day, but the way to deal with this kind of situation is this:

Overview of operations

  1. Get an  overview – how big is the database
  2. Stop Usage Tracking
  3. Refresh the active agents out there
  4. Delete Usage Tracking information from the database.
  5. Start the Workspace Manager console to automatically recreate the mentioned tables.
  6. Optionally shrink the database. Workspace Manager does this by itself on a regular basis though.
  7. Reconfigure Usage Tracking and start it again
  8. Check the dbsize to see what you’ve reclaimed.
  9. Verify that Usage Tracking is up and running again

 

1) Checking the size of the database 

First thing to do is to get an idea of the size of the database. There are several ways to do this

  • The easiest is to go to the SQL manager console, rightclick on the Database and select properties. This will give you the current size and space left in the database. This requires you to have administrator priveledges.
  • Another approach is to look at  the physical files of the database. If they are in the default path they would be in the Microsoft SQL Server\MSSQL\Data\name-of-Workspace Manager-database.?df
  • A third option is to run an SQL query against the Workspace Manager database. This does not require special permissions, as long as you use an account that has minumum read-only on the DB. The query would read as:
SELECT [size] * 8 / 1024 AS [Size in MB], filename FROM sysfiles

If you’re wondering what the 8 / 1024 is all about, the 8 represents the default blocksize of the database in kb. Chances are that initially you created your Workspace Manager database using the wizard in the console. If that’s the case the blocksize of the database is 8kb. We divide the result by 1024 in order to get the size result in MB. An example Automation Manager buildingblock for this, is available in the appendix at the end of this article

 

2) Stopping Usage Tracking

pt-modeNext item on the list is stop the avalanche of log info by getting Usage Tracking stopped. You go to the Monitoring, Auditing and Alerting | Usage Tracking node and set New Usage Tracking node to disabled. 

 

3) Refreshing all sessions

Once we’ve signalled to pull the brakes on Usage Tracking, we need to have all active sessions refreshed, so the agents out there actually stop logging Usage Tracking info. There are several ways to do this.

  • The simplest option option is to either wait until all users have logged out. This is okay if your database is not performance hampered and normal production can continue. 
  • The second option is to send out a refresh to all the active sessions. There is a method to do this, which is found in the RES Workspace Manager Settings | Settings | Advanced Configuration:

adv-refresh

Using the ‘Refresh active’ button, you send a signal out to all machines that they should refresh at once. This is however something you would want to think over quite carefully, as it potentially can generate alot of network traffic. Especially if you’ve got users by the thousands out there. The number next to the button specifies the timeframe in minutes, in which all active Workspace Manager agents will refresh.

Scr, Wisdom refresh taskIf you are using RES Automation Manager, you also have another option. Create a module to schedule a refresh task to clients at your convenience. Use the refresh task and  schedule it to the clients in question. This way you can have more granular control of which sessions are to be refreshed when. See notes from the Automation Manager helpfile on this task below:

“Use the Task Refresh RES Workspace Manager Workspace Manager to force a refresh of each active RES Workspace Manager session on the target computer. Multiple sessions on the same computer (for example a Terminal Server) will be refreshed at a 10-second interval. This Task is useful, e.g. to refresh RES Workspace Manager sessions on laptops or to notify users in a RES Workspace Manager environment that new applications are available.  Note: This Task will always complete. If RES Workspace Manager is not installed on the target computer, this will be displayed in the log.” 

4) Cleaning out the closet

When all agents have been refreshed, it’s time to clean things up. There’s the nice and safe way to clean up, clear-buttonwhich is going to the RES Workspace Manager Setup | Datastore | Maintainance node and use the ‘Clear all logs now…’ button. This will however nuke all the logs stored in the database also, so if you for example want to keep the AppGuard logs we need to look at a different approach.  

The alternative is to delete (drop) the Usage Tracking tables directly from the SQL database. On some DBMS’s this may be faster than doing it through the Workspace Manager console. You will need to drop the 5 tables which are: TBLhistory, TBLuser, TBLserver, TBLsession and TBLapp. You can do this in several ways:

  • Use the iSQL command. Run it as isql.exe -U <sa equivalent user or dbo of the Workspace Manager DB>. This is for MSSSQL Server. Use the command drop <tablename>. End the commands with a ^Z (Ctrl+Z) to start.
  • Go to the SQL Management console | Your  Workspace Manager database | Tables. Select the tables, rightclick and select delete
  • Use a RES Automation Manager job to do the work, without getting your hands dirty ;-) As usual a Automation Manager BuildingBlock is available for your lab use at the end of this article.

If you have RES Automation Manager, it is easy to create a buildingblock to do the dropping. Just create a module and add the Execute SQL Statement. Configure it so it looks like this:

sql-drop

Notice the red blob next to the database server name above here. This means you need to configure a Automation Manager Connector. Configure it correctly and the blob turns nice and green. For more information see the Automation Manager Licensing 101 article.

 

5) Recreating the tables

The quick and easiest way to recreate the Usage Tracking tables, is to start the RES Workspace Manager console on any connected machine. The console will check for the existence of the 5 tables uppon startup and wil recreate the missing tables. While it would be possible also to do this automatically, it is really not feasable as you would have the Usage Tracking logging stopped anyway so you can take your time to clean up. Second, the table formats of the Usage Tracking database may change in the future.

 

6) Shrinking the database

This step is optional, however if you’ve got a massive heap of dropped tables and you want to reclaim diskspace on the SQL server, you need to think about shrinking the database. This can be done through both the SQL console and a SQL script. The easy option for doing a quick database shrinking, is like this:

scrshot21

 In the SQL manager console, rightclick the Workspace Manager database, chose All tasks, Shrink Database. This will give you the option to reclaim the diskspace. In most DBMS’s this does not require you to take the database offline, however if the database has gotten really big, it may temporarily degrade the performance while the shrinking is going on.

If you want to script the shrinking the command to use with iSQL is DBCC SHRINKDATABASE (Workspace ManagerDataBaseName,Percent-to-leave free). For example. If you run an iSQL command such as DBCC SHRINKDATABASE (Workspace ManagerDB,10) the SQL server will shrink the database, freeing up unused diskspace, leaving 10 percent on top of the used data portion for future use. You can read more about the shrink command on MSDN. If you want to do it through RES Automation Manager, a generic database shrinking module has been made available at the bottom of this article.

 

7) Reconfiguring Usage Tracking

pt-defaultTo ensure that once you re-enable Usage Tracking that logging is kept at a managable level, it is recommended to review the Usage Tracking configuration settings. In the Monitoring, Auditing and Alerting | Usage Tracking node, initially set the logging days to the default values as shown on the right:

If still the amount of logging data is too high, you may consider to disable Usage Tracking on some machines where it is not necessary to collect data. This is not archievable throught the console, however there is a registry setting which can be used to disable logging of Usage Tracking on a given machine or user(s):

In HKCU or HKLM, create \SOFTWARE\Policies\RES\Workspace Manager\Settings\Usage Tracking\Enabled (REG_SZ) = No

A HKLM registry setting, to be imported in Automation Manager, can be found here:  Icon, Regfile 

(rename to .reg when downloaded) Once satisfied, re-enable Usage Tracking using the Enable radiobutton describe in step 2

 

 

8) Check new database size.

Run once again the db size check, which you choose in step 1, in order to compare how much the database has been shrunk.

 

9) Verify that Usage Tracking is operational again

Perform the following quick test to ensure that Usage Tracking is up and running, logging data once again:

  • With Usage Tracking enabled in the console, log into another computer (which is online with the database and one which you haven’t disabled Usage Tracking on!)
  • Start a couple of programs, and exit them again
  • Go to the Workspace Manager console | Monitoring Auditing and Alerting | Usage Tracking. Click on the Usage Tracking viewer button.
  • Go to the Details tab in the Usage Tracking viewer. Hit [enter] to refresh You should be able to see your transactions at the top of the table.

Note: It has been observed sometime back that it might take a few of minutes before Usage Tracking initializes, so don’t panic if you don’t se new records immediatly after.

 

Appendix: Related Buildingblocks

In this appendix you will find the necessary Automation Manager buildingblocks to perform some of the operations, as described above. Note: The sizequery and shrink modules are not specifically tailored to work just with the Workspace Manager database, they could be usefull with other databases as well. Click on the bricks below to download them. All the following buildingblocks are for Automation Manager only. Rightclick and save-as (buildingblocks on this page are not rar-compressed)

legobrick_red

Check database size. This will return the size of a database in MB based on blocksize of the specifed DB. This is usefull if you have neither SA eqivalent or file-system access to the DBMS

legobrick_red

Refresh Workspace Manager sessions. When sheduled to a Workspace Manager enabled computer, all sessions will refresh. Very usefull for terminal servers.

legobrick_red

Drop the Usage Tracking tables. This module will drop the required tables. Make sure that all Workspace Manager agents have stopped logging data before scheduling this one.

legobrick_red

Shrink a database. This generic module will let you shink any DBMS using SQL serves own shrink command. The module allows you to specify which type of shrinking should occur. See module for details.

 

No Comments

No comments yet.

RSS feed for comments on this post.

Leave a comment

You must be logged in to post a comment.