Operations Manager Custom Collection

  • Grant Fritchey

    SSC Guru

    Points: 395510

    Comments posted to this topic are about the item Operations Manager Custom Collection

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Raghuram (AJ)

    Ten Centuries

    Points: 1214

    Great article Grant!

    The vision must be followed by the venture. It is not enough to stare up the steps - we must step up the stairs. - Vance Havner
  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article ........

  • Grant Fritchey

    SSC Guru

    Points: 395510

    Thanks guys. I realize it's kind of an obscure topic, but I had such a hard time tracking down the individual pieces of information that I felt compelled to share it so the next person has a slightly easier time.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • thierry.vandurme

    Hall of Fame

    Points: 3098

    Great stuff! Although I'm one of those guys who loves to find this out himself (you forget less if you need to search yourself) it kinda eases the mind with the idea that I can always revert to your article when I'm stuck. Thx again!

  • Grant Fritchey

    SSC Guru

    Points: 395510

    I'm glad it was helpful.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Anipaul

    SSC-Insane

    Points: 24681

    Grant Fritchey (6/5/2008)


    Thanks guys. I realize it's kind of an obscure topic, but I had such a hard time tracking down the individual pieces of information that I felt compelled to share it so the next person has a slightly easier time.

    Yaa, I know Grant, how much care a author has to take before producing a good article. Thank you for all your efforts you have taken to write this article.

    🙂

  • Jack Corbett

    SSC Guru

    Points: 184371

    Nice article Grant. Not sure if I'll use it or not, since there are other ways of collecting the information without MOM, but since we have MOM it might be the best way to go about it and now I know how.

    Definitely a good topic to write about because I mainly think of MOM as a System Admin tool and not a SQL Server tool.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Grant Fritchey

    SSC Guru

    Points: 395510

    Oh yeah, this is a major SQL Server tool if you can put it to work. Talk to Tom LaRock some time. He's gone way past where I am currently with the tool. Just the basic monitors & rules are pretty fantastic, but when you can start customizing it to your environment... it really takes off. And now that you've got the data... Reports... Except there's not a good model available for custom reporting (and yeah, if I get one working well enough, that's going to be another article).

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • SQLEngine

    SSCommitted

    Points: 1633

    Grant, very useful article, thanks. One question I have is how to report on this stuff after creating a rule? When I try to use if in Generic Performance reports in OpsManager, I don't see any of the properties specified in this new rule.

    I'd also want to report on the same thing in the filegroups, as far as I can tell.

    Thanks

  • Grant Fritchey

    SSC Guru

    Points: 395510

    File groups, I'm working on. Several people have asked the same question. I'll post something as soon as I have a complete story.

    Custom reporting, since these properties are collected just like any other, you can easily and quickly create a view in the Monitoring console. Otherwise, the data is stored into the datawarehouse in the same manner as all the other rules and so, if you've figured out how to query data from there, you can pull this data too. This could be an article too. No one from MS has published a model on how to do this. I've got some custom reports working from data collected, but it was labor to get them going.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey

    SSC Guru

    Points: 395510

    I'm not sure why you'd want to manage by FileGroup, but... What if you set up the same process that I outlined, but used this query to gather the data. I think it'd do what you want.

    SELECT fg.GroupName

    ,SUM(f.size / 128.) AS TotalSizeInMB

    ,SUM(CAST(FILEPROPERTY(f.NAME, 'SpaceUsed') AS INT) / 128.) AS SpaceUsedInMB

    ,SUM(f.size / 128. - CAST(FILEPROPERTY(f.NAME, 'SpaceUsed') AS INT) / 128.) AS FreeSpaceInMB

    FROM dbo.sysfiles f

    JOIN [dbo].sysfilegroups fg

    ON f.groupid = fg.groupid

    GROUP BY fg.GroupName

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • SQLEngine

    SSCommitted

    Points: 1633

    :)Grant, that's the join I was looking for, thanks.

    I could use it in the same VBS you posted earlier, absolutely. The chellenge I am facing now is reporting on this.

    I tried creating a view, scope on the same target as the Rule is on, and check "collected by specific rule". But I get 'No Performance Counter is Selected...' And the legend is empty, so I think no data is being gathered. I'll play with this a little more, but your help is aappreciated!

    Thanks

  • Grant Fritchey

    SSC Guru

    Points: 395510

    The first time I created the rule, it took a while for it to actually gather the data. I'm assuming that was time spent sending everything out to all the various agents. Also, since I only collect data once a day, the default on the views is to show only a day's worth of data, but if the process hasn't run yet today, it just shows as blank. Changing the date range to two or more days makes the difference.

    Good luck. Let me know if you run into any other issues.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Grant Fritchey (6/5/2008)


    Thanks guys. I realize it's kind of an obscure topic, but I had such a hard time tracking down the individual pieces of information that I felt compelled to share it so the next person has a slightly easier time.

    Great article, thanks for writing about SCOM. I've been having a hard time working with SCOM, since, as you yourself mention, information is not well organized out there. It's been very frustrating.

    Please keep those SCOM articles coming, they are a valuable resource.

    __________________________________________________________________________________
    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]

Viewing 15 posts - 1 through 15 (of 76 total)

You must be logged in to reply to this topic. Login to reply