Ready Made Code Snippet Tool Box Courtesy of SCOM

Interesting article especially if you are a newbie to SCOM 2k7 and have limited exposure SQL 2005 R2. Have you a desire to share in greater detail some of possibilities in scripts you have created from SQL and SCOM 2k7? Its a pleasure to read your enthusiasm.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

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?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

SQLRockstar SSCertifiable Points: 6926 More actions · Answer 7

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.

----------------------
https://thomaslarock.com

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

SQLRockstar SSCertifiable Points: 6926 More actions · Answer 9

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.

----------------------
https://thomaslarock.com