database-maint.exe causing heavy I/o on the Orion NPM database each night

Having a problem with the database causing large i/o on the Orion database each night.
Rebooting seems to fix the issue, this is not an option thought. The SQL Query that seems to be causing the issue is:

Here’s the SQL query that appeared to be causing the constant I/O (I got this from the Activity Monitor in the Microsoft SQL Server Management Studio)

INSERT INTO NPM_NV_WL_INTERFACES_HOURLY (ID, OrigID, GroupID, RecordID, NPMNodeID, NodeID, ParentID, OrionLink, UnManagerd, FirstUpdate,LasUpdate,Clients,Clients_MIN,Clients_MAX,CurrentChannel,MACA

It went back to normal after I killed the database-maint.exe on the SolarWinds box.

Were are running Orion 9.5 sp5

Windows 2008 standard sp2 32 bit

MS Sql Server 2008 enterprise 64 bit

Find more posts tagged with

database-maint.exe_database

database-maint.exe_io_database

Accepted answers

All comments

MarieB

Hi Mike--

If you don't get a fast response from the community, open a support ticket.

mikeyjay

Update for Case #174237 - "Database-maint.exe Causing Heavy I/O load on database Each night, rebooting seems to fix."

Hello,

Thank you for contacting SolarWinds support via our web site. Most likely what has happened is the summarization has not completed. This would leave data in 2 different tables. When the database maintenance process is trying to move data from one table to another it already exists. We may have to manually remove the data. Can you send in the swdebugmaintenance.log file that is stored in the installation directory?

Update for Case #174237 - "Database-maint.exe Causing Heavy I/O load on database Each night, rebooting seems to fix."

Hello Mike,

It looks like the transaction log is full on your SQL server. It needs more space. Can you adjust that? Here is the error:

System.Data.SqlClient.SqlException: The transaction log for database 'NetPerfMon' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

MarieB

Thanks, Mike, for letting the community know.

epenney

Greetings Mike,

We too had exact same problem and I had opened a ticket. Our resolution, which has completely solved our problem is to perform the following on a monthly basis. Maintaining wireless related details with the same data retention as all other NPM data retention just didn't make sense to us due to impact to database. I submitted a request to Solarwinds that Wireless data have a separate data retention mgt interface. Not sure if that request made it to the proper channels, and not sure if its being taken seriously. We still benefit from seeing current wireless data - historical beyond a few days is not relevant for us at this time. Our nightly was restored to 30min once i implemented the manual monthly cleanup below (we have 3 additional pollers, total of 2,500 managed nodes, (14,000 managed elements).

Would have liked to use TRUNCATE instead of DELETE, to avoid logging this delete operation to transaction log, however there are references which unfortunately don't permit truncate operation on most of these tables, so I just delete- i am considering changing to weekly cleanup, as the operation below takes considerable time to run (90min).

use NetPerfMon
DELETE NPM_NV_WL_CLIENTS_DAILY
DELETE NPM_NV_WL_INTERFACES_DAILY
DELETE NPM_NV_WL_APS_DAILY
DELETE NPM_NV_WL_CONTROLLERS_DAILY
DELETE NPM_NV_WL_CLIENTS_HOURLY
DELETE NPM_NV_WL_INTERFACES_HOURLY
DELETE NPM_NV_WL_APS_HOURLY
DELETE NPM_NV_WL_CONTROLLERS_HOURLY
DELETE NPM_NV_WL_CLIENTS_DETAIL
DELETE NPM_NV_WL_INTERFACES_DETAIL
DELETE NPM_NV_WL_APS_DETAIL
DELETE NPM_NV_WL_CONTROLLERS_DETAIL
go

Regards,

Ellen Penney.

Network_Guru

"I submitted a request to Solarwinds that Wireless data have a separate data retention mgt interface. Not sure if that request made it to the proper channels, and not sure if its being taken seriously. We still benefit from seeing current wireless data - historical beyond a few days is not relevant for us at this time."

I second this request, I only need a few days of data for Wireless APs & clients, and would like to dump anything older than that.

I'm having similar issues with my Database Maintenance running for hours, causing huge gaps in the charts.

Network_Guru

Just a follow up on this;

After running some delete commands and re-indexing and compressing some of the Wireless DB tables, my nightly maint. actually finished last night.

However, it took almost 30 minutes to summarize the Wireless data:

2012-04-13 02:38:21,016 [1] INFO SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Summarizing detailed data from NPM_WIRELESS

2012-04-13 03:06:47,674 [1] INFO SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Summarizing hourly data from NPM_WIRELESS