1. Check the connectivity between node from which you are installing SQL Server to other nodes. (\\Othernodes should work when you are installing SQL on box which is part of windows cluster regardless of SQL Cluster).

A buffer failed while allocating n bytes. The system reports n percent memory load. There are n bytes of physical memory with n bytes free.

There are 2147352576 bytes of virtual memory with 44814336 bytes free. The paging file has 687069143034 bytes with 35348451328 bytes free.

A buffer failed while allocating 63160 bytes. The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Read Results” (975) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

The system reports 2 percent memory load. There are 206147428352 bytes of physical memory with 24692375552 bytes free. There are 8796092891136 bytes of virtual memory with 8713333325824 bytes free. The paging file has 205966053376 bytes with 15835136 bytes free.

The system reports 24 percent memory load. There are 68716240896 bytes of physical memory with 51754737664 bytes free. There are 2147352576 bytes of virtual memory with 146911232 bytes free. The paging file has 137430540288 bytes with 120562003968 bytes free.

Below are options you can use to solve the above errors

1. Check if the Page file for the system is configured properly, you might get this error if there is no page file or if page file is very small

2. Change the lookup cache mode from Full to Partial or no cache mode.

3. Migrate to 64-Bit SSIS if you are in 32-BIT. Remember you have to migrate all the drivers and providers used in connections. If you are in 64 BIT system and getting above errors while executing packages from BIDS then check if you have set package to run in 64bit run time ( Run64bitruntime = True in Integration services project properties page).

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ’74eaaa74-4e7d-470a-96b4-29459cb4516e’) online (Error code 5018). The WSFC service may not be running or may not be accessible in its current state, or the WSFC resource may not be in a state that could accept the request. For information about this error code, see “System Error Codes” in the Windows Development documentation.

Failed to designate the local availability replica of availability group ‘KK_AG’ as the primary replica. The operation encountered SQL Server error 41066 and has been terminated. Check the preceding error and the SQL Server error log for more details about the error and corrective actions.

Failed to create availability group ‘KK_AG’. The operation encountered SQL Server error 41160 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41066)

You would also see errors similar to one below when you configure always on availability group using scripts.

Msg 41131, Level 16, State 0, Line 1

Failed to bring availability group ‘TDE_AG’ online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 1

Failed to create availability group ‘TDE_AG’. The operation encountered SQL Server error 41131 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Msg 41131, Level 16, State 0, Line 1

Failed to bring availability group ‘TDE_AG’ online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 1

Failed to create availability group ‘TDE_AG’. The operation encountered SQL Server error 41131 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Resolution:

Add start up account of cluster service to SQL Server login and grant sysadmin role (Start up account of cluster service will be nt authority\system by default).

2. Log on as an Administrator. SQL Server 2005/2008: Log on to the cluster node as a domain administrator that is also local administrator on all nodes, but not necessarily an account that is to be used as a service startup account for the SQL Server services.

Pre create user groups in active directory for different SQL Server 2005/2008 services(SQL,Agent,FT).SQL Server setup will add the service accounts of SQL Services to this groups during setup. You have to pre create unique group name for each Service in AD and enter it when prompted during SQL Server2005/2008 Cluster setup. SQL Server Setup grants the following Windows NT rights and privileges to the Group you enter during setup (If you can not run the setup as domain administrator then add the service logon accounts to the security groups created in AD before the setup). For SQL Server group Log on as a service (SeServiceLogonRight) Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000) Log on as a batch job (SeBatchLogonRight) Replace a process-level token (SeAssignPrimaryTokenPrivilege) Bypass traverse checking (SeChangeNotifyPrivilege) Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) Permission to start SQL Server Active Directory Helper Permission to start SQL Writer and many more For SQL Server Agent group Log on as a service (SeServiceLogonRight) Act as part of the operating system (SeTcbPrivilege) (only on Windows 2000) Log on as a batch job (SeBatchLogonRight) Replace a process-level token (SeAssignPrimaryTokenPrivilege) Bypass traverse checking (SeChangeNotifyPrivilege) Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

3. There should not be a pending Reboot requirement

Check pending Reboot requirement in following registry: HKLM\SYSTEM\CurrentControlSet\Control\Session Manager In the right-hand pane, is there a value of PendingFileRenameOperations? If so, is it empty? If empty close the registry editor. If not empty, reboot the machine prior to running setup. After logging in, re-check this registry value to make sure it is empty.

4. Verify Cluster “Heartbeat” For a Failover Clustered Instance installation on Windows 2003 or earlier, verify cluster networking was setup per: 258750 Recommended private “Heartbeat” configuration on a cluster server http://support.microsoft.com/?id=258750NOTE: This needs to be followed explicitly in regards to the binding orders and your heartbeat adapter’s configuration.

5. Verify the TCP\IP Advanced DNS settings are identical on the “Public” network adapters between all Failover cluster nodes

Use the “Shared Folders” MMC snap-in to verify the existing Administrative shares.

11. Only One RDP Session

Make sure no one else is logged in to any of the nodes. There should be only one logon session in active node which we use for setup. 12 . Make sure we are able to failover SQL Server groups to all nodes.

13. Cluster MSDTC Create MSDTC resource.

14. If windows2008+ Grant “Create Computer Objects” permission for the computer object created for the cluster (Computer Name object(CNO)).

SQL Server threads which are controlled by SOS (SQL Server operating system) are Non preemptive but at times they switch preemptive when they can’t obey the rules of SOS. Some common places when SOS thread is switched preemptive are when we call extended proc’s, few Windows API etc.

Let us assume you use “execute as user x” in your job, SQL Server calls Windows functions like LookupAccountName to get the credential of user. Windows functions interacts with AD services to get the credentials of account and return the info to the caller in SQL Server process and then SQL Server would build the logintoken. If there is a delay in AD and if it takes long time to respond to the windows function calls other threads in the same scheduler would get blocked so SQL Server thread would switch preemptive (Doesn’t follow SOS rules) before making these function calls. PREEMPTIVE_OS_AUTHORIZATIONOPS wait type would occur when a thread is waiting on such windows functions (security) to return, So first thing which would have to do is to fix the performance of AD calls.

To narrow down and prove that this issue occurs because of Active directory performance. Login to SQL server using the startup account of SQL Server and execute below query when you notice PREEMPTIVE_OS_AUTHORIZATIONOPS wait type and compare the times printed. It will give you the time it takes for SQL Server to complete the AD calls.