Configuring multiple tempdb files is not a new feature in SQL Server and has been a best practice for sometime to help ease and prevent tempdb contention which helps to increase the scalability of your SQL Server installation. Before SQL Server 2016, this was a task which needed to be carried out after the installation but one of the new features of SQL Server 2016 is the … [Read more...] about SQL Server 2016 New Features – Multiple TempDB Files Configured at Server Install
There may come a time when you might want to shrink tempdb because it has become too large. There are a few ways you can do this and I have listed them below but please read to the end of the post before making a decision on which way you want to approach this. There is an important note at the end of the post. So first, we'll look at the configuration on my … [Read more...] about How to shrink tempdb
SQL Server TempDB is a system database, automatically created when you install SQL Server. So what is it used for? Well a few things actually but first I have to tell you that Microsoft didn't name tempdb because they couldn't think of a suitable name. It is a temporary database which is re-created every time the SQL Server service is started and at a higher level, it … [Read more...] about SQL Server TempDb – what’s it for?
Continuing on from my post describing what SQL Server TempDB is used for, I wanted to write about tempdb best practices and how on a busy system, modifications to the default setup of tempdb and its placement can boost performance. How to optimize tempdb Place tempdb on a separate disk to user databases. See my post on how to move tempdb. Place tempdb on the fastest … [Read more...] about TempDB best practices
How to move tempdb ? So in this post, I show you what is involved to do this and what you shouldn't try and do. This is how to move tempdb You will need to run run some T-SQL and restart the sql server service to complete the operation. The logical file name values will need to be obtained and there are a couple of ways to do that. You can either view the logical file … [Read more...] about How to move tempdb