Friday, March 2, 2012

SBSMonitoring shows heavy disk usage

The server disk usage is high with no particular reason, the service useing all the disk resources was mssql.exe reading from SBSMonitoring.mdf.
Searching the net took me to a TechNet post talking about this exact issue.
After applying this fix everything got back to normal.
To perform the fix:
  1. Log on as an administrator.
  2. Start SQL Server Management Studio. (Express edition is fine)
  3. Attach to SBSMONITORING instance (replace SERVERNAME with the name of your server):
    Server Type: Database Engine
    Server Name: SERVERNAME\SBSMONITORING
    Authentication: Windows Authentication
  4. Once connected, from the Menu, click: File -> New -> Query with Current Connection. This creates a new query window.
  5. Copy the commands below into the window, then press F5 to execute.
USE SBSMONITORING
GO
CREATE NONCLUSTERED INDEX [IDX_Alerts_GetAlertsPerID] ON [dbo].[Alerts] 
(
   [DefinitionID] ASC,
   [ComputerID] ASC,
   [DateOccured] ASC,
   [IsSet] ASC,
   [ID] ASC
)
GO
CREATE NONCLUSTERED INDEX [IDX_WMICollectedData_GetSecurityProductPerComputer] ON [dbo].[WMICollectedData] 
(
   [WMIInstanceID] ASC,
   [WMIPropertyID] ASC,
   [DateCollected] ASC,
   [ID] ASC
)
GO
 
create index IDX_WMIObjectProperties_ObjectID on WMIObjectProperties(ObjectID,ID)
Go

create index IDX_WMIObjectInstances_ObjectID on WMIObjectInstances(ObjectID,ID)

No comments: