cluster failover --memory

  • 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?

  • 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" 😉

  • 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.

  • 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.

  • 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" 😉

  • lock pages in memory set for sql server service account. No traces running.

  • 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

  • I'm assuming the OP is referring to

    DBCC DROPCLEANBUFFERS

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 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

  • Perry Whittle (3/7/2014)


    I'm assuming the OP is referring to

    DBCC DROPCLEANBUFFERS

    Yeah, just wanted to confirm from his end ..

    --

    SQLBuddy

  • Yes Perry is right. I mean DBCC DROPCLEANBUFFERS. Edited the previous post.

  • 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" 😉

  • Nope. I never ran the checkpoint stmnt.

  • any suggestions?

  • 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