Visualize the timeline of your SQL jobs using Google graph and email

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4482

    Comments posted to this topic are about the item <A HREF="/articles/Jobs/127346/">Visualize the timeline of your SQL jobs using Google graph and email</A>CHECK THE ENTIRE DISCUSSION !! 🙂 :-)Multiple versions of script samples have been posted, read, download and maybe even add your own!grtz, Theo

    Latest version can be found on: http://www.ekelmans.com/FTP/Web/GoogleJobTimeline.sql

  • cms4j

    SSC Enthusiast

    Points: 193

    Great way to visualize this, thank you for sharing.

    I just downloaded the "GoogleJobTimeline.sql" and executed it in an Instance that has a Collation with "Case Sensitive".

    It ran into an error because of line 158 where you have written the columen "html" in lower case while it has to be in UPPER CASE: "HTML".

    Once again, thanks for the great work!

    CMS

  • philcart

    SSC-Forever

    Points: 47792

    Very nice.

    Running SQL 2012 11.0.3412 (SP1 + CU9), needed to add this to the sp_send_dbmail execute, otherwise results were in the email body.

    ,@attach_query_result_as_file = 1

    --------------------
    Colt 45 - the original point and click interface

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4482

    Dagnabbit....

    You allways miss something when you share a script,

    Thanks CMS / Phil 🙂

    Updated script (v1.2) can be downloaded at: http://www.ekelmans.com/FTP/Web/GoogleJobTimelineV1.2.sql

  • k.schouten

    Mr or Mrs. 500

    Points: 526

    I've been waiting very long for a script like this.

    I used the alternate link to download the script and had to remove the third of these 3 lines:

    ,@query_result_no_padding = 1 -- prevent SQL adding padding spaces in the result

    --,@query_no_truncate= 1 -- mutually exclusive with @query_result_no_padding

    ,@query_no_truncate= 1

    Kees

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4482

    Hi Kees,

    Those switches are optimizations that only are usefull with larger HTML outputs.

    Did you download the updated version (v1.2)? http://www.ekelmans.com/FTP/Web/GoogleJobTimelineV1.2.sql

    Grtz, Theo

  • k.schouten

    Mr or Mrs. 500

    Points: 526

    Yes i did load that version.

    On the second line your comment says: Mutually exclusive with the first line. So if the third line has the same code as the second line, it will cause an error when executing the code:

    Msg 22050, Level 16, State 1, Line 0

    Failed to execute the query because the @query_result_no_append and @query_no_truncate options are mutually exclusive.

  • Theo Ekelmans

    SSCarpal Tunnel

    Points: 4482

    Ah, i see!!

    There is a chaching issue @yahoo hosting.

    Here is the new link: http://www.ekelmans.com/FTP/Web/GoogleJobTimelineV1.2.sql

  • sal.at.york

    SSC Journeyman

    Points: 91

    This is really useful, however, I have multiple servers and don't want an email for each server. I'd rather see them all in one large chart to identity any resource bottlenecks/ clashes. I might have to look at using an SSIS job to iterate through all the instances and plug the graph data into a central repository before emailing it. I'll have a play around with it. Thanks for posting.

  • sequelgarrett

    Right there with Babe

    Points: 795

    This is really neat. If I didn't have Litespeed I would definitely use this.

  • k.schouten

    Mr or Mrs. 500

    Points: 526

    I ran the script on 2 servers with MSX (Multi-Server Management) and received 2 emails, maybe you can collect the data from all the servers on the MSX-server and later send the email from the MSX-server.

    Kees

  • mauriciorpp

    Default port

    Points: 1472

    thats some really great article. simple presentation and nice explanation of the code involved. would it be possible to share the code for disk space and CPU usage also? 😀

  • Sonny Childs

    SSC-Addicted

    Points: 411

    I'd also love to see how you pulled in CPU/Disk I/O. Our current server host is having some disk issues, so it would be nice to show them when they're happening.

    A 200GB backup shouldn't take 9 hours.

    Stuff like this is why I keep coming back to SSC.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Great idea, thanks so much.

  • matt6288

    SSCertifiable

    Points: 5058

    This is really cool and I can't wait to play around with this for different reporting needs. However, I attempted to run this and received this error:

    Failed to initialize sqlcmd library with error number -2147467259.

    After some google-fu, it appears that the sp_send_dbmail doesn't like the @query parameter. I confirmed this by commenting out that parameter and running successfully. I am running SQL Server 2014 - 12.0.2402.0 (X64). Any advice on how to get this working?

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

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