favorite SPs?
-
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?
-
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.
-
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?
-
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
-
Interesting, can you expand on how you do this? Do you use DTS on one machine to deploy out the "favorites" to others?
-
How come you're using blat instead of xp_sendmail (or mail equivalent)?
-
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