Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Ready Made Code Snippet Tool Box Courtesy of SCOM Expand / Collapse
Author
Message
Posted Tuesday, April 28, 2009 10:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 1,861, Visits: 3,599
I'm currently having a performance problem related to SCOM that I'm trying to resolve.

Procedure sp_spaceused appears to be consuming a lot of tempdb space in my SQL 2000 instance, to the point that tempdb free space is almost gone.

This seems to be occurring when the sp_spaceused process is blocked by another server process.

I am certain that stored procedure sp_spaceused is being executed by a SCOM rule/monitor that ships with the SQL Server management packs: the user is NT Authority/System, which is the SCOM user.

I think sp_spaceused is being called from the DB Space Free monitors.

To confirm, I looked through the SQL2000 Monitoring management pack (in XML) and could not find any occurrence of sp_spaceused.

What am I missing?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #706074
Posted Tuesday, April 28, 2009 12:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:53 AM
Points: 2,878, Visits: 466
did you also look through the discovery management packs? the SQL packs usually are in pairs, one for dscovery, one for monitoring. do a quick search through all of them (2000/5/8), just to be safe.


----------------------
http://thomaslarock.com
Post #706154
Posted Tuesday, April 28, 2009 12:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 1,861, Visits: 3,599
Thomas LaRock (4/28/2009)
did you also look through the discovery management packs? the SQL packs usually are in pairs, one for dscovery, one for monitoring. do a quick search through all of them (2000/5/8), just to be safe.


Thanks for the reply.

Yes, I just looked at all management packs for SQL, SCOM version 6.0.6460.0, and could not find sp_spaceused.

Here are the MPs I searched (under ...\Program Files\System Center Operations Manager 2007\Health Service State\Management Packs):

Microsoft.SQLServer.2000.Discovery.{0B5A085F-F001-0E39-42B4-807321EE46D4}.{DC14D306-B845-D465-ED80-FFD52DE8DF5E}.xml

Microsoft.SQLServer.2000.Monitoring.{8F1F7C0E-ADC9-2226-BD01-B70C6D1B206D}.{7B6079F9-AF30-8053-50D1-8288DC6D9240}.xml

Microsoft.SQLServer.2005.Discovery.{6A9F98A6-04FB-7740-9429-08D0721CAE7D}.{2FFCE1C9-09C4-D319-9E03-2C4B2665BC64}.xml

Microsoft.SQLServer.2005.Monitoring.{1E1833E1-5E0B-1B28-AF46-933A6861FAE0}.{1C88D85E-8695-2AE7-215A-B6E707DD2D14}.xml

Microsoft.SQLServer.2008.Discovery.{542C15D1-2E58-C85D-8574-84A35AB751E9}.{B18574A1-9304-5FEB-91BF-F128F4A519BD}.xml


There is no monitoring MP for SQL 2008.

I did not find a reference for sp_spaceused in any of these files.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #706182
Posted Tuesday, April 28, 2009 7:21 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:53 AM
Points: 2,878, Visits: 466
Marios,

Try a search for sp_spaceused on all management packs on your target server. I beieve it will be in one of the core system packs.



----------------------
http://thomaslarock.com
Post #706421
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse