Job History

SELECT job.name As JobName,
history.step_name,
CAST(msdb.dbo.agent_datetime(history.run_date, history.run_time) AS DATE) AS Run_Start_Date,
msdb.dbo.agent_datetime(history.run_date, history.run_time) AS Run_Start_date_time,
STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') 'Run_time',


STUFF(
STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration AS VARCHAR(8)), 8), 3, 0, ':'), 6, 0, ':'),
9,
0,
':'
) 'duration'
, msdb.dbo.fn_get_job_end_datetime(history.run_date, history.run_time, history.run_duration)
as Run_End_date_time,

DATENAME(weekday,CAST(msdb.dbo.agent_datetime(run_date, run_time) AS DATE)) AS Run_DayofWeek,

history.step_name,
Case
When history.run_status = 0 then 'Failed'
When history.run_status = 1 then 'Succeeded'
When history.run_status = 2 then 'Retry'
When history.run_status = 3 then 'Canceled'
end as Run_status,
message,
instance_id,
step.command,
history.job_id,
history.step_id,
sql_message_id,
sql_severity,

--run_date,
--run_time,
--run_duration,
AVG(history.run_duration) OVER (PARTITION BY history.job_id) AS AverageRuntime,
MAX(history.run_duration) OVER (PARTITION BY history.job_id) AS MaxRuntime,
MIN(history.run_duration) OVER (PARTITION BY history.job_id) AS MinRuntime,
 history.server
FROM msdb..sysjobhistory history
 JOIN msdb..sysjobs job
ON job.job_id = history.job_id
left outer JOIN msdb..sysjobsteps step
ON step.job_id = job.job_id
AND step.step_id = job.start_step_id
WHERE
--history.step_id = 0 AND
CAST(msdb.dbo.agent_datetime(run_date, run_time) AS DATE) >= GETDATE() - 3
and job.name not in ('DevOps - PerfMon Counter Collection' , 'Shrink_All_Log_Files', 'DevOps - Load Session Status per minute', 'DevOps - Update Stats USER_DATABASES', 'DevOps - Load SystemHealthSession' , 'syspolicy_purge_history', 'SSIS Server Maintenance Job', 'Database_File_Growth_Data_Collection') --- filter out noise
ORDER BY Run_Start_date_time  DESC;

Distributor and Publisher Information Script

-- https://docs.microsoft.com/en-us/sql/relational-databases/replication/administration/distributor--- --and-publisher-information-script
--********** Execute at the Distributor in the master database **********--

USE master;
go

--Is the current server a Distributor?
--Is the distribution database installed?
--Are there other Publishers using this Distributor?
EXEC sp_get_distributor

--Is the current server a Distributor?
SELECT is_distributor FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername;

--Which databases on the Distributor are distribution databases?
SELECT name FROM sys.databases WHERE is_distributor = 1

--What are the Distributor and distribution database properties?
EXEC sp_helpdistributor;
EXEC sp_helpdistributiondb;
EXEC sp_helpdistpublisher;

--********** Execute at the Publisher in the master database **********--

--Which databases are published for replication and what type of replication?
EXEC sp_helpreplicationdboption;

--Which databases are published using snapshot replication or transactional replication?
SELECT name as tran_published_db FROM sys.databases WHERE is_published = 1;
--Which databases are published using merge replication?
SELECT name as merge_published_db FROM sys.databases WHERE is_merge_published = 1;

--What are the properties for Subscribers that subscribe to publications at this Publisher?
EXEC sp_helpsubscriberinfo;

--********** Execute at the Publisher in the publication database **********--

USE RollbackDB;
go

--What are the snapshot and transactional publications in this database?  
EXEC sp_helppublication;
--What are the articles in snapshot and transactional publications in this database?
--REMOVE COMMENTS FROM NEXT LINE AND REPLACE with the name of a publication  
--EXEC sp_helparticle @publication='';  

--What are the merge publications in this database?  
EXEC sp_helpmergepublication;
--What are the articles in merge publications in this database?
EXEC sp_helpmergearticle; -- to return information on articles for a single publication, specify @publication=''  

--Which objects in the database are published?
SELECT name AS published_object, schema_id, is_published AS is_tran_published, is_merge_published, is_schema_published
FROM sys.tables WHERE is_published = 1 or is_merge_published = 1 or is_schema_published = 1
UNION
SELECT name AS published_object, schema_id, 0, 0, is_schema_published
FROM sys.procedures WHERE is_schema_published = 1
UNION
SELECT name AS published_object, schema_id, 0, 0, is_schema_published
FROM sys.views WHERE is_schema_published = 1;

--Which columns are published in snapshot or transactional publications in this database?
SELECT object_name(object_id) AS tran_published_table, name AS published_column FROM sys.columns WHERE is_replicated = 1;

--Which columns are published in merge publications in this database?
SELECT object_name(object_id) AS merge_published_table, name AS published_column FROM sys.columns WHERE is_merge_published = 1;

Replication Undistributed commands

SELECT DISTINCT
ma.article,
ma.publisher_db,
ms.subscriber_db,
ma.source_object,
ma.destination_object,
md.UndelivCmdsInDistDB AS 'Transactions to be processed',
md.DelivCmdsInDistDB AS 'Transactions processed'
FROM
distribution.dbo.MSarticles AS ma WITH (NOLOCK)
INNER JOIN
distribution.dbo.MSdistribution_status AS md WITH (NOLOCK)
ON ma.article_id = md.article_id
INNER JOIN
distribution.dbo.MSsubscriptions AS ms WITH (NOLOCK)
ON ma.article_id = ms.article_id AND md.article_id = ms.article_id
WHERE
(md.UndelivCmdsInDistDB > 0)
ORDER BY
ma.publisher_db, 'Transactions to be processed' DESC

Maintaining SQL Replication Publications -or- How To Add An Article To A Publication AND Generate A Snapshot

Maintaining SQL Replication Publications -or- How To Add An Article To A Publication AND Generate A Snapshot

http://mattslocumsql.blogspot.com/2013/07/maintaining-sql-replication.html

ReSync AG

USE Master;
GO
SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
DECLARE @AgName sysname
-- 2 - Initialize variables
SET @AgName = 'SUCoreAG'
SET @dbName = 'Community'
SET @backupPath = 'H:\logship\' +@dbName +'\'

-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd

-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
   AND backupFile LIKE @dbName + '%'

SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd

-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
   AND backupFile LIKE @dbName + '%'
   AND backupFile > @lastFullBackup

-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
   SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
   PRINT @cmd
   SET @lastFullBackup = @lastDiffBackup
END

-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
   SELECT backupFile
   FROM @fileList
   WHERE backupFile LIKE '%.TRN'
   AND backupFile LIKE @dbName + '%'
   --AND backupFile > @lastFullBackup

OPEN backupFiles

-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
       + @backupPath + @backupFile + ''' WITH NORECOVERY'
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @backupFile
END

CLOSE backupFiles
DEALLOCATE backupFiles




-- 6 - Add Databases to AD
 SET @cmd = '-- ALTER DATABASE [' + @dbName + '] SET HADR AVAILABILITY GROUP =' + @AgName
 PRINT @cmd

Paul S. Randal WaitingTasks

http://www.sqlskills.com/blogs/paul/updated-sys-dm_os_waiting_tasks-script-2/

/*============================================================================

  File:     WaitingTasks.sql

 

  Summary:  Snapshot of waiting tasks

 

  SQL Server Versions: 2005 onwards

------------------------------------------------------------------------------

  Written by Paul S. Randal, SQLskills.com

 

  (c) 2016, SQLskills.com. All rights reserved.

 

  For more scripts and sample code, check out

    http://www.SQLskills.com

 

  You may alter this code for your own *non-commercial* purposes. You may

  republish altered code as long as you include this copyright and give due

  credit, but you must obtain prior permission before blogging this code.

   

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED

  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

  PARTICULAR PURPOSE.

============================================================================*/

SELECT

    [owt].[session_id],

    [owt].[exec_context_id],

    [ot].[scheduler_id],

    [owt].[wait_duration_ms],

    [owt].[wait_type],

    [owt].[blocking_session_id],

    [owt].[resource_description],

    CASE [owt].[wait_type]

        WHEN N'CXPACKET' THEN

            RIGHT ([owt].[resource_description],

                CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)

        ELSE NULL

    END AS [Node ID],

    [eqmg].[dop] AS [DOP],

    [est].text,

    [er].[database_id],

    [eqp].[query_plan],

    [er].[cpu_time]

FROM sys.dm_os_waiting_tasks [owt]

INNER JOIN sys.dm_os_tasks [ot] ON

    [owt].[waiting_task_address] = [ot].[task_address]

INNER JOIN sys.dm_exec_sessions [es] ON

    [owt].[session_id] = [es].[session_id]

INNER JOIN sys.dm_exec_requests [er] ON

    [es].[session_id] = [er].[session_id]

FULL JOIN sys.dm_exec_query_memory_grants [eqmg] ON

    [owt].[session_id] = [eqmg].[session_id]

OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]

OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]

WHERE

    [es].[is_user_process] = 1

ORDER BY

    [owt].[session_id],

    [owt].[exec_context_id];

GO