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

Read SQL deadlock information from Extended Events Log

Analysis Deadlock from Extended Event Log is difficult.
It will give result in XML Code


Do google and found out script to convert the XML code to table format so we can easy analysis the deadlock information


SQLServerCentral.com - Reading Deadlock Trace
http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx


we paste the xml format get from the extended event log;
1) SSMS- >Servername->Management->Extended Events-> Session-> system_health->Package0.event_file
2) Right click -> View Target data
3) filter with xml_deadlock_report
4) in the row return with xml_deadlock_report, click on detail window -> values
    xml data will be appear in another window
5) copy the deadlock graph xml data and add at the start of xml file and to the end of the xml data
6) pass the xml into "put your deadlock graph here"
http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx
declare @deadlock xml set @deadlock = 'put your deadlock graph here'
select [PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'), [DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', '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)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end, [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'), [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'), [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'), [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'), [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'), [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'), [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'), [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
 from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
      LEFT JOIN @deadlock.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)')

SQL Reporting database (SSRS) moving/migration- note

Back Up and Restore Reporting Services Encryption Keys An important part of report server configuration is creating a backup copy of the symmetric key used for encrypting sensitive information. A backup copy of the key is required for many routine operations, and enables you to reuse an existing report server database in a new installation.

refer to the above link for how to back up and restore reporting services encryption keys.

Moving the Report Server Databases

Both the reportserver and reportservertempdb databases must be moved or copied together

• Schedules will be recreated the first time that you restart the Report Server service.
• SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.
• Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.
• Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

 refer to the above link for more information

Create the RSExecRole

Information from the article
Reporting Services uses a predefined database role called RSExecRole to grant report server permissions to the report server database. The RSExecRole role is created automatically with the report server database. As a rule, you should never modify it or assign other users to the role. However, when you move a report server database to a new or different SQL Server Database Engine, must re-create the role in the Master and MSDB system databases.

The article has instruction how to re-create the role in the Master and MSDB system databases using SSMS gui

The below scripts copy from the above article for my reference

use [master]
GRANT EXECUTE ON [sys].[xp_sqlagent_enum_jobs] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_is_starting] TO [RSExecRole]
GRANT EXECUTE ON [sys].[xp_sqlagent_notify] TO [RSExecRole]
use [msdb]
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_verify_job_identifiers] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_add_job] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_help_category] TO [RSExecRole]
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [RSExecRole]
GRANT SELECT ON [dbo].[syscategories] TO [RSExecRole]
GRANT SELECT ON [dbo].[sysjobs] TO [RSExecRole]  

Inline and Multi-Statement Table-Valued Functions

Reference for Inline and Multi-Statement Table-valued Functions

http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/

Sunday T-SQL Tip: Inline vs. Multi-statement Table Valued Functions

SQL 2016 CTP3 testing

Time to testing SQL 2016 new features
1. Download SQL 2016 CTP3 3.2 with evaluation version
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

2. Setup Hyper-V
http://soonyu-sql.blogspot.my/2012/12/70-462-virtual-server-preparation.html

3. Install SQL 2016 in Virtual Server

4. download SQL 2016 testing database and database
https://msftdbprodsamples.codeplex.com/releases/view/618193

5. Restore AdventureWorks2016CTP3 database
    Download file (AdventureWorks2016CTP3.bak & AdventureWorksDW2016CTP3.bak) from
     step 4

6. Going to test the below features with the demo script from step 4
    Always Encrypted           
    Clustered Columnstore Index
    Data Masking               
    In-Memory OLTP             
    JSON                  
    PolyBase                  
    Row-Level Security        
    Query Store               
    Stretch DB             
    Temporal Tables     

Error: 17049, Severity: 16, State: 1. DBCC ErrorLog fail

DBCC ERRORLOG

Error: 17049, Severity: 16, State: 1.
Unable to cycle error log file from 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG' to 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.1' due to OS error '32(The process cannot access the file because it is being used by another process.)'. A process outside of SQL Server may be preventing SQL Server from reading the files. As a result, errorlog entries may be lost and it may not be possible to view some SQL Server errorlogs. Make sure no other processes have locked the file with write-only access."

SELECT *
 FROM sys.dm_exec_requests a
 OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b
 WHERE session_id > 50
 and session_id <> @@spid AND( text = 'xp_readerrorlog'  OR  text = 'sp_cycle_errorlog')
 ORDER BY start_time


kill the spid


rerun DBCC ERRORLOG and it successful


reference : Unable to cycle error log due to lock that will not clear

SQLAgent - TSQL JobStep (Job % to SQL Job Name

SQLAgent - TSQL JobStep (Job % to SQL Job Name



select sp.program_name
from
sys.dm_exec_requests req
left join sys.sysprocesses sp on sp.spid=req.session_id
where (req.session_id>50 or req.session_id is null) and req.session_Id NOT IN (@@SPID)
order by logical_reads




go


program_name
SQLAgent - TSQL JobStep (Job 0x8BF803BD5BFFD3448392075F1E82E037 : Step 1)


what is the job name that execute?


SELECT CASE
   WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
   ELSE RTRIM([program_name])
  END ProgramName
  , Val1.UqID
  , Val1.UqIDStr
FROM sys.sysProcesses P
  CROSS APPLY
   (
   VALUES (
     CASE
      WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
       CAST(
        SUBSTRING(RTRIM([program_name]), 32, 8) + '-' +
        SUBSTRING(RTRIM([program_name]), 40, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 44, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 48, 4) + '-' +
        SUBSTRING(RTRIM([program_name]), 52, 12)
       AS uniqueidentifier)
      ELSE NULL
     END ,
     CASE
      WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN
       CAST(CONVERT(VARBINARY(MAX),'0x' + SUBSTRING(RTRIM([program_name]), 32, 32),1) AS uniqueidentifier)
      ELSE NULL
     END
     )
   ) Val1(UqID, UqIDStr)
  OUTER APPLY
   (
   SELECT DISTINCT Name
   FROM MSDB.dbo.SysJobs
   WHERE CAST(Job_ID AS varchar(50)) = Val1.UqIDStr
     OR Job_ID =  Val1.UqID
     OR CAST(Job_ID AS varchar(50)) = Val1.UqID
   ) J
WHERE RTRIM([program_name]) ='SQLAgent - TSQL JobStep (Job 0x8BF803BD5BFFD3448392075F1E82E037 : Step 1)'  




Extract from
http://www.sqlservercentral.com/Forums/Topic1648616-392-1.aspx

Sharepoint in SQL AGL intermittent timeout

Quote:
Intermittent, unusually high latency might occur when you use availability groups that have replicas that are deployed on multiple subnets.
As a best practice, connections to SharePoint availability groups in a multi-subnet environment should configure specifyMultiSubnetFailover=True to avoid issues caused by high network latency. For more information, see Supporting Availability Group Multi-Subnet Failovers.



Below should run in Sharepoint application server each time create / recreate sharepoint site to make sure multisubnetfailover keyword in add into sharepoint connection string

$dbs = Get-SPDatabase | ?{$_.MultiSubnetFailover -ne $true}
     foreach ($db in $dbs)
     {
          $db.MultiSubnetFailover = $true
           $db.Update()
     }

Reference :
Configure SQL Server 2012 AlwaysOn Availability Groups for SharePoint 2013