Month: August 2021

Removing duplicate rows

No need to over develop this – once you realise that the DELETE command can include the TOP option.

More? Ok. Create a SELECT command that shows the issue. EG:-

SELECT ID
FROM dbo._op2
WHERE ID = 'X123456';

Add TOP to return just the unwanted rows. IE: if the above query should return 1 row but erroneously returns 2 rows use TOP(1), if it returns 5 rows use TOP(4) …

(NOTE: It does not matter which particular rows get deleted … they are duplicates remember)

SELECT TOP(1) ID
FROM dbo._op2
WHERE ID = 'X123456';

Change SELECT to DELETE and remove the column name …

DELETE TOP(1)
FROM dbo._op2
WHERE ID = 'X123456';

… and only run it once 😉

Stop notification spam

Although I have previously tackled this problem by rolling my own notifications. This time I realised that the SQL jobs that run every minute or two are of low importance – and I don’t really want emails when they fail. I will notice at some point during the working day.

Here is the code to lists jobs that run frequently and are configured to send notification emails. Along with the commands to remove those notifications.

/* JobNotificationEmailStop.sql */

SELECT S.[name] JobName,
    SS.freq_subday_interval [ScheduleFreq(mins)],
    'EXEC msdb.dbo.sp_update_job @job_name = N''' 
    + S.[name] 
    + ''', @notify_email_operator_name = N'''';' 
    CommandToDisableEmailNotification

  FROM msdb.dbo.sysjobs S
  JOIN msdb.dbo.sysjobschedules SJ
    ON S.job_id = SJ.job_id
  JOIN msdb.dbo.sysschedules SS
    ON SS.schedule_id = SJ.schedule_id

 WHERE SS.freq_subday_interval > 0
   AND S.notify_level_email > 0
 ORDER BY SS.freq_subday_interval;