March 7, 2014 at 9:10 am
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?
March 7, 2014 at 9:27 am
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" 😉
March 7, 2014 at 9:45 am
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.
March 7, 2014 at 9:52 am
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.
March 7, 2014 at 10:22 am
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" 😉
March 7, 2014 at 11:36 am
lock pages in memory set for sql server service account. No traces running.
March 7, 2014 at 12:26 pm
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
March 7, 2014 at 12:31 pm
I'm assuming the OP is referring to
DBCC DROPCLEANBUFFERS
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 7, 2014 at 12:46 pm
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
March 7, 2014 at 12:48 pm
Perry Whittle (3/7/2014)
I'm assuming the OP is referring toDBCC DROPCLEANBUFFERS
Yeah, just wanted to confirm from his end ..
--
SQLBuddy
March 7, 2014 at 1:55 pm
Yes Perry is right. I mean DBCC DROPCLEANBUFFERS. Edited the previous post.
March 8, 2014 at 12:07 am
muthyala_51 (3/7/2014)
Yes Perry is right. I mean DBCC DROPCLEANBUFFERS. Edited the previous post.
You need to checkpoint the database(s) before dropping clean buffers. Did you run checkpoint first?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 8, 2014 at 5:12 am
Nope. I never ran the checkpoint stmnt.
March 8, 2014 at 6:43 pm
any suggestions?
March 9, 2014 at 3:33 am
muthyala_51 (3/8/2014)
any suggestions?
For what?
anything now would just be speculation since the instance was restarted
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply