Post reply

Best way to create a weekly email report for SQL Server

  • March 15, 2010 at 10:02 am

    #142065

    Hi,

    I have been asked to create a automated "report" of some sort to provide some kind of statistics on our SQL Server 2005.

    Basically, something we can take a look weekly to see how the server is, and growing trend.. something I would want to see is:

    - Max user connections

    - CPU %

    - Disk Space Usage

    - SQL Error Log (if any)

    Anything else that anyone can suggest as a good metric to keep track of? Also, what would be the best way to archive this? Anyone have a similar thing running on their SQL box?

    Thanks.

  • March 15, 2010 at 10:18 am

    #1133862

    - Max user connections

    I am not sure this possible

    - CPU %

    SCOM

    - Disk Space Usage

    SCOM or SQL 2008 Data collection or performance dash board

    - SQL Error Log (if any)

    Set up traces

    EnjoY!

  • March 15, 2010 at 11:06 am

    #1133901

    Backup summary.

    Avg Disk Reads / Writes

    SCOM could get you some of the info you need. Other options are to use PowerShell, SMO, tsql scripts to build a custom in-house app that could track and email this stuff.

    Another option would be to use the dynamic management views to gather information, and then use SSRS, SSIS, or TSQL script to send the report.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 15, 2010 at 10:02 pm

    #1134236

    Short summary of how I used to do this

    - Create small db on every instance to hold data.

    - write scripts to gather metrics, separate table for each metric. Use a separate job to schedule each set of metrics as needed (some daily, some hourly, etc.)

    - Put a report table in the db that just holds char fields, an instance name, and an ordering field

    - Write a proc that "builds" a report, putting lines of data in the report table, ordering them as needed, and including the instance name. This gives you a report daily, on each server.

    - Write a script (SMO/Powershell/LinkedServers/etc) to roll up all reports from all instances to one central server.

    Have that central server email you the report, ordering by instance, and then ordering column.

    It's flexible, you can add requirements as needed.

    You can also do the final export in SSRS.

  • March 16, 2010 at 9:52 pm

    #1134969

    You might have to monitor multiple server and multiple instances if this is true then

    1.Create a centralize monitor server (SQL server with single instance)

    2.Create a table and store the SQL server inventory detail as explained below

    SQLInventory

    1.ServerID

    2.ServerName

    3.InstanceID

    4.InstanceName

    5.SQLVer

    6.Enable_monitoring

    SQLInvDetail

    1. InstanceID

    2. DatbaseID

    3 DatabaseName

    4. Enable_monitoring

    Create another table that will store the information about the server and instance resource

    usage

    InstResource

    1.ServerID

    2.InstanceID

    3.Fromtime

    4.Totime

    5.CPUusage

    6.MemUsgae

    DbRessource

    1.InstnaceID

    2.DBID

    3. File1

    4. GrowthMB

    3. Now Create a SQL job that will connect to each server and collect the information and stored them in the InstanceRessource and DBressource table

    Note; You can enable and disable monitoring for a particular Instance and User by setting using Enable_monitoring

    Schedule the job to run as you needed

    Now we can use the SSRS to design your report.

    Hopes this gives you some idea. Let me know if this works

    Cheers,

    Gorachand Mohapatra

  • March 19, 2010 at 2:19 pm

    #1137025

    Do you still help with this? I can share my script and .rdl if you want. Let me know.

  • April 21, 2010 at 6:04 am

    #1155278

    I do need your script. If possible post it

  • April 22, 2010 at 7:20 am

    #1156085

    Yes, I am interested too.

  • April 22, 2010 at 8:16 am

    #1156134

    amc, that would be great if you can post yours.

    I have postponed this actually, but need to have something ready by end of next week.

  • April 22, 2010 at 10:54 am

    #1156315

    HI, sorry it so long to respond.

    Actually, the how to create this Drive (disk) Space Report came from Sql Server Central.

    Right here is the link:

    http://www.sqlservercentral.com/scripts/Administration/67064/

    And I attached my .RDL, so I hope this helps you guys.

    It wasn't too hard at all. Just have to make alot of data sources to all the servers I wanted to monitor. Have fun. Let me know if you have any questions. 😉

    ps. I hope I attached the .RDL correctly.

  • October 24, 2011 at 7:32 am

    #1398669

    This is the best I've found for simple monitoring and it's right here on SQLServerCentral

    http://www.sqlservercentral.com/Authors/Articles/David_Bird/120509/

    David Birds morning review. Once its set up, its very powerful and you can collect tons of information.

    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61774/

    I use it at every job I take

    Thanks,
    Kimberly Killian
    Sr. DBA / DB Engineer
    www.sitedataview.com
    Follow me on Twitter
    Follow me on Facebook

  • October 25, 2011 at 5:01 am

    #1399021

    Hi have a look @ this http://www.sqlservercentral.com/articles/Monitoring/69650/

    I have been using it and it monitors all the sql servers on an entire domain for me ...

    saves a lot of time ... gives more info and more helpful for me than SCOM!!! 😛

    *Edit same as above post ;-)*

    MCITP: Database Administrator 2005
    MCTS SQL Server 2008
    MCP SQL 2012/2014
    MCSA SQL Server 2012/2014
    MCSE Data Management and Analytics

Viewing 12 posts - 1 through 11 (of 11 total)

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