Will adding files to TempDB replicate to 2nd node within an always on group?
-
I want to add 3 more datafiles to tempdb on a database server that has a second node in an always on high availability group.
With MSSQL (I'm more used to Oracle) when I do this on the primary server, does the DDL get ran on the 2nd node and the files created on it (assuming file have identical location on each node) or do I have to add them manually to the 2nd node?
I am assuming the DDL should be run on the second when it syncs, but want to be sure.
Thanks in advance.
-
No, system databases cannot be part of the databases in an availability group.
If you need to add files to tempdb, you will need to do it on all of the nodes in the AG.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Okay thanks, all I needed to know!
-
What is the reasoning behind adding additional tempdb files? Is there an issue you are trying to resolve - and have identified that issue as being related to tempdb?
If the systems are performing well now - there isn't any reason to add more files to tempdb.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks for the reply.
The database is supporting a Dynamics D365 application, and the vendor supporting it recommended that tempdb have the same amount of datafiles as processors on the server. Currently there are 2 datafiles for each node, and 4 cores on each.
There has been some out of memory errors reported by users of the application, and the vendor recommended the additions.
One thing I didn't think of, was can I add to the 2nd node directly or do we need to failover to it then add. This always-on is relatively new to me.
-
No, you do not need to failover to add files to tempdb. Are you confusing an AG to an Oracle grid installation? Very different beasts.
The system databases, master, model, msdb, and tempdb, are separate from the AG. Adding files has no effect on the AG.
Out of memory errors in the application? Specifically, which ones?
Is Dynamics installed on the same server as SQL? If so, has the max memory in SQL been set properly? Regardless, this should be set. I cannot explain it better than this article. https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John wrote:
No, you do not need to failover to add files to tempdb. Are you confusing an AG to an Oracle grid installation? Very different beasts.
The system databases, master, model, msdb, and tempdb, are separate from the AG. Adding files has no effect on the AG.
Out of memory errors in the application? Specifically, which ones?
Is Dynamics installed on the same server as SQL? If so, has the max memory in SQL been set properly? Regardless, this should be set. I cannot explain it better than this article. https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
I've got a blurry screenshot that I believe says: "a timeout occurred while waiting for memory resources to execute the query in 'internal'(1), Rerun the query.
Dynamics is not installed on the same server as the DB. The database server has 24GB memory, showing about 4GB available.
-
Please run this query and show us the results:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'max server memory (MB)'
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Here you go.
-
That's likely part of your problem.
The Max server setting specifies how much memory SQL can use. Your server is set to the default, which is everything!
If you read the link I sent, this is the starting point to set the max memory for your server.
EXEC sp_configure 'max server memory (MB)', 18432
RECONFIGURE
As far as the vendor recommending adding tempdb files, this used to be "required", but unless there is contention, it may not make a difference.
More good reading on the subject:
https://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply