Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

cluster failover --memory Expand / Collapse
Author
Message
Posted Friday, March 7, 2014 9:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 419, Visits: 2,118
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?
Post #1548779
Posted Friday, March 7, 2014 9:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 6,742, Visits: 14,376
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"
Post #1548784
Posted Friday, March 7, 2014 9:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 419, Visits: 2,118
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.
Post #1548794
Posted Friday, March 7, 2014 9:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 419, Visits: 2,118
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.
Post #1548796
Posted Friday, March 7, 2014 10:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 6,742, Visits: 14,376
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"
Post #1548809
Posted Friday, March 7, 2014 11:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 419, Visits: 2,118
lock pages in memory set for sql server service account. No traces running.
Post #1548839
Posted Friday, March 7, 2014 12:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1548859
Posted Friday, March 7, 2014 12:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 6,742, Visits: 14,376
I'm assuming the OP is referring to

DBCC DROPCLEANBUFFERS


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1548861
Posted Friday, March 7, 2014 12:46 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 1,194, Visits: 2,234
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
Post #1548868
Posted Friday, March 7, 2014 12:48 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 1,194, Visits: 2,234
Perry Whittle (3/7/2014)
I'm assuming the OP is referring to

DBCC DROPCLEANBUFFERS


Yeah, just wanted to confirm from his end ..

--
SQLBuddy
Post #1548870
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse