Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


cluster failover --memory


cluster failover --memory

Author
Message
muth_51
muth_51
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 2860
When we failover as the sql services gets restarted, how the memory behaves with during this period. We had an issue last weekend after failover the services got restarted as there was insufficient memory available and during this period all the transactions were failing due to lack of memory. We had enough memory but the sql server memory was increasing in small chunks. What could be the reason behind it?
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8777 Visits: 16548
muthyala_51 (3/7/2014)
When we failover as the sql services gets restarted, how the memory behaves with during this period.

Same as any other time. The services go offline so SQL Servers memory is released after being flushed to disk.
When the service restarts it will start from zero.



muthyala_51 (3/7/2014)
We had an issue last weekend after failover the services got restarted as there was insufficient memory available and during this period all the transactions were failing due to lack of memory. We had enough memory but the sql server memory was increasing in small chunks. What could be the reason behind it?

When the service is restarted it will accumulate memory up to the max set for the instance.
Who made the decision to restart as memory was low, what proof drove this decision?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
muth_51
muth_51
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 2860
The sql services got restarted by itself. It didn't failed over to other node. The services got restarted on the same instance. We tried to run the dbcc DROPCLEANBUFFERS but still the server got restarted on the same instance twice. After that it was back to normal. total memory on the server 500 GB , max memory setting for sql server 400. The memory got accumulated to around 200 GB and services got restarted. During this time we have seen dead locks happening on insert queries. Nothing much. Just backups and audit log jobs which load data from files to tables were running.
muth_51
muth_51
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 2860
My assumption is the accumulation of memory by sql server was at a slower pace than the memory required for the queries to run on the server at that time. This might have led to the lack of memory errors causing the server to choke and restart the services.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8777 Visits: 16548
Do you have lock pages in memory policy set or any trace flags

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
muth_51
muth_51
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 2860
lock pages in memory set for sql server service account. No traces running.
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
muthyala_51 (3/7/2014)
The sql services got restarted by itself. It didn't failed over to other node. The services got restarted on the same instance. We tried to run the dbcc freebuffercache but still the server got restarted on the same instance twice. After that it was back to normal. total memory on the server 500 GB , max memory setting for sql server 400. The memory got accumulated to around 200 GB and services got restarted. During this time we have seen dead locks happening on insert queries. Nothing much. Just backups and audit log jobs which load data from files to tables were running.


Did you find the reason why SQL got restarted from looking into the logs ? Also what was the intention in running this
dbcc freebuffercache
and how did you run that ?

--
SQLBuddy
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8777 Visits: 16548
I'm assuming the OP is referring to

DBCC DROPCLEANBUFFERS

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
muthyala_51 (3/7/2014)
My assumption is the accumulation of memory by sql server was at a slower pace than the memory required for the queries to run on the server at that time. This might have led to the lack of memory errors causing the server to choke and restart the services.


This might not be the case. SQL Server will grab the memory as long as there is enough memory and no contention issues.

--
SQLBuddy
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
Perry Whittle (3/7/2014)
I'm assuming the OP is referring to

DBCC DROPCLEANBUFFERS


Yeah, just wanted to confirm from his end ..

--
SQLBuddy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search