Wednesday, August 12, 2020

Get index progress in SQL 2014 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;

Thursday, September 13, 2018

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

Thursday, July 05, 2018

Cluster log


Sample Error :

1. cluster-node-keeps-loosing-communication-to-file-share-disk-witness
Failed to arbitrate for \\FileServer\ShareFolder\16d280e9-633c-4db3-a7f5-2db3b4fae331 with 64.

net helpmsg 64
The specified network name is no longer available.

Extract from fourm :
This is a typical heartbeat communication failed between some nodes. Mostly the network connection or communication is failed among the cluster nodes. 
Event 1135 will be logged on all nodes in the Cluster except for the node that was removed. The reason for this event is because one of the nodes in the Cluster marked that node as down. It then notifies all of the other nodes of the event. When the nodes are notified, they discontinue and tear down their heartbeat connections to the downed node.

 having-a-problem-with-nodes-being-removed-from-active-failover-cluster-membership

Sunday, May 21, 2017

Troubleshoot Hyper V issue - Cannot Connect to Virtual Machine

This is learning note.
Copy from
Client Hyper-V "Cannot Connect to Virtual Machine
Solution from Taylor Brown


If not we should enable some additional logging if possible to see what is going on.
  1. Shutdown any running VM’s
  2. Close all of the Hyper-V UI
  3. Stop the VMMS service (net stop vmms)
  4. Enable Analytic logging for the VMMS and Worker Process (see below)
  5. Enable UI Tracing for Hyper-V Manager and VMConnect (see below)
  6. Start the VMMS service (net start vmms)
  7. Open Hyper-V Manger
  8. Restart the VM
  9. Attempt to connect (try two or three times)
  10. Gather Logs (see below)
  11. Disable Tracing (see below)
Enabling Analytic Logging For the VMMS and Worker Process
  1. Open the Event Viewer
  2. From the menu bar select View -> Show Analytic and Debug Logs
  3. Navigate to the Hyper-V logs and Enable the Analytic Channels
Applications and Services Logs
                Microsoft
Windows
Hyper-V-VMMS
                                                                Analytic
                                                                                Right Click -> Enable (yes if a pop up asks about enabling the logs)
Hyper-V-Worker
                                                                Analytic
                                                                                Right Click -> Enable (yes if a pop up asks about enabling the logs)
Enabling UI Tracing
  1. Create a new text file named “VMClientTrace.config” in the "%appdata%\Microsoft\Windows\Hyper-V\Client\1.0\” folder
  1. Copy the following XML text into that file:

Save the file.
Gathering The Logs
  1. The Analytic logs will be in the analytic folder (you often have to refresh or select another source then analytic again to see them)
  2. The UI trace Logs will be in %temp% (sometime back one directory i.e. %temp% = “C:\Users\taylorb\AppData\Local\Temp\2” for me but the logs are at “C:\Users\taylorb\AppData\Local\Temp”
Disabling Tracing
  1. To disable Analytic Tracing just right click on the analytic sources and select disable (same as enabling)
  2. To disable UI tracing just delete the “%appdata%\Microsoft\Windows\Hyper-V\Client\1.0\VMClientTrace.config” file.





Friday, May 12, 2017

Setup Ubuntu server in Hyper-V - Prepare for SQL 2017 linux

The below can only apply to testing environment :

My computer connect to network using wifi


1. download Ubuntu Server
https://www.ubuntu.com/download/server
I download this version
Ubuntu Server 16.04.2 LTS

2. From Hyper-V - > New -> Virtual Machine
    Specific Name and location of Ubuntu Virtual Machine we want setup; "Ubuntu"
    Specific generation - > Generation 1
    Assign memory -> 4000 (3.25 gb memory requirement for SQL 2017 for Linux; reference)
           using dynamic memory for this virtual server
   connection - no connect
   create a virtual hard disk -> Name - > ubuntu.vhdx; location -> harddisk location; size - 127GB
   Installation -> Install an operating system later;

3. Pre -install Ubuntu
    In Hyper-V manager- >;Select Virtual machine I created just now (Ubuntu)
    Right side -> Setting - >; It will show Setting for Ubuntu
    From IDE Controller 1; DVD drive -> Media -> select image file ->browse to the ubuntu Disk image file (ubuntu-16.04.2-server-amd64) I download at step 1

  4. Install Ubuntu Server
      From Hyper-V manager- > Select Virtual machine (Ubuntu)
      Right click and connect and press start the machine and it will go through the installation process
      I select install Ubuntu Server
      Follow all step  in UI. Below is some note
      *  Do not configure network at this time
      *  Partitioning method ; Guided - use entire disk and setup LVM
      * Choose software to install ->; I didn't change anything
      * Install the GRUB Boot loader to the master boot record

5. Setup network in Virtual machine
Hyper-V Virtual Server connect to internet using WIFI

6. After step 5, restart Virtual server and we found the network will disappear again

7. Setup fix IP
Virtual Server - Setup Fix IP

7a  List of available update
   sudo apt-get update

8. Installs newer versions of the packages
     sudo apt-get upgrade

9. performing the function of upgrade, also intelligently handles changing dependencies with new versions of packages;
     sudo apt-get dist-upgrade


Virtual Server - Setup Fix IP

Enable Wifi network in Virtual Server.

sudo lshw -class network  -> it show network disabled
ls /sys/class/net   -> show ubuntu internet interfce name; example :   enpp1203

enabled network interface enpp1203
sudo dhclient enpp1203

verify:
sudo lshw -class network
We can see the "disabled" word no longer there

More information
Hyper-V Virtual Server connect to internet using WIFI 


When I restart Virtual Server, network connection in Virtual Server lost ?
How to setup fix IP in Virtual Server?
1) sudo vim /etc/network/interfaces

2) add the below information inside interfaces
     auto enp0s10f0
     iface enp0s10f0 inet static
     address 192.168.0.160
     netmask 255.255.255.0
     broadcast 192.168.0.255
     gateway 192.168.0.1
     wpa-ssid ssidname
     wpa-psk passwordforwifi

3) restart network;
    sudo /etc/init.d/networking restart
    [ ok ] Restarting networking (via systemctl): networking.service.

4) try list available update, and it give all error, can't connect to internet
     sudo apt-get update
   Err:1 http://security.ubuntu.com/ubuntu xenial-security InRelease
  Temporary failure resolving 'security.ubuntu.com'
Err:2 http://us.archive.ubuntu.com/ubuntu xenial InRelease
  Temporary failure resolving 'us.archive.ubuntu.com'
Err:3 http://us.archive.ubuntu.com/ubuntu xenial-updates InRelease
  Temporary failure resolving 'us.archive.ubuntu.com'
Err:4 https://packages.microsoft.com/ubuntu/16.04/prod xenial InRelease
  Could not resolve host: packages.microsoft.com
 5) add dns server name into /etc/network/interfaces
     sudo vim /etc/network/interfaces

auto enp0s10f0
iface enp0s10f0 inet static
   address 192.168.0.160
   netmask 255.255.255.0
   broadcast 192.168.0.255
   gateway 192.168.0.1
   dns-nameservers 8.8.8.8
   dns-nameservers 8.8.4.4

   wpa-ssid ssidname
    wpa-psk passwordforwifi
6) restart network after add dns name servers
    sudo /etc/init.d/networking restart
    [ ok ] Restarting networking (via systemctl): networking.service.

7) try list available update, and it and now ok
     sudo apt-get update
    Hit:1 https://packages.microsoft.com/ubuntu/16.04/prod xenial InRelease
Hit:2 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial InRelease
Hit:3 http://us.archive.ubuntu.com/ubuntu xenial InRelease
Get:4 http://security.ubuntu.com/ubuntu xenial-security InRelease [102 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu xenial-updates InRelease [102 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu xenial-backports InRelease [102 kB]
Fetched 306 kB in 2s (142 kB/s)
Reading package lists... Done