PowerShell: Generate a report of failed SQL Agent jobs

$cms ="CMSServer"
$serverlist = invoke-sqlcmd -serverinstance $cms -database 'CMSCentralDB' -query "
    SELECT DISTINCT ServerName FROM AllSQLServers"

$qry = "
USE [msdb]

SELECT  @@SERVERNAME ServerName,
        j.name JobName,
        js.last_outcome_message LastOutcomeMessage,
        LastRunTime = dbo.agent_datetime(
            CASE WHEN js.last_run_date = 0 THEN NULL ELSE js.last_run_date END, 
            CASE WHEN js.last_run_time = 0 THEN NULL ELSE js.last_run_time END) 
FROM    dbo.sysjobs j
        INNER JOIN dbo.sysjobservers js 
        ON j.job_id = js.job_id
  WHERE js.last_run_outcome = 0
  AND j.name like 'dba%'
  AND j.job_id NOT IN (  
select job_id from msdb.dbo.sysjobactivity
where start_execution_date IS NOT NULL AND stop_execution_date IS NULL
AND session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity) )
"

$results = new-object object[] 0

foreach ($server in $serverlist)
{ 
$server.ServerName
trap [Exception] {continue}   
     $results += invoke-sqlcmd -Query $qry –serverinstance $server.ServerName -database 'msdb'    
}

foreach ($r in $results)  
{      
        $ServerName = $r.ServerName
        $JobName = $r.JobName
        $LastOutcomeMessage = $r.LastOutcomeMessage
        $LastRunTime = $r.LastRunTime
          
        invoke-sqlcmd -serverinstance $cms -database 'CMSCentralDB' -query " 
                 insert dbo.SQLAgentJobsFailed (ServerName, JobName, LastOutcomeMessage, LastRunTime) select  '$ServerName','$JobName','$LastOutcomeMessage','$LastRunTime'"
}

 

ColumnStore Indexes concepts and maintenance

What are Columnstore Indexes?

Understanding Columnstore Indexes in SQL Server Part 1

https://www.sqlpassion.at/archive/2017/01/30/columnstore-segment-elimination/

https://www.sqlpassion.at/archive/2016/03/21/clustered-columnstore-indexes-space-savings/

https://techcommunity.microsoft.com/t5/sql-server/columnstore-index-defragmentation-using-reorganize-command/ba-p/384653#

https://techcommunity.microsoft.com/t5/datacat/sql-2016-columnstore-row-group-merge-policy-and-index/ba-p/305255#

https://techcommunity.microsoft.com/t5/sql-server/adaptive-index-defrag/ba-p/383893

 

https://fonsecasergio.wordpress.com/2019/04/15/lesson-learned-1-columnstore-compressed-rowgroups-clustered-vs-nonclustered/

Rebuild Partitioned Clustered Columnstore Indexes

 

 

 

 

 

Useful SQL scripts to extract replication metadata about articles and columns

Show Articles And Columns For All Publications

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpsubscription-transact-sql?view=sql-server-ver15

Useful PowerShell code to capture sp_helpsubscription output into a table

invoke-sqlcmd -serverinstance PublicationServer -database 'PublicationDatabase' -query sp_helpsubscription -OutputAs DataTables |             
Write-SqlTableData -ServerInstance PublicationServer -DatabaseName DBAMonitoringDB -SchemaName dbo -TableName PublicationDatabasesubscriptions -Force

SQL to extract replication articles information

USE DBAMonitoringDB
GO

select @@SERVERNAME source_server, p.publication publication_name, 'PublicationDatabase' source_database_name, s.article source_table_name,p.Columns source_list_of_columns, 
subscriber destination_server, [destination database], s.article destination_table_name,p.Columns 
destination_list_of_columns  from PublicationDatabasepublications p 
join PublicationDatabasesubscriptions s on s.publication=p.publication and p.article=s.article

PublicationDatabasepublications table extracted through below SQL in above URL Show Articles And Columns For All Publications

USE 'PublicationDatabase'


GO

SELECT syspublications.NAME                AS "Publication",        sysarticles.NAME                    AS "Article",        Stuff((SELECT ', ' + syscolumns.NAME AS [text()]               FROM   sysarticlecolumns WITH (nolock)                      INNER JOIN syscolumns WITH (nolock)                              ON sysarticlecolumns.colid = syscolumns.colorder               WHERE  sysarticlecolumns.artid = sysarticles.artid                      AND sysarticles.objid = syscolumns.id               ORDER  BY syscolumns.colorder               FOR xml path('')), 1, 2, '') AS "Columns" FROM   syspublications WITH (nolock)        INNER JOIN sysarticles WITH (nolock)                ON syspublications.pubid = sysarticles.pubid ORDER  BY syspublications.NAME,           sysarticles.NAME;

 

Extract consolidated list of SQL Servers from Central Management Server hierarchy

SELECT DISTINCT     
  c.name as Environment ---Higher level 1 folders   
  , a.server_name as ServerName    
  , case when c.name = 'PRD' then 1    
  when c.name = 'STG' then 2    
  when c.name = 'QA' then 3    
  when c.name = 'DEV' then 4    
  end as CriticalityOrder    
  , substring(b.name, CHARINDEX('SQL', b.name), LEN(b.name)) as SQLVersion ---If subfolder names has got SQL Server versions    
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal a    
JOIN msdb.dbo.sysmanagement_shared_server_groups_internal b    
    ON a.server_group_id = b.server_group_id    
JOIN msdb.dbo.sysmanagement_shared_server_groups_internal c    
    ON b.parent_id = c.server_group_id    
WHERE c.parent_id = ID number of the root container level 0