Post reply

favorite SPs?

  • March 8, 2004 at 12:30 pm

    #65023

    When you get a new machine, what are some of your favorite sps or triggers to install?

    I use

    SQL SMTP Mail (sp)

    Notify failed job (trigger)

    Look for locks that haven't released (sp)

    Force rotate logs once a month (sp)

    Delete old backup history (sp)

    updatestats (sp)

    reindex routine (sql script)

    Anyone else?

     

  • March 8, 2004 at 1:25 pm

    #497877

    That's about what I implement. Slightly different, I have a central machien that rolls up some of this info. I do add one that checks the version and config of SQL Server daily. Helps catch those "oh yeah, i installed..." items.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • March 8, 2004 at 1:30 pm

    #497880

    pray do tell....notify failed jobs trigger?

    I have a job step right now, but it's a pain in the preverbials to add the step for each job, where is the trigger at?


    Shamless self promotion - read my blog http://sirsql.net

  • March 8, 2004 at 2:05 pm

    #497890

    I thought it was on this web site here, but cannot seem to find it

    (of course I am breaking my rule of using the SMTP SP above)

    /* drop trigger trg_stepfailures */

    alter trigger trg_stepfailures

    on sysjobhistory

    for insert

    as

    declare @strcmd varchar(800),@strRecipient varchar(128),@strMsg varchar(700)

    set @strRecipient = ''

    if exists (select * from inserted where run_status = 0 and step_name <> '(job outcome)')

    begin

     select  @strMsg =  @@servername + ' Job ' + sysjobs.name + ' Step ' +

     inserted.step_name + ' Message ' + inserted.message

     from inserted

     join sysjobs

     on inserted.job_id = sysjobs.job_id

     where inserted.run_status = 0

     

     raiserror (@strMsg, 16, 10) with log

     /* exec master.dbo.xp_sendmail @recipients = @strRecipient,

         @message = @strMsg,

         @subject = 'Job Failure' */

     select @strcmd = 'echo ' + @strMsg + ' > d:\jobs\blat\trigmsg.txt'

     exec master..xp_cmdshell @strcmd

     select @strMsg = 'D:\JOBS\BLAT\blat d:\jobs\blat\trigmsg.txt -t me@work.com -s ' + @@servername + '-SQL_Job_Failed -server smtp.work.com -f sqlalert@work.com -noh'

     exec master..xp_cmdshell @strMsg

       

    end

     

     

  • March 8, 2004 at 2:23 pm

    #497893

    Interesting, can you expand on how you do this?  Do you use DTS on one machine to deploy out the "favorites" to others?

     

  • March 9, 2004 at 2:08 pm

    #498058

    How come you're using blat instead of xp_sendmail (or mail equivalent)?


    Shamless self promotion - read my blog http://sirsql.net

  • March 10, 2004 at 6:45 am

    #498112

    Sorry, I'm relatively new to SQL and from an Oracle background, the procedure - Look for locks that haven't released (sp) sounds exactly what I need at the moment.  Can you point me in a direction for the sample script?

    Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

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