RG013 Reducing the size of the PowerFuse database
Warning: The following article contains SQL actions on the RES PowerFuse database, so do NOT use this on a production system. If you do, it’s at your own risk and peril. If you are unfamiliar with PowerTrace, 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, PowerTrace is switched off when you install RES PowerFuse. Even when you switch PowerTrace 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, PowerTrace 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 PowerTrace DBsize calculation tool, which may help you determine this.
To make a long story short: You might inadvertently find yourself in the situation, where PowerTrace 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
- Get an overview – how big is the database
- Stop PowerTrace
- Refresh the active agents out there
- Delete PowerTrace information from the database.
- Start the PowerFuse console to automatically recreate the mentioned tables.
- Optionally shrink the database. PowerFuse does this by itself on a regular basis though.
- Reconfigure PowerTrace and start it again
- Check the dbsize to see what you’ve reclaimed.
- Verify that PowerTrace 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-powerfuse-database.?df
- A third option is to run an SQL query against the PowerFuse 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 PowerFuse 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 Wisdom buildingblock for this, is available in the appendix at the end of this article
2) Stopping PowerTrace
Next item on the list is stop the avalanche of log info by getting PowerTrace stopped. You go to the Monitoring, Auditing and Alerting | PowerTrace node and set New powertrace node to disabled.
3) Refreshing all sessions
Once we’ve signalled to pull the brakes on PowerTrace, we need to have all active sessions refreshed, so the agents out there actually stop logging PowerTrace 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 PowerFuse Settings | Settings | Advanced Configuration:
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 PowerFuse agents will refresh.
If you are using RES Wisdom, 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 Wisdom helpfile on this task below:
“Use the Task Refresh RES PowerFuse Workspace Manager to force a refresh of each active RES PowerFuse 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 PowerFuse sessions on laptops or to notify users in a RES PowerFuse environment that new applications are available. Note: This Task will always complete. If RES PowerFuse 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, 
which is going to the RES PowerFuse 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 PowerTrace tables directly from the SQL database. On some DBMS’s this may be faster than doing it through the PowerFuse 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 PowerFuse 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 PowerFuse database | Tables. Select the tables, rightclick and select delete
- Use a RES Wisdom job to do the work, without getting your hands dirty ;-) As usual a Wisdom BuildingBlock is available for your lab use at the end of this article.
If you have RES Wisdom, 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:
Notice the red blob next to the database server name above here. This means you need to configure a Wisdom Connector. Configure it correctly and the blob turns nice and green. For more information see the Wisdom Licensing 101 article.
5) Recreating the tables
The quick and easiest way to recreate the PowerTrace tables, is to start the RES PowerFuse 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 PowerTrace logging stopped anyway so you can take your time to clean up. Second, the table formats of the PowerTrace 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:
In the SQL manager console, rightclick the PowerFuse 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 (PowerFuseDataBaseName,Percent-to-leave free). For example. If you run an iSQL command such as DBCC SHRINKDATABASE (PowerFuseDB,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 Wisdom, a generic database shrinking module has been made available at the bottom of this article.
7) Reconfiguring PowerTrace
To ensure that once you re-enable PowerTrace that logging is kept at a managable level, it is recommended to review the PowerTrace configuration settings. In the Monitoring, Auditing and Alerting | PowerTrace 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 PowerTrace 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 PowerTrace on a given machine or user(s):
In HKCU or HKLM, create \SOFTWARE\Policies\RES\PowerFuse\Settings\PowerTrace\Enabled (REG_SZ) = No
A HKLM registry setting, to be imported in Wisdom, can be found here: 
(rename to .reg when downloaded) Once satisfied, re-enable PowerTrace 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 PowerTrace is operational again
Perform the following quick test to ensure that PowerTrace is up and running, logging data once again:
- With PowerFuse enabled in the console, log into a computer (which is online with the database and one which you haven’t disabled PowerTrace on!)
- Start a couple of programs, and exit them again
- Go to the PowerFuse console | Monitoring Auditing and Alerting | PowerTrace. Click on the PowerTrace viewer button.
- Go to the Details tab in the PowerTrace 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 PowerTrace 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 Wisdom 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 PowerFuse database, they could be usefull with other databases as well. Click on the bricks below to download them. All the following buildingblocks are for Wisdom only. Rightclick and save-as (buildingblocks on this page are not rar-compressed)
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
Refresh PowerFuse sessions. When sheduled to a PowerFuse enabled computer, all sessions will refresh. Very usefull for terminal servers.
Drop the PowerTrace tables. This module will drop the required tables. Make sure that all PowerFuse agents have stopped logging data before scheduling this one.
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.
RESguru | 
2009/02/15 20:01
Subscribe