niaidsdt
asked on
SQL server Multiple Tempdb
We have a sql 2008r2 default instance running that has 2 Tempdb's. The server has two processors. For some reason, one tempdb is MUCH larger than the other. WHy is the default instance of SQL 2008r2 not using the 2nd Tempdb
Tempdb1 ~ 29Gb
Tempdb2 ~ 35Mb
Tempdb1 ~ 29Gb
Tempdb2 ~ 35Mb
ASKER
Sorry for the poor wording. Yes, we have 2 Tempdb files.
The data files should be the same size. I would manually set the two tempdb files to a decent size and turn auto increment off. Take a look at this list of tempdb best practices.
http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx
Greg
http://blogs.msdn.com/b/cindygross/archive/2009/11/20/compilation-of-sql-server-tempdb-io-best-practices.aspx
Greg
>>WHy is the default instance of SQL 2008r2 not using the 2nd Tempdb<<
But to answer your question: It has not needed to use it.
But to answer your question: It has not needed to use it.
ASKER
Can you reference a KB or article that explains when the database would use the 2nd tempdb?
This is not exactly what you had in mind, but it does address your question in a round about way:
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx
ASKER
anyone got a good explannation as to why the 2nd tempdb is not being used?
ASKER
Why is it best practice to have a tempdb file per processor?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Greg