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

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

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:

<configuration> <microsoft .virtualization.client.traceconfigurationoptions=""> <setting name="TraceTagFormat" type="System.Int32"> <value>3</value> </setting> <setting name="BrowserTraceLevel" type="System.Int32"> <value>71</value> </setting> <setting name="VMConnectTraceLevel" type="System.Int32"> <value>71</value> </setting> </microsoft> </configuration>

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.

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

Reporting Service - SSRS subscription cant edit/ add and report not send out

One of my report not send out from subscription.
I click on the report manually and report can be come out without any issue.


I login into reporting service and found out  SSRS subscription cant edit/ add and report not send out.

It give the below message :

"Reporting sererver -subscription can't be created or edited because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid"


I fix two item in the report
1. link to sub-report which is not available
2. data source have invalid server name, and the data source is not been use in the report. I re-point to the correct data source

After I fixed the above 2 items, email subscription working again.

Read ALL SQL deadlock information from Extended Events Log

How to read single deadlock information from extend event log

Read SQL deadlock information from Extended Events Log

So if we want to read all deadlock information which log into extend event,

how I doing ?

1. Run the below query in ssms to retrieve deadlock information from extend event.

Remember to change the folder name based on sqlversion

 "FROM sys.fn_xe_file_target_read_file('D:\MSSQL11.MSSQLSERVER\MSSQL"

 2. Copy the result into notepad and save

3. Open the notepad from excel with delimited "|"

4. All deadlock information will show in excel worksheet

create table #tempdeadlock
(rownumber int,
 Code varchar(max),
 Code2 varchar(max),
 Code3 varchar(max),
 Code4 varchar(max),
  Code5 varchar(max),
   Code6 varchar(max),
   PagelockObject varchar(200),
   DeadlockObject varchar(200),
   [processid] varchar(200),
 [KeylockObject] varchar(200),
 [Index] varchar(200),
 [IndexLockMode] varchar(5),
 [Victim] char(1),
  VictimProcessID NVarChar(50),
 [Procedure]  varchar(200),
 [LockMode] char(1),
 [ClientApp]  varchar(100),
 [HostName]  varchar(20),
 [LoginName] varchar(20),
 [spid] varchar(10),
 [TransactionTime] datetime,
   [InputBuffer] varchar(1000))


declare @max int
declare @min int
declare @intFlag int=1
declare @textall varchar(max)
select @max=max(rownumber),@min=min(rownumber) from #temp2
set @intflag=@min
WHILE (@intFlag <=@max)
BEGIN
    PRINT @intFlag
    set @textall=
    '
    declare @text varchar(max)
    declare @xmltext xml
    select @text=''''+replace(cast(xml1 as varchar(max)),'''''''','''''''''''')+'''' from #temp2 where rownumber='+cast(@intflag as varchar(10))+'
     set @xmltext=cast(@text as xml)
    insert into #tempdeadlock
    select '+ cast(@intflag as varchar(10))+ ' as rownumber,
 [Code] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[1]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code2] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[2]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code3] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[3]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code4] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[4]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code5] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[5]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code6] = replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[6]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
  [PagelockObject] = @xmltext.value(''/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname'', ''varchar(200)''),
 [DeadlockObject] =@xmltext.value(''/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname'', ''varchar(200)''),
[processid]=Deadlock.Process.value(''@id'', ''varchar(200)''),
 [KeylockObject] = Keylock.Process.value(''@objectname'', ''varchar(200)''),
 [Index] = Keylock.Process.value(''@indexname'', ''varchar(200)''),
 [IndexLockMode] = Keylock.Process.value(''@mode'', ''varchar(5)''),
 [Victim] = case when Deadlock.Process.value(''@id'', ''varchar(50)'') = DeadlockList.Graphs.value(''(victim-list/victimProcess[1]/@id)[1]'', ''NVarChar(50)'')  then 1 else 0 end,
 DeadlockList.Graphs.value(''(victim-list/victimProcess[1]/@id)[1]'', ''NVarChar(50)'') AS VictimProcessID,
 [Procedure] = Deadlock.Process.value(''executionStack[1]/frame[1]/@procname[1]'', ''varchar(200)''),
 [LockMode] = Deadlock.Process.value(''@lockMode'', ''char(1)''),
 [ClientApp] = Deadlock.Process.value(''@clientapp'', ''varchar(100)''),
 [HostName] = Deadlock.Process.value(''@hostname'', ''varchar(20)''),
 [LoginName] = Deadlock.Process.value(''@loginname'', ''varchar(20)''),
 [spid] = Deadlock.Process.value(''@spid'', ''varchar(10)''),
 [TransactionTime] = Deadlock.Process.value(''@lasttranstarted'', ''datetime''),
  [InputBuffer] = replace(replace(replace(replace(Deadlock.Process.value(''inputbuf[1]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '')

 from @xmltext.nodes(''/deadlock-list/deadlock/process-list/process'') as Deadlock(Process)
      LEFT JOIN @xmltext.nodes(''/deadlock-list/deadlock/resource-list/keylock'') as Keylock(Process)
        ON Keylock.Process.value(''owner-list[1]/owner[1]/@id'', ''varchar(50)'') =
           Deadlock.Process.value(''@id'', ''varchar(50)'')
      CROSS JOIN  @xmltext.nodes(''/deadlock-list/deadlock'') AS DeadlockList(Graphs)
      '
  exec (@textall)
    SET @intFlag = @intFlag + 1
 END

select * from #tempdeadlock



select distinct rownumber,'|' as '|',
 REPLACE(REPLACE(REPLACE(Code, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code ,'|' as '|',
  REPLACE(REPLACE(REPLACE(Code2, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code2 ,'|' as '|',
   REPLACE(REPLACE(REPLACE(Code3, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code3 ,'|' as '|',
    REPLACE(REPLACE(REPLACE(Code4, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code4 ,'|' as '|',
     REPLACE(REPLACE(REPLACE(Code5, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code5 ,'|' as '|',
      REPLACE(REPLACE(REPLACE(Code6, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code6 ,'|' as '|',

 PagelockObject ,'|' as '|',   DeadlockObject ,'|' as '|',   [processid] ,'|' as '|', [KeylockObject] ,'|' as '|',
 [Index] ,'|' as '|', [IndexLockMode] ,'|' as '|', [Victim] ,'|' as '|',  VictimProcessID ,'|' as '|', [Procedure]  ,'|' as '|', [LockMode] ,'|' as '|',
 [ClientApp] ,'|' as '|', [HostName]  ,'|' as '|', [LoginName] ,'|' as '|',
 [spid] ,'|' as '|', [TransactionTime] ,'|' as '|',   [InputBuffer]  from
#tempdeadlock where 1=1

Hyper-V Virtual Server connect to internet using WIFI

Below from my own reference, and I direct copy direct from the below URL
 how-to-set-a-hyper-v-virtual-machine-to-use-my-wi-fi-connection  -answered Jul 9 '14 at 20:32 byTravis

Below for my reference :

1. Launch Hyper-V Manager from your App Menu
2. In the Actions area in right-hand navigation, click “Virtual Switch Manager”
3. When the Virtual Switch Manager window opens, select “New virtual network switch” on the left, select “Internal” on the right, and then click the “Create Virtual Switch” button
4. Give the new switch a name like “Virtual WLAN” and click “OK”
5. In Windows 8 sys tray, right-click on the wireless icon and click on “Open Network and Sharing Center.” You will see the new Unidentified Network connected to the vEthernet (Virtual WLAN) adapter.
6. Back in Hyper-V Manager, select your VM (make sure it is turned off) and on the lower left side, click “Settings”
7. The Settings window will default to Add New Hardware, select “Legacy Network Adapter” and click “Add”
8. In the Legacy Adapter details, select the “Virtual WLAN” adapter we configured earlier and click “OK”
9. Go back to Windows 8’s Network and Sharing Center and click on the “Wi-Fi” link (or the name of your laptop’s wireless adapter) listed in the Connections In the adapter status window, click “Properties”
10. In the Properties window, click the Sharing tab, check the “Allow other network users…” box, select “vEthernet (Virtual WLAN)” (or the name of your wireless adapter), and click “OK” to close the window
11. Click Close to exit the Wi-Fi status window
    
12. To confirm you have it setup correctly, click on the “Change adapter settings” link. You should see the word Shared beside your wireless connection.
    

more detail : Using Your Windows 8 Wireless Connection Inside Hyper-V 

 
After setup all the above,  windows server in hyper-v able connect using WIFI

 Ubuntu using HyperV;
I still no manage to get my wifi connect to internet

I do the below :
sudo  ifconfig -a    -> cant get IP
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

after do the above step, i can connect to internet from Ubuntu inside hyperv using my wifi