Temporary

 SELECT

    e.execution_id,

    ex.package_name,

    es.executable_id,

    ex.executable_name,

    es.execution_path,

    es.execution_result,

    es.start_time,

    es.end_time,

    DATEDIFF(SECOND, es.start_time, es.end_time) AS duration_seconds,

CASE

WHEN executable_name LIKE '%IS_LOAN_DELINQUENT%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_AAS'

WHEN executable_name LIKE '%IS_CC_DELINQUENT%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%IS_ACTIVE_OFFER%' THEN 'Archown.SAMIR_OFFER'

WHEN executable_name LIKE '%RIB_ENABLED%' THEN 'Archown.RIB11_CUSR, Archown.Fincl10_Accounts'

WHEN executable_name LIKE '%ACTIVE%' AND executable_name LIKE '%OFFER%' THEN 'Archown.SAMIR_OFFER'

WHEN executable_name like '%Historical cc%' THEN 'Archown.PRIME17_APPLOG, Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%CIF%' then 'ARCHOWN.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%CIB_ENABLED%' THEN 'Archown.CIB11_CUSR, Archown.Fincl10_Accounts'

WHEN executable_name LIKE '%IS_DO%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%SAVINGS%' THEN 'ARCHOWN.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%DUPLI%' THEN 'Archown.SAMIR_OFFER'

WHEN executable_name LIKE '%INFLOW%' THEN 'Archown.Fincl10_GAM, Archown.SAMIR_OFFER, Archown.Fincl10_ATO'

WHEN executable_name LIKE '%ACTIVE%' AND executable_name LIKE '%LOAN%' THEN 'Archown.Fincl10_GAM, Archown'

WHEN executable_name like '%Historical loan%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_LDT, Archown.Fincl10_LDTH'

WHEN executable_name like '%woff%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%BLBD%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%trading%' THEN 'Archown.Fincl10_GAM, Archown.SAMIR_OFFER, Archown.Fincl10_CMG'

WHEN executable_name LIKE '%EXISTING%' AND executable_name LIKE '%LOAN%' THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%WATCH%' THEN 'Archown.Fincl10_ACCOUNTS, Archown.Fincl10_RCT'

WHEN executable_name LIKE '%AML%' then 'Archown.C360_CustomerAML'

WHEN executable_name LIKE '%Defaulted%' THEN 'Archown.Fincl10_GAM, Archown.Fincl10_GAC, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERTING  UPDATING  HAS DIGITAL LOAN' THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERTING PAYPLUS LOAN' THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERTNG  MICRO LOAN FLAG' THEN 'Archown.Fincl10_GAM, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERT INTO WRITE OFF' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC'

WHEN executable_name LIKE '%IS_D015%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE '%ACTIVE PAYADVANCE%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name LIKE 'INSERTING STG EIM OBLIGATIONS' THEN 'Archown.Fincl10_LRS'

WHEN executable_name LIKE 'INSERTING ST MONTHLY INT AMT' THEN 'Archown.FINCL10_LDT'

WHEN executable_name LIKE 'INSERTING ST PRDEM' THEN 'Archown.FINCL10_LDT'

WHEN executable_name LIKE '%INSERTING SOLE TRADER FLAG%' then 'Archown.C360_GOLDENCUSTOMER'

WHEN executable_name like '%HAS_HISTORICAL_LOAN%' then 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_LDT, Archown.Fincl10_LDTH'

WHEN executable_name like '%HIGH%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_ATO'

WHEN executable_name like '%HISTORICAL CREDIT CARD%' THEN 'Archown.PRIME17_APPLOG, Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%DECLINE%' THEN 'ARCHOWN.BPM_NCB_AP_CARDS_DETAILS_DECISION, ARCHOWN.BPM_NCB_CARDS_ROUTE_TABLE, ARCHOWN.LENDX_CL_CARDS_EXTTABLE, ARCHOWN.LENDX_LD_CREDITCARD_DECISION'

WHEN executable_name like '%CREDIT CARD DEL%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%LOAN DELINQUENT%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_AAS'

WHEN executable_name like '%CLASSIFIED%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC'

WHEN executable_name like '%existing cc%' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERT INTO ACCT_CLS_FLG' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_CMG'

WHEN executable_name LIKE '%BOB ENABLED%' THEN 'Archown.CIB11_CUSR, Archown.Fincl10_Accounts'

WHEN executable_name = 'INSERTING UNSECURED RISK SCORE VALUE' THEN 'Archown_Custom_Grmd.GRMD_FINAL_PRE_UN_SCORES'

WHEN executable_name = 'INSERTING CREDIT CARD RISK SCORE VALUE' THEN 'Archown_Custom_Grmd.GRMD_FINAL_PRE_CC_SCORES'

WHEN executable_name = 'INSERT INTO RWR TMP RTO,GAM' THEN 'ARCHOWN.Fincl10_GAM'

WHEN executable_name like '%UNEMPLOYED%' THEN 'Archown.C360_GOLDENCUSTOMER, ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE, ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST, ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST, ARCHOWN.C360_GOLDENCUSTOMERACCOUNT, ARCHOWN.C360_GOLDENCUSTOMER'

WHEN executable_name like '%INSERTING IS_MOBILE_APP%' THEN 'Archown.C360_GOLDENCUSTOMER, Archown.C360_CUSTOMERACTIVE, Archown.C360_GOLDENCUSTOMERKYC, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERTING_ IS_MISSED PAYMENT OCMP INSTALOAN' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_LDT, Archown.Fincl10_LDTH'

WHEN executable_name = 'INSERT INTO HAS STAFF ACCOUNT' THEN 'Archown.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name like '%LOAN DELIQUENT%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_GAC, Archown.Fincl10_AAS'

WHEN executable_name LIKE '%OWNER TYPE%' THEN 'ARCHOWN.Fincl10_GAM, Archown.Fincl10_AAS'

WHEN executable_name = 'INSERTING STG CUSTOMR BALANCES 1' THEN 'ARCHOWN.C360_GOLDENCUSTOMERACCOUNT'

WHEN executable_name = 'INSERTING CUSTOMER DISP AMOUNT,OUTSTANDING BALANCE AND COUNTS' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.FINCL10_LAM, Archown.Fincl10_RTH'

WHEN executable_name = 'INSERTING  UNSECURED LOAN FLAG' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERTING  EXISTING FASTCASH' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERTING  UPDATING  HAS DIGITAL LOAN 1' THEN 'ARCHOWN.FINCL10_GAM'

WHEN executable_name = 'INSERTING  UPDATING  HAS DIGITAL LOAN' THEN 'ARCHOWN.FINCL10_GAM'

WHEN executable_name = 'INSERTING ACTIVE CCBIZPREAPPROVED OCMP FLAG' THEN 'ARCHOWN.SAMIR_OFFER'

WHEN executable_name = 'INSERTTING  BLOCK LISTED ACCOUNTS' THEN 'ARCHOWN.FINCL10_GAM, ARCHOWN.FINCL10_CMG'

WHEN executable_name = 'INSERTING CC PREQUAL CREDIT CARD EXISTING FLAG OCMP' THEN 'Archown.PRIME17_CACCOUNTS, Archown.C360_GOLDENCUSTOMERSYSTEMMAPPING'

WHEN executable_name = 'INSERTING INTO STG OBLIGATIONS' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.FINCL10_LAM, Archown.Fincl10_GAC'

WHEN executable_name = 'INSERT INTO RWR TEMP OFFERS' THEN 'Archown.SAMIR_OFFER'

WHEN executable_name like '%internet%' then 'Archown.Fincl10_Accounts, Archown.RIB11_CUSR'

when executable_name = 'INSERTING  PAYADVANCE OFFER FLAG' then 'ARCHOWN.SAMIR_OFFER'

WHEN executable_name = 'INSERTING CREDIT CARD EXISTING FLAG' THEN 'ARCHOWN.C360_GOLDENCUSTOMER, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.PRIME17_PEOPLE, ARCHOWN.PRIME17_CARDX'

WHEN executable_name = 'INSERTINGIS_INSTALOAN_FLAG' THEN 'Archown.C360_CUSTOMERAML'

WHEN executable_name = 'INSERTING_CREDIT_LIMIT_CC' then 'ARCHOWN_CUSTOM_GRMD.GRMD_FINAL_PRE_CC_SCORES'

WHEN executable_name = 'INSERT INTO EMPLOYMENT_TENURE' THEN 'ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE, [ARCHOWN].[C360_GOLDENCUSTOMER], ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST, [ARCHOWN].C360_GOLDENCUSTOMERACCOUNT, ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST'

when executable_name = 'INSERT INTO IS_PRE_APPROVAL_PRE_QUALIFICATION' then 'ARCHOWN_CUSTOM_GRMD.GRMD_FINAL_PRE_CC_SCORES'

when executable_name = 'INSERTING CREDIT_LIMIT_INSTALOAN' THEN '[ARCHOWN_CUSTOM_GRMD].[GRMD_FINAL_PRE_UN_SCORES]'

WHEN executable_name = 'INSERT INTO CLD CLFD' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.Fincl10_GAC, ARCHOWN.Fincl10_LTDH'

WHEN executable_name = 'INSERT INTO REPAY COMP' THEN 'ARCHOWN.Fincl10_GAM, ARCHOWN.C360_GOLDENCUSTOMERSYSTEMMAPPING, ARCHOWN.Fincl10_GAC, ARCHOWN.Fincl10_LTDH'

WHEN executable_name = 'INSERT EMPLOYMENT_CHECK_FASTCASH' THEN 'ARCHOWN.BPM_NCB_CONSUMERLOAN_EXTTABLE, [ARCHOWN].[C360_GOLDENCUSTOMERSYSTEMMAPPING], ARCHOWN.CIB11_SALARY_PAYMENT_REQUEST, [ARCHOWN].C360_GOLDENCUSTOMERACCOUNT, ARCHOWN.CIB11_SALARY_TRANSACTION_REQUEST'

ELSE 'TBD' END Tables_Involved

FROM

    catalog.executions AS e WITH (NOLOCK)

JOIN

    catalog.executable_statistics AS es WITH (NOLOCK) ON e.execution_id = es.execution_id

JOIN

    catalog.executables AS ex WITH (NOLOCK) ON es.execution_id = ex.execution_id AND es.executable_id = ex.executable_id

WHERE

    e.package_name = 'ETL_UNIFIED_CUSTOMER_BASE.dtsx' and

e.status = 7 and

executable_name LIKE 'INSERT%'

ORDER BY

    es.start_time desc

Monitoring SQL Server Deadlocks using Extended Events

Extended Events were first introduced in SQL Server 2008, and therefore you will not face any problem implementing them in our environment.

Extended Events is a lightweight performance monitoring feature that enables users to collect data to monitor and troubleshoot problems.

A deadlock is a situation where two or more processes or threads are blocked indefinitely, waiting for each other to release resources that they need to proceed.

Here we are going to observe how to gather deadlock information through Extended Events :

Creating SQL Server Extended Events to Capture Deadlocks using Management Studio

Step 1: First, open SQL Server Management Studio (SSMS) and navigate to Management > Extended Events > Sessions.

 

Step 2: Right-click on Sessions and select New Session.

Step 3: In the new window, provide a name for your event, we will call it SQLWATCH_deadlock in this case. Select the event to start at the beginning of the server and when the session is created, you can change these settings if necessary.

Step 4:  Go to the Events tab and in the event library textbox type “deadlock” to filter deadlock events:

Step 5: We will select 2 events: Lock_Deadlock (Raised when a request to acquire a lock is cancelled for the victim of a deadlock) and Lock_deadlock_chain (Raised when a request to acquire a lock results in a deadlock. This event is raised for all members of the deadlock).

Step 6: Having both the events chosen, click on the configure button, and a new window will be opened, in this window we will take a snapshot of the sql_text field so that we can view the query that generated the deadlock

Step 7: In the Data Storage tab, select where you want to store the Extended Event data use a file.

Step 8: After that, click on OK to save the Event configuration, we can see that the event is created and is already running.

The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read at offset 0x0000 in file.

Error Message

 Msg 823, Level 24, State 2, Procedure tables, Line 2 [Batch Start Line 2]

The operating system returned error 59(An unexpected network error occurred.) to SQL Server during a read 

at offset 0x00004675548000 in file '\\Server_Name\D2A_DB\MSSQL\Data\HISTORICAL_NDF_1.ndf'.

Additional messages in the SQL Server error log and operating system error log may provide more detail. 

This is a severe system-level error condition that threatens database integrity and must be corrected 

immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many

factors; for more information, see SQL Server Books Online.

Cause

The Windows Session Timeout is 60 s by default. This timeout is dependent not only on Data Domain response time, 

but also any network latency. 

If there is a slow network or longer than expected Data Domain processing (such as rapid index expansion under 

a heavy backup load), Windows resets the connection causing backup errors.

PS C:\Windows\system32> get-SmbClientConfiguration

Resolution

Increase the Windows Session Timeout to a higher value such as 180 s by running the following command:

PS C:\Windows\system32> Set-SmbClientConfiguration -SessionTimeout 180 -Force