Contents
- 1 What are the best practices to place data files log files and TempDB on storage?
- 2 What best practices do you implement with respect to the number of TempDB datafiles and the location of TempDB database files in SQL Server?
- 3 How many logical disks do you need for tempdb?
- 4 How big should my SQL Server tempdb file be?
What are the best practices to place data files log files and TempDB on storage?
First, the tempdb data and log files should be placed on different physical drives than your production database data and log files. Because tempdb is so active, it’s also a good idea to make sure the drives are protected with RAID 1 or striped with RAID 10.
What best practices do you implement with respect to the number of TempDB datafiles and the location of TempDB database files in SQL Server?
The recommended practice is to keep files and the initial space required for each file to be roughly 80 to 90% of the volume on which the TempDB is stored. The 10 to 20% disk space is left for OS-based virtual memory.
How do I know my tempdb size?
It is easy to use SSMS to check the current tempdb size. If you right click on tempdb and select Properties the following screen will open. The tempdb database properties page will show the current tempdb size as 4.6 GB for each of the two data files and 2 GB for the log file. If you query DMV sys.
Where do I put my tempdb log files?
I prefer to keep the tempdb data files on their own drive so they can be sized to fill the drive and place the tempdb log files with the user database log files where there should be enough free disk space for unexpected autogrow events for any log file.
How many logical disks do you need for tempdb?
At the very minimum you should aim to have one logical disk for data files, one for transaction log files, and one for tempdb data files.
How big should my SQL Server tempdb file be?
A default installation of any SQL Server edition will create a tempdb database with an 8MB data file and a 1MB transaction log file.
What’s the best way to set the size of tempdb?
First of all, unless you’re running SQL Server Express, set tempdb to be bigger than the default; that’s an easy one. Next, if you can give tempdb its own disk, then configure it to almost fill the drive. If nothing else will ever be on the drive, then you’re better off setting it to be larger than you’ll ever need.