Error 0xC0014062: Unable to complete login process due to delay in opening server connection

Sometimes you may get an error log when your backup maintenance plan job gets fails with the following error on a server with many databases or connections:

Message:
Executed as user: xyz\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  01:00:05 AM  Could not load package "Maintenance Plans\xyz" because of error 0xC0014062.  
Description: The LoadFromSQLServer method has encountered OLE DB error code 0-80004005 (Unable to complete login process due to delay in opening server connection).  The SQL statement that was issued has failed.  
Source:   Started:  01:00:05 AM  Finished: 01:01:02 AM  Elapsed:  57.175 seconds.  
The package could not be loaded.  The step failed.

This indicates that the maintenance plan which uses SSIS (integrated services) and msdb databases is delay in connection. To resolve this issue general recommendation could be

Change the host file
Which is generally located at “C:\WINDOWS\system32\drivers\etc\host” add server name and an IP address.

Change the number of retry for the specific job to greater number (eg. 5 or more) and also can change the retry interval to (0). This way if this job fails with delay in connection, will retry again till specified number of times and mostly it gets succeeded.

T-Log space used by which transaction

Sometime T-Log space is increasing very fast and we are not aware that which transaction is culprit. You can use below DMV to check which transaction is culprit.

SELECT ST.Session_ID AS [Session ID], DT.Transaction_ID AS [Transaction ID], DB_NAME(DT.Database_ID) AS [DataBase Name], 
CASE Database_Transaction_Type WHEN 1 THEN 'Read / Write' WHEN 2 THEN 'Read Only' WHEN 3 THEN 'System' END AS [Transaction Type], 
CASE Database_Transaction_State WHEN 1 THEN 'Not Initialized' WHEN 3 THEN 'Transaction No Log' WHEN 4 THEN 'Transaction With Log' WHEN 5 THEN 'Transaction Prepared' WHEN 10 THEN 'Commited' WHEN 11 THEN 'Rolled Back' WHEN 12 THEN 'Commited And Log Generated' END AS [Transaction State], 
Database_Transaction_Begin_Time AS [Transaction Begin Time], SP.HostName AS [Host Name], SQLT.Text, SP.LogiName AS [Login Name], SP.LastWaitType AS [Last Wait Type], SP.Status, 
Database_Transaction_Log_Bytes_Reserved AS [Log Bytes Reserved], Database_Transaction_Log_Record_Count AS [Log Record Count], Database_Transaction_Log_Bytes_Used AS [Log Bytes Used], 
(Database_Transaction_Log_Bytes_Used + Database_Transaction_Log_Bytes_Reserved )/1024 AS [Total Log Space Used KB]
FROM SYS.DM_TRAN_Database_TransactionS AS DT JOIN SYS.DM_TRAN_SESSION_TransactionS AS ST ON DT.Transaction_ID=ST.Transaction_ID JOIN SYS.SYSPROCESSES AS SP ON SP.SPID = ST.SESSION_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT 
WHERE DT.Transaction_ID > 1000 AND ST.Session_ID > 50

Change Batch Separator

By default GO is the batch separator in SQL Server. GO is not a Transact SQL statement. It is a command recognized by SQL server as batch separator. You can change the batch separator (GO) in SQL server.

Follow below Steps to change the batch separator in SSMS:

• Start SSMS
• Go to Tools –> Options
• Click on the “Query Execution” node
• Notice that we have an option to change the Batch Separator
• Change the batch separator
• Click “OK”

List all Disabled Trigger

Use below query to get a list of all disabled trigger in a database:

SELECT db_name() AS [Database Name],
T.[Name] AS [TableName],
TR.[Name] AS [TriggerName],
[Status] = CASE WHEN OBJECTPROPERTY(TR.[ID], 'ExecIsTriggerDisabled') = 1
THEN 'Disabled' ELSE 'Enabled' END
FROM sysobjects T JOIN sysobjects TR ON T.[ID] = TR.[parent_obj]
WHERE T.[xtype] = 'U' AND TR.[xtype] = 'TR' AND OBJECTPROPERTY(TR.[ID], 'ExecIsTriggerDisabled') = 1 

Here you can use 0 to list Enabled triggers and 1 for Disabled triggers.