SQL Agent Job: Update the notification operator

I found many SQL Agent job were enabled but no any  notifications setup in case of failure.

I ran the following

use msdb
GO
select * from dbo.sysjobs

where notify_level_email = 0

and enabled = 1

--- gives the list of jobs enabled but no notification set up.

SELECT [ID], [Name], [Enabled]

FROM MSDB.dbo.sysoperators

WHERE [Enabled] = 1

ORDER BY [Name];
Go

-- Gives the list of operators. Pick the Id of the one you want to notify to use in the below script








UPDATE S

SET S.[notify_level_email] = 2,

S.[notify_email_operator_id] = 2   ---my operator

FROM MSDB.dbo.sysjobs S

WHERE S.[Notify_Level_Email] = 0

AND S.[Enabled] = 1;




GO

DMV:Indexes

To check of any primary key is a NOT a clustered index


select OBJECT_SCHEMA_NAME(so.[object_id]) as schemaname ,

so.name as TableName ,

si.name as IndexName,

si.type as IndexType,

si.is_primary_key

from sys.indexes si

inner join sys.tables so on

si.[object_id] = so.[object_id]

where si.type in (0,2)

and si.is_primary_key = 1

order by so.name

CDC error: 'The specified '@server' is invalid (valid values are returned by sp_helpserver)

I encountered the following error when I was enabling the CDC for the db1.dbo.pat_table


Could not update the metadata that indicates table [dbo].[pat_Table] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database db1 to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_jobstep_internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.

After researching around I  understood that the above error is caused due to mismatch in value between SERVERPROPERTY(‘ServerName’)) and master.dbo.sysservers

I solved the issue as follows -

select @@SERVERNAME --oldname

select SERVERPROPERTY('ServerName') --defaultname


--1 drop the old servername
  exec sp_dropserver 'oldservername'




--2 add new servername
  exec sp_addserver 'newservername', 'local'




--3 see changes
  exec sp_helpserver

Monitor disk space

/** This script checks the current space of the disks and if the available space is lower that the threshold (GB in this case) sends out email**/


use MyAdminDB
GO

declare @from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
　
　
　
SET NOCOUNT ON

DECLARE @list nvarchar(2000) = '';

WITH core AS (
　
　
SELECT DISTINCT

　
　
s.volume_mount_point [Drive],
　
　
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
　
　
FROM
　
　
sys.master_files f
　
　
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
　
　
)
　
　
　
　
　
　
　
　
　
SELECT @list = @list + ' ' + Drive + ', current space available =' + cast(cast(round([AvailableMBs]*1.000/1024.000,2) as numeric(36,2)) as varchar) + 'GB'
　
　
FROM core
　
　
WHERE AvailableMBs < @threshold
　
　
IF LEN(@list) > 3 BEGIN
　
　
DECLARE @msg varchar(500) = 'Disk Space les than 8GB Notification. The following drives are currently reporting less than '
　
　
+ CAST(@threshold/1024 as varchar(12)) + ' GB free: ' + @list
　
　
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile@mydomain.com',
@recipients = @to,
@subject = @subject,
@body = @msg
END

RETURN 0
　
　
　
　
　
GO