Contents
Where do I put TempDB?
Best Practice: Move TempDB to its own drive. By default, the TempDB files are put on the same drive as the SQL Server binaries. Even if the user chooses a custom install, TempDB still goes on the same drive as the other data files, and that’s not a good idea either.
How much space do I need for TempDB?
So, for a 100 GB database, you will need 1.5 – 2 GB for TempDB in order to complete the consistency check. Correspondingly, you will need approximately 15 – 20 GB for TempDB when you run the consistency check for 1 TB database.
How do I set up TempDB?
The short version: configure one volume/drive for TempDB. Divide the total space by 9, and that’s your size number. Create 8 equally sized data files and one log file, each that size. Presto, the drive is full and your TempDB is configured for easy performance.
How big should my tempdb log file be?
I have set the size of my tempdb data files to their maximum and disabled autogrowth. For example, i have 100gb of free disk space and set the size of my 8 tempdb data files to 10gb each. This prevent fragmentation on the disk as recommended by Brent Ozar and also I have 20gb free for my log file.
How to change the location of tempdb data?
Change the location of TempDB Data and Log files using ALTER DATABASE. Execute the below ALTER DATABASE command to change the location of TempDB Data and Log file in SQL Server. USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘T:\\MSSQL\\DATA\empdb.mdf’); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog,
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.
Can you move tempdb files to a new drive?
There are times when as a DBA you find the need to move TempDB Data and Log Files to a new Drive. This article explains all the steps you need to follow to move TempDB files. This article explains the steps you must follow to move TempDB database from one drive to another in SQL Server.