Window Cluster resource

 FailoverClustering

Get index progress in SQL 2004 and above

Copy from answer from Solomon Rutzky on topic SQL Server : HOw To trace progress of Create index command. This query is very useful to trace the progress. Pam Lahoud also mention in SQL 2019, that is new dmv called sys.index_resumable_operations  which trace resume index operation.

I copy script to here for reference and reference URL as below
 https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command

DECLARE @SPID INT = 51;

;WITH agg AS
(
     SELECT SUM(qp.[row_count]) AS [RowsProcessed],
            SUM(qp.[estimate_row_count]) AS [TotalRows],
            MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],
            MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
                    [physical_operator_name],
                    N'<Transition>')) AS [CurrentStep]
     FROM sys.dm_exec_query_profiles qp
     WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan',
                                           N'Index Scan',  N'Sort')
     AND   qp.[session_id] = @SPID
), comp AS
(
     SELECT *,
            ([TotalRows] - [RowsProcessed]) AS [RowsLeft],
            ([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
     FROM   agg
)
SELECT [CurrentStep],
       [TotalRows],
       [RowsProcessed],
       [RowsLeft],
       CONVERT(DECIMAL(5, 2),
               (([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
       [ElapsedSeconds],
       (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
       DATEADD(SECOND,
               (([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
               GETDATE()) AS [EstimatedCompletionTime]
FROM   comp;

SQL 2019 what new

Microsoft SQL Server 2019 Technical white paper September 2018

SQL Server 2019 Is Here: A Roundup of Its Best Features


What's new in SQL Server 2019 

SQL Server 2019 Learning Series
SQL WorkShop

Database Reliability Engineering

Database Reliability Engineering
Come to my reading list when this share by my manager in internal Microsoft Team Chat.


Resource
Book : http://shop.oreilly.com/product/0636920039761.do
Book : https://www.amazon.com/Database-Reliability-Engineering-Designing-Operating/dp/1491925949
PPT  - Summary: https://conferences.oreilly.com/velocity/vl-ca-2017/public/schedule/detail/61495
YouTube - Surge 2015 - Laine Campbell - Database Reliability Engineering, Modernizing the DBA Role

SQL CDC fail after servicepack upgrade

Issue

·         SQL CDC job fail

·         Database log space usage can’t clear

Troubleshooting

·         SQL CDC job fail with error message

o   Msg 8144, Level 16, State 2, Procedure or function sp_batchinsert_1582628681 has too many arguments specified. For more information, query the sys.dm_cdc_errors dynamic management view

o   Msg 8144, Level 16, State 2, Procedure or function sp_batchinsert_690101499 has too many arguments specified. For more information, query the sys.dm_cdc_errors dynamic management view

·         Error message in sqlerror log

o   Could not open database [xxxxxx]. Replication settings and system objects could not be upgraded. If the database is used for replication, run sp_vupgrade_replication in the [master] database when the database is available.

·         Error in sys.dm_cdc_errors

session_id	phase_number	entry_time	error_number	error_severity	error_state	error_message
1	7	9/11/18 2:20	8144	16	2	Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
1	7	9/11/18 2:20	22863	16	1	Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
2	7	9/11/18 2:22	8144	16	2	Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
2	7	9/11/18 2:22	22863	16	1	Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
3	7	9/11/18 2:24	8144	16	2	Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
3	7	9/11/18 2:24	22863	16	1	Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
4	7	9/11/18 2:26	8144	16	2	Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
4	7	9/11/18 2:26	22863	16	1	Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
5	7	9/11/18 2:28	8144	16	2	Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
5	7	9/11/18 2:28	22863	16	1	Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
6	7	9/11/18 2:30	8144	16	2	Procedure or function sp_batchinsert_1389248004 has too many arguments specified.
6	7	9/11/18 2:30	22863	16	1	Failed to insert rows into Change Data Capture change tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.

·         log_reuse_wait_desc for the database = REPLICATION

·         Log space usage for xxxxxx = 80% of 320 gb

·         Run Log backup for xxxxxx and log usage can’t be clear

Finding

·         If a CDC enabled database is part of an Always On availability group and users follow the general recommendations of upgrading the secondary replica first, sp_vupgrade_replication will not run in such databases during upgrade because secondary replica databases are not in read/write mode. This is a known behavior and is by design.

Workaround

·         Run sp_vupgrade_replication in master db and issue solve

Reference

·         CDC functionality may break after upgrading to the latest CU for SQL Server 2012, 2014 and 2016