HOW TO CHECK YOUR MIRROING IS RUNNING OR NOT ?
you can Check :
1- port number listener : open CMD and write this Command ” netstat -an ” you will see the 3 port for the 3 Servers Principle – mirroring – Witness also I see the 3 port because I setup the 3 SQL Server Instance in the same Windows Server
2- Event Viewer for Windows Server
3- SQL Server Database Log
4- Check Endpoint Status
If you Create the 3 SQL Servers in the same PC
Begin
you must use difference port
Principle Server use the Stander port number 5021
Mirroring Server use the Stander port number 5022
Witness Server use the Stander port number 5023
End
Else The 3 SQL in 3 different PC
You can use one port like 5021
Don’t Forget to Disable Firewall or must be open this ports on the Firewall
***************************************************Introduction********************************************************
Today i will Dessecus how to Get all Database job history then Send Email to The Technical Support “job History Report” then i will delete this log history
Get Daatabase job history
select * from msdb.dbo.sysjobs
Get database job Step history
select * from msdb.dbo.sysjobhistory
Now i will make join between msdb.dbo.sysjobhistory and msdb.dbo.sysjobs by Job_ID and i will Convert the job tun Date time because if you select it without convert you will sedd the Data Formate “20121224” so i will convert it , also i have column name “Run_Status” this Column return 0 or 1 { 0 = the job is Failed , 1 = The job is Success } so i will make Case when on this Column this Script you can use it for to ways
1- job Failed history report
2- job Success history Report
but i will write now ho to get job all history Failed and Success
select J.job_id,J.nameas[Job Name],S.step_name,S.step_id,
CONVET (DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 asRun_Date_Time,
RIGHT(‘000000’+CONVERT(varchar(6),S.run_duration), 6)as[Run Duration],
CASE S.run_status
WHEN 0 THEN‘JOB FAIL’
WHEN 1 THEN‘JOB Success’
END
,S.message,S.server, CONVERT(DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 asRun_Date_Time
from msdb.dbo.sysjobhistory as S innerjoin msdb.dbo.sysjobs as J
on s.job_id=J.job_id
where Step_id<> 0
/*
************************************ Send Email With Database job History Report**************************************
If you need to know hot Configure your database mail See this post in the First http://wp.me/p1Oidq-9A
*/
DECLARE @xmlNVARCHAR(MAX)
DECLARE @bodyNVARCHAR(MAX)
SET @xml=CAST((SELECTJ.nameAS‘td’,”,S.step_nameAS‘td’,”,S.step_idAS‘td’,”,CASES.run_status
WHEN 0 THEN‘JOB FAIL’
WHEN 1 THEN‘JOB Success’
END AS‘td’,
”,CONVERT(DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 AS‘td’,”,S.messageAS‘td’
from msdb.dbo.sysjobhistory as S innerjoin msdb.dbo.sysjobs as J
on s.job_id=J.job_id
where Step_id<> 0
FOR XMLPATH(‘tr’),ELEMENTS)ASNVARCHAR(MAX))
SET @body=‘<html><body><H3>Database job history</H3>
<table border = 1>
<tr><font color=blue size=5 >
<td> Job Name </td> <td> Step Name </td> <td> Step ID </td> <td> Job Status </td> <td> Job_Run_Time </td> <td> message </td></font></tr>’
SET @body=@body+@xml+‘</table></body></html>’
EXEC msdb.dbo.sp_send_dbmail
@profile_name =‘DBA’,— replace with your SQL Database Mail Profile
@body =@body,
@body_format =‘HTML’,
@recipients =‘Mostafa@Safeerp.com’,— replace with your email address
@subject =‘Database job History’;
/*
************************************ Cleare job history**************************************************
If you need to know hot Configure your database mail See this post in the First
*/
EXEC MSDB.dbo.sp_purge_jobhistory
@job_name=N’testsenemail’; — JOB Nmar
GO
introduction
Today i will explain how to monitor your backup is Saved on or no also How to Delete the old backup history from the log
How to Check your Backup log or your backup history
we can Check the backup history by this way but before this step i need from you to Create Databae “backupTest” and take backup from it with 3 types Full,DIFF,LOG
select Database_name,backup_start_date,backup_finish_date,type from msdb.dbo.backupset
you can select all Column (*) to see all information
but i will customize it to help you how to check the FULL backup , DiFF backup , Log backup
********************************Check FULL abckup history************************************
SELECT database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected
FROM msdb.dbo.backupset where Type=‘D’ ORDER BY backup_set_id DESC
GO
********************************Check DIFF abckup history************************************
SELECT database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected
FROM msdb.dbo.backupset where Type=‘i’
ORDER BY backup_set_id DESC
********************************Check LOG abckup history************************************
SELECT
database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected
FROM msdb.dbo.backupset where Type=‘L’
ORDER BY backup_set_id DESC
GO
How to Delete your backup history:-
we can make delete for all backup history for any database by this Code
exec MSDB.DBO.sp_delete_database_backuphistory ‘Database name’
So this Code Delete history to the backup but for one database one by one so i will make Cursore to loop on all Server to get all database name then Delete the backup history for all Database
set ANSI_NULLSON
set QUOTED_IDENTIFIERON
go
Create PROC[dbo].DeletebackupHistory
AS
BEGIN
DECLARE @DB Nvarchar(50)
DECLARE GetDB CURSOR FOR
Select Name from Sys.Databases Where database_id> 4 and state_desc=‘ONLINE’
OPEN GetDB
FETCH NEXT FROM GetDB INTO @DB
WHILE @@FETCH_STATUS= 0
BEGIN
—-Delete backup history
exec MSDB.DBO.sp_delete_database_backuphistory @db
——————————-
FETCH NEXT FROM GetDB
INTO @DB
END— FETCH WHILE
CLOSE GetDB
DEALLOCATE GetDB
END
DB Cloud TECH 