MSSQLWIKI

Karthick P.K on SQL Server

Archive for June, 2013

The connection to the primary replica is not active. The command cannot be processed

Posted by Karthick P.K on June 20, 2013

When you configure SQL Server always on available group from management studio it may fail with below error while joining secondary replica to the availability group.

 

Error 1

 

{

Joining database on secondary replica resulted in an error.  (Microsoft.SqlServer.Management.HadrTasks)

——————————

ADDITIONAL INFORMATION:

Failed to join the database ‘AG’ to the availability group ‘AG1’ on the availability replica ‘NODE2’. (Microsoft.SqlServer.Smo)

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

——————————

The connection to the primary replica is not active.  The command cannot be processed. (Microsoft SQL Server, Error: 35250)

}

 

Error 2

 

{

TITLE: Microsoft SQL Server Management Studio

——————————

Failed to join the instance ‘NODE2’ to the availability group ‘AG1’. (Microsoft.SqlServer.Management.SDK.TaskForms)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

——————————

ADDITIONAL INFORMATION:

Failed to join local availability replica to availability group ‘AG1’.  The operation encountered SQL Server error 41106 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 41158)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=41158&LinkId=20476

}

 

 

You may get below error when you configure AG availability group using  alter database command mentioned below or synchronization might fail with 35250 error mentioned below.

 

ALTER DATABASE [AG] SET HADR AVAILABILITY GROUP = [Group name];

 

Error 1

 

Msg 35250, Level 16, State 7, Line 1

The connection to the primary replica is not active.  The command cannot be processed.

 

 

To resolve  above errors

 

1. Ensure always on endpoint ([Hadr_endpoint]) are not blocked by firewall (Default port 5022).

 

2. Make sure startup account of primary server is added to all secondary server’s and Startup accounts of all secondary servers are added to primary servers.(Startup account of each replica to be added to other replica’s)

 

3. If log on account of SQL Server is “Nt service\” or local system account then ensure system account (Domainname\systemname$) of each replica is added to other replicas.

{

CREATE LOGIN [MSSQLWIKI\node2$] FROM WINDOWS

}

 

4. Grant connect on always on endpoints created on each replicas for startup account of other replica servers (Grant connect on endpoints even if startup account of other replicas are added as sysadmins).

{

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MSSQLWIKI\node1$]

}

 

5.  Make sure SQL Server name (select @@servername) matches with hostname.

6. Make sure cluster service startup account is part of SQL Server logins (More details in This link).

 

 

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

 

Posted in Always On, Configuration, Connectivity, Security, SQL General | Tagged: , , , , , , | 27 Comments »

False warning “A significant part of sql server process memory has been paged out”

Posted by Karthick P.K on June 13, 2013

In A significant part of SQL Server process memory has been paged out we discussed about SQL Server working set trim warning, when they can occur and how to troubleshoot them.

I the same blog I mentioned SQL Server will log “A significant part of sql server process memory has been paged out” warning when working set reaches 50% or below of the overall committed memory by SQL Server memory manager. In this blog I will try to cover when this warning could be a false warning and how to identify it.

Let us recollect what is committed memory and different states of committed bytes in windows.

Committed: Total memory that is allocated by process (allocated bytes can be in RAM or Page file)

Committed working set: Committed memory which is currently in RAM

Committed Paged : Committed memory which is currently page file

Committed Mapped : Committed mapped to page file.

Committed untouched: committed memory which is never accessed (When a page is committed in windows it will never become working set unless accessed).

Let us understand what Committed untouched is. Download Memoryallocator exe from This link and Keep committing memory using the same exe.

You will notice that the committed memory of the Memoryallocator process increases, but the physical memory usage (RAM usage or Working set) (or) Page file usage will not increase at all. Only the committed memory of the process and committed memory of overall system increases.

Why?

When a page is committed in windows it will not become part of working set or page file unless it is accessed.

Similarly when SQL Server estimates the memory requirements of different clerks and allocates them during startup or on need. These allocated memory is part of committed memory but will not have a page in RAM or Page file unless accessed for the first time.

So during this condition SQL Server’s working set can go far below the committed bytes and once working set reaches 50% or below of overall committed bytes then ““A significant part of sql server process memory has been paged out” warning is logged in SQL Server errorlog.

How do you identify if this warnings are false warnings?

We can identify if these warnings are false using the SQL Server memory dump or using the Perfmon counters.

Let us stimulate a false warning situation using the below backup query and see how to identify if the warning is false.

Run the below query in your test system.

Note: If you do not get the warning message increase the buffer count in below query. If you get “There is insufficient system memory in resource pool” then reduce the buffer count.

WARNING: dumptrigger  and below trace flag’s are undocumented and should be used only in test environments with caution  (or) under Microsoft Support supervision. There is no guarantee that they will work in future versions of SQL Server.

DBCC TRACEON(8026,-1) --Trace flag –T8026 tells dump trigger to remove the trigger after the first dump has been triggered.
go
DBCC DUMPTRIGGER('SET',17890)
go
BACKUP DATABASE MSDB TO DISK = N'msdb.BAK' WITH NOFORMAT, INIT,NAME = N'msdb', SKIP, NOREWIND, NOUNLOAD, STATS = 1 ,BUFFERCOUNT = 10000,BLOCKSIZE = 65536 ,MAXTRANSFERSIZE=2097152

Once you run the above backup command you will see   error: 17890 “A significant part of sql server process memory has been paged out” and a mini memory dump will be created in Errrorlog  folder along with SQLDump00nn.txt

Using memory dump

Open the SQLDump00nn.txt and review the memory section in SQLDump00nn.txt. This will give you the system memory information when the error occurred.

Snippet from my SQLDump00nn.txt.

{

Memory                              

MemoryLoad = 26%                    

Total Physical = 131067 MB          

Available Physical = 96691 MB       

Total Page File = 393201 MB         

Available Page File = 334217 MB     

Total Virtual = 8388607 MB          

Available Virtual = 8166328 MB      

**Dump thread – spid = 0, EC = 0x00000020F19C2B90                                                               

***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL11.RBS\MSSQL\LOG\SQLDump0048.txt    

}

In the above output  “Available Physical  is  96,691 MB” which indicates there is no physical memory pressure when SQL Server raised  17890 warning so widows is not trimming the working set and obviously we can come to conclusion that this instance of warning is false warning.

Note: Above method may not work well in earlier versions of windows in which working set of all processes are hard trimmed when there memory pressure in the system.

Using perfmon counters

In below perfmon graph I have collected three counters

1. Process\SQLServr\Working set (highlighted Black)

2. Process\SQLServr\Private bytes (Committed memory. Green line)

3. Memory\AvailableMbytes  (Red line)

I you review your SQL Server error log you would notice 17890 warning at the same time when Private bytes (Green line) spiked.

How to conclude that the warning is printed because of “untouched committed pages”  by SQL Server.

In general when a page is committed and accessed it will be part of working set as long as there is enough available memory on the system. If you look at below graph you will notice that the private bytes (committed) is increasing but the working set is not increasing at same phase though there is adequate available memory in the system. This can happen only when the pages are committed and not accessed (If you look at below graph carefully committed memory increased and dropped with in 10 seconds so when you configure Perfmon choose sample rate every 1 second else perfmon might miss the data and you will find some thing like this happened). 

clip_image002

 

Related posts

Troubleshooting steps for all SQL Server Memory errors

Trouble shooting working set trim “A significant part of SQL Server process memory has been paged out”

SQL Server lock pages in memory should I use it?

SQL Server memory leak

What is new in SQL Server 2012 Memory

How to set max server memory and min server memory

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

 

Posted in Memory, SQL Server Engine, SQL Server memory | Tagged: , , , , | 5 Comments »

What does MemoryUtilization in sys.dm_os_ring_buffers and Memory_utilization_percentage in sys.dm_os_process_memory represents?

Posted by Karthick P.K on June 2, 2013

Few days back someone asked me an interesting question.  Why memory_utilization_percentage (working set ) is 100 % when Virtual_address_space_committed_Kb ( committed ) is around 10 GB and Physical_memory_in_use_kb is just 1.7 GB (refer below image)?

clip_image002

Physical_memory_in_use_kb is Memory allocated by the SQL Server process which is currently in RAM. (This includes AWE and Large pages allocation).

Virtual_address_space_committed_Kb is total memory that is allocated by process (allocated bytes can be in RAM, page file, mapped or in not used state)

Memory_utilization_percentage is ratio between Physical_memory_in_use_kb and Memory allocated by SQL Server using SQL Server memory manager(derived from dm_os_memory_nodes). If the Memory_utilization_percentage  is greater than 100% then it is  capped to 100% . Memory used by external components in the SQL Server address space is not considered while SQL Server derive the memory utilization percentage.

Memory_utilization_percentage =  (Physical_memory_in_use_kb/Memory allocated by SQL Server using SQL Server memory manager) * 100

To reproduce the above behavior download VirtualallocLeak.dll from THIS link and copy to ‘C:\EXE\’ folder.

Execute the below script and then query the sys.dm_os_process_memory DMV

select * from sys.dm_os_process_memory
exec sp_addextendedproc  'VirtualallocLeak','C:\exe\VirtualallocLeak.dll' 
exec VirtualallocLeak –This allocates 1048576 bytes per execution
go 300
select * from sys.dm_os_process_memory

Review the memory_utilization_percentage , Virtual_address_space_committed_Kb and Physical_memory_in_use_Kb

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer:

The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

Posted in SQL Server Engine, SQL Server memory | Tagged: , , , | 8 Comments »

 
%d bloggers like this: