This blog is to learn and share SQL DBA and Azure SQL knowledge among people by Bandaru Ajeyudu & Sudheer Thota
Interview Questions on AOAG in SQL server
Send Email for Service Restarts in SQL server
--Send Email for Service Restarts in SQL server
USE msdb
GO
-- Declare variables for necessary email content
DECLARE @ServerName VARCHAR(128),
@ComputerNamePhysicalNetBIOS VARCHAR(128),
@Datetime DATETIME,
@EmailRecipients VARCHAR(512),
@EmailSubject VARCHAR(128),
@MessageBody VARCHAR(512)
-- Set variables to proper values
SELECT @ComputerNamePhysicalNetBIOS = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(128)),
@ServerName = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(128)),
@Datetime = GETDATE(),
@EmailRecipients = 'bajeyudu@sqldbanow.com', -- if more than one email address use ; between email addresses
@EmailSubject = 'SQL Server Services Have Been Started!!!'
SELECT @MessageBody = 'SQL Server services have been started on a SQL Server Instance named ' + @ServerName + CHAR(13) +
'running on windows server ' + @ComputerNamePhysicalNetBIOS + '.' + CHAR(13) + CHAR(13) +
'Investigate the service restart if it has not been communicated.'
EXEC sp_send_dbmail
@recipients = @EmailRecipients,
@subject = @EmailSubject,
@body = @MessageBody,
@body_format = 'TEXT'
Suspect Pages Alert in SQL Server
--By using below mentioned script will get suspect pages report via email
declare @count integer
declare @tableHTML nvarchar(MAX);
declare @subj nvarchar(100)
select @count=count(1)
from msdb.dbo.suspect_pages;
set @subj = 'Suspect Pages Found in ' + @@SERVERNAME;
set @tableHTML =
N'<H1>Suspect Pages Found in ' + @@SERVERNAME + ', details are below.</H1>' +
N'<table border="1">' +
N'<tr><th>Database ID</th><th>Database</th>' +
N'<th>File ID</th><th>File</th><th>Page ID</th>' +
N'<th>Event Desc</th><th>Error Count</th><th>Last Updated</th></tr>' +
cast ( ( select td = sp.database_id, '',
td = d.name, '',
td = sp.file_id, '',
td = mf.physical_name, '',
td = sp.page_id, '',
td = case when sp.event_type = 1 then '823 or 824 error other than a bad checksum or a torn page'
when sp.event_type = 2 then 'Bad checksum'
when sp.event_type = 3 then 'Torn Page'
when sp.event_type = 4 then 'Restored (The page was restored after it was marked bad)'
when sp.event_type = 5 then 'Repaired (DBCC repaired the page)'
when sp.event_type = 7 then 'Deallocated by DBCC'
end, '',
td = sp.error_count, '',
td = sp.last_update_date
from msdb.dbo.suspect_pages sp
inner join sys.databases d on d.database_id=sp.database_id
inner join sys.master_files mf on mf.database_id=sp.database_id and mf.file_id=sp.file_id
for xml path('tr'), TYPE
) as nvarchar(max) ) +
N'</table>' ;
IF @count > 0
exec msdb.dbo.sp_send_dbmail
@recipients=N'bandaruajeyudu@outlook.com',
@body= @tableHTML,
@subject = @subj,
@body_format = 'HTML',
@profile_name ='SQLMail'
GO
delete from msdb.dbo.suspect_pages
where last_update_date < getdate()-90;
SQL Jobs Failed report via email from SQL Server
create table Failed_Jobs (
[Status] [varchar](6) NOT NULL,
[Job Name] [varchar](100) NULL,
[Step ID] [varchar](5) NULL,
[Step Name] [varchar](30) NULL,
[Start Date Time] [varchar](30) NULL,
[Message] [nvarchar](4000) NULL)
insert into Failed_Jobs
select 'FAILED' as Status, cast(sj.name as varchar(100)) as "Job Name",
cast(sjs.step_id as varchar(5)) as "Step ID",
cast(sjs.step_name as varchar(30)) as "Step Name",
cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',
sjh.message as "Message"
from sysjobs sj
join sysjobsteps sjs
on sj.job_id = sjs.job_id
join sysjobhistory sjh
on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_id
where sjh.run_status <> 1
and cast(sjh.run_date as float)*1000000+sjh.run_time >
cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at 7am
union
select 'FAILED',cast(sj.name as varchar(100)) as "Job Name",
'MAIN' as "Step ID",
'MAIN' as "Step Name",
cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),'.','-')+' '+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) 'Start Date Time',
sjh.message as "Message"
from sysjobs sj
join sysjobhistory sjh
on sj.job_id = sjh.job_id
where sjh.run_status <> 1 and sjh.step_id=0
and cast(sjh.run_date as float)*1000000+sjh.run_time >
cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at
declare @cnt int
select @cnt=COUNT(1) from Failed_Jobs
if (@cnt > 0)
begin
declare @strsubject varchar(100)
select @strsubject='Check the following failed jobs on ' + @@SERVERNAME
declare @tableHTML nvarchar(max);
set @tableHTML =
N'<H1>Failed Jobs Listing - ' + @@SERVERNAME +'</H1>' +
N'<table border="1">' +
N'<tr><th>Status</th><th>Job Name</th>' +
N'<th>Step ID</th><th>Step Name</th><th>Start Date</th>' +
N'<th>Message</th></tr>' +
CAST ( ( SELECT td = [Status], '',
td = [Job Name], '',
td = [Step ID], '',
td = [Step Name], '',
td = [Start Date Time], '',
td = [Message]
FROM Failed_Jobs
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@from_address='BANDARUAJEYUDU@OUTLOOK.com',
@recipients='BANDARUAJEYUDU@OUTLOOK.com',
@subject = @strsubject,
@body = @tableHTML,
@body_format = 'HTML' ,
@profile_name='SQLMail'
end
drop table Failed_Jobs
Automatic alert when cluster fail over happened
When fail over happen, both SQL server and Agent service will get restarted, Then we will get Notification Alert When Fail Over Happened
DECLARE
@importance AS VARCHAR(6)
DECLARE
@body AS NVARCHAR(1000)
DECLARE
@Subject AS NVARCHAR(1000)
DECLARE
@InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)
DECLARE
@recipients VARCHAR(100)
SELECT
@InstanceName =@@SERVERNAME
SELECT
@NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT
@Subject = 'Fail
over Happened for Instance '+@InstanceName
SELECT
@body = 'Fail over
Happened for Instance '+@InstanceName + '. This instance is
currently running on the node '+@NodeName
SELECT
@importance ='High'
EXEC
msdb.dbo.sp_send_dbmail
@profile_name ='sqlmail',
@recipients ='bajeyudu@SQLDBANOW.com;',
--@recipients=@recipientsList,
@subject = @subject ,
@body = @body,
@body_format = 'HTML'
,
@importance=@importance