WhatsUp Professional - sqlservr.exe 100% CPU issue
|WhatsUp Professional||2005+||Windows 2003 Server
Windows XP SP1 or later
Question/Problem: When the WhatsUp Pro service is running, the 'sqlservr.exe' process takes 100% of the CPU. How can I address this issue?
Answer/Solution: The 'sqlservr.exe' process is part of the Microsoft SQL Server database application used by WhatsUp Pro. In certain circumstances, it can consume 100% of the CPU if more "raw" sample data is collected than can be purged. There are three components to the workaround for this issue.
First, we recommend you reduce the "keep time" for Active Monitor sample data to the smallest value possible. In the WhatsUp Pro Console application, select the "Configure, Program Options" menu item. Then select the "Report Data" page, and set the "Rollup raw data after" value to zero days and two hours. Click the "OK" button to save this change.
Second, we recommend you minimize the number of Devices that you are collecting statistics for. The "Bulk Field Change" functionality can be useful for this: simply select a group of devices and/or groups that you wish to not collect statistics for, right-click, and choose the "Bulk Field Change, Performance Monitors" item from the context menu. Next, in the "Bulk Field Change: Performance Monitors" dialog box that appears, set the "Collect data for" field to "None" for each monitor you want to disable, and click the "OK" button.
Third, the following SQL script will correct the issue immediately, by deleting all rows from the ActiveMonitorActivityLog and ActiveMonitorSampleData tables. (A constraint is dropped, and then re-added, to facilitate this.) Also, the entire database is compacted to recover space and reduce the size of the database.
Before running this script, shutdown the WhatsUp Pro Console application and the WhatsUp Pro service.
Note: This script will delete all sample data from your WhatsUp Pro database. You should backup your database using the "Tools, Database Utilities" menu item in the WhatsUp Pro Console before running this script.
Each time a device (for which statistics are being collected) is polled, a row is written to the ActiveMonitorActivityLog and ActiveMonitorSampleData tables. These rows are rolled-up into hourly and daily subtotals, and then they are deleted once they are older than the "Raw Data Keep Time".
So, since this sample data is automatically deleted anyway, this will not cause any permanent loss of functionality. The only visible effect will be in the Performance reports: if you drill down to a portion of the current day, you won't see the individual dots for each sample taken, until the "Raw Data Keep Time" (recommended to be set to two hours) has passed since the script was run.
Critical: The command "EXEC sp_createstats" should only be done by WUP 2006 users. It must not be done by WUP 2005 users. (WUP 2005 users who are expert in SQL Server issues should refer to: WhatsUp Professional - Upgrade to Pro 2006 fails on RC3-to-RC4 Upgrade Script)
TRUNCATE TABLE ActiveMonitorSampleData
TRUNCATE TABLE ActiveMonitorActivityLog
DBCC SHRINKDATABASE ('WhatsUp')
sp_MsForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'
The following batch commands can be saved in a BAT file and used to execute the above SQL script. These batch commands assume that the above SQL statements are saved to a file named "workaround.sql":
osql -E -D WhatsUp -i workaround.sql
Depending on the amount of sample data in the database, and other factors, this script should take about one minute to execute. When it has finished, the WhatsUp Pro Console and service can be re-started.
|Document #:||Revision Date:|
Return To KnowledgeBase Search Page