Tag Archives: mssql

MSSQL – Always On availability group – Get sync status

If you wan to know the sync status of a always On availability group database replication, this command can be used:

SELECT 
	ar.replica_server_name, 
	adc.database_name, 
	ag.name AS ag_name, 
	drs.is_local, 
	drs.is_primary_replica, 
	drs.synchronization_state_desc, 
	drs.is_commit_participant, 
	drs.synchronization_health_desc, 
	drs.recovery_lsn, 
	drs.truncation_lsn, 
	drs.last_sent_lsn, 
	drs.last_sent_time, 
	drs.last_received_lsn, 
	drs.last_received_time, 
	drs.last_hardened_lsn, 
	drs.last_hardened_time, 
	drs.last_redone_lsn, 
	drs.last_redone_time, 
	drs.log_send_queue_size, 
	drs.log_send_rate, 
	drs.redo_queue_size, 
	drs.redo_rate, 
	drs.filestream_send_rate, 
	drs.end_of_log_lsn, 
	drs.last_commit_lsn, 
	drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
	ON drs.group_id = adc.group_id AND 
	drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
	ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
	ON drs.group_id = ar.group_id AND 
	drs.replica_id = ar.replica_id
ORDER BY 
	ag.name, 
	ar.replica_server_name, 
	adc.database_name;

MSSQL Backup History Query

To see MSSQL history Query, run this SQL query. It will show all backups between the 1 january 2021 and 1 february 2021, order by Database and then Last backup time.

It will also show if the backup is a CopyOnly job.

;WITH CTE_Backup AS
(
SELECT  database_name,is_copy_only,backup_start_date,type,physical_device_name
       ,Row_Number() OVER(PARTITION BY database_name,BS.type
        ORDER BY backup_start_date DESC) AS RowNum
FROM    msdb..backupset BS
JOIN    msdb.dbo.backupmediafamily BMF
ON      BS.media_set_id=BMF.media_set_id
AND		BS.backup_start_date >= '01/01/2021'
AND		BS.backup_start_date <= '02/02/2021'
)
SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc,
            CASE WHEN type ='D' THEN 'Full database'
            WHEN type ='I' THEN 'Differential database'
            WHEN type ='L' THEN 'Log'
            WHEN type ='F' THEN 'File or filegroup'
            WHEN type ='G' THEN 'Differential file'
            WHEN type ='P' THEN 'Partial'
            WHEN type ='Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
           ,physical_device_name,CTE.is_copy_only
FROM        sys.databases D
LEFT JOIN   CTE_Backup CTE
ON          D.name = CTE.database_name

ORDER BY    D.name,last_backup_time

MSSQL SP_WHO2 order by db / databases

Order sp_who2, after database with this commands:

declare @tempTable table (SPID INT,Status VARCHAR(255),
Login VARCHAR(255),HostName VARCHAR(255),
BlkBy VARCHAR(255),DBName VARCHAR(255),
Command VARCHAR(255),CPUTime INT,
DiskIO INT,LastBatch VARCHAR(255),
ProgramName VARCHAR(255),SPID2 INT,
REQUESTID INT);

 INSERT INTO @tempTable
EXEC sp_who2 

select *
from @tempTable order by DBName

MSSQL – check that the Agent Job have been run as Schedule

I have this problems, that if a schedule inside a Agent job, have not been started for some reason, there will be no errors about this problem.

So Í have created this script, that will give the Status of 1, if the job, haven’t been run for the last two hours.
I used PRTG to check the status.

USE msdb
GO

DECLARE @JobName VARCHAR(1000)
DECLARE @LastRunTime VARCHAR(1000)
DECLARE @LastRunStatus VARCHAR(1000)
DECLARE @CurrentTime VARCHAR(20)

SET @JobName = 'JOBNAME'

SET @CurrentTime = LEFT(REPLACE(CONVERT (time, DATEADD(HOUR, - 2, GETDATE())),':',''),6)

SELECT DISTINCT @LastRunTime = SJH.run_time
FROM sysjobhistory SJH, sysjobs SJ
WHERE SJ.[name] = @JobName AND SJH.job_id = SJ.job_id and SJH.run_date = 
(SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id)

IF @LastRunTime >= @CurrentTime
	SELECT '0' AS Status
 ELSE
	SELECT '1' AS Status

In line 10, replace – 2 with the number of hours, you want to go back

Below is the same script, just for daily jobs, so it will will check if the job haven’t been run in the last 25 hours (My Schedule is set to run every 24 hours, and i give it an hour to be finish, before i got an alarm.

DECLARE @JobName VARCHAR(1000)
DECLARE @LastRunDate VARCHAR(1000)
DECLARE @LastRunStatus VARCHAR(1000)
DECLARE @CurrentDate VARCHAR(20) -- used for file name

SET @JobName = 'JOBNAME'

SET @CurrentDate = REPLACE(CONVERT (date, DATEADD(HOUR, -25, GETDATE())),'-','') 

SELECT DISTINCT @LastRunDate = SJH.run_date
FROM sysjobhistory SJH, sysjobs SJ
WHERE SJ.[name] = 'Fibia optimering daglig (Batch 2)' AND SJH.job_id = SJ.job_id and SJH.run_date = 
(SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id)
ORDER BY SJH.run_date desc

IF @LastRunDate >= @CurrentDate
	SELECT '0' as Status
 ELSE
 	SELECT '1' as Status

In line 10, replace – 25 with the number of hours, you want to go back

Implement SQL Server Agent Jobs with AlwaysOn Availability Groups

The function I use fn_hadr_group_is_primary from this post is a UDF (User Defined Function) that we create in the master database. The function gets an Availability Group name as an input parameter and returns a Boolean value indicating weather this instance is a primary replica.

-- fn_hadr_group_is_primary
USE master;
GO
IF OBJECT_ID('dbo.fn_hadr_group_is_primary', 'FN') IS NOT NULL
  DROP FUNCTION dbo.fn_hadr_group_is_primary;
GO
CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit
AS
BEGIN;
  DECLARE @PrimaryReplica sysname; 

  SELECT
    @PrimaryReplica = hags.primary_replica
  FROM sys.dm_hadr_availability_group_states hags
  INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
  WHERE ag.name = @AGName;

  IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
    RETURN 1; -- primary

    RETURN 0; -- not primary
END; 

Use the fn_hadr_group_is_primary function within a new job step to find if this sql instance is a Primary replica. If this is not a primary replica we issue a stop job request while identifying the current job name using SQL Server Agent Tokens

-- Detect if this instance's role is a Primary Replica.
-- If this instance's role is NOT a Primary Replica stop the job so that it does not go on to the next job step
DECLARE @rc int; 
EXEC @rc = master.dbo.fn_hadr_group_is_primary N'my-ag';

IF @rc = 0
BEGIN;
    DECLARE @name sysname;
    SELECT  @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, '$(ESCAPE_NONE(JOBID))'));
    
    EXEC msdb.dbo.sp_stop_job @job_name = @name;
    PRINT 'Stopped the job since this is not a Primary Replica';
END;

Monitoring a Automatic Seeding in msSQL Always on Availability Groups

This command, can be run on both the primary and secondary node.

SELECT local_database_name
 ,role_desc
 ,internal_state_desc
 ,transfer_rate_bytes_per_second
 ,transferred_size_bytes
 ,database_size_bytes
 ,start_time_utc
 ,end_time_utc
 ,estimate_time_complete_utc
 ,total_disk_io_wait_time_ms
 ,total_network_wait_time_ms
 ,is_compression_enabled
FROM sys.dm_hadr_physical_seeding_stats