June 1, 2017 at 3:59 pm
Some server I inherited had multiple tempdb data files and 1 log file.
He prob created to distribute IO but he put all files on same drive which doesnt do good . also its a VM ware with VMDK drives.
Is there a way to delete all extra tempdb files and just have 1 mdf and 1 ldf?
June 1, 2017 at 4:24 pm
sqlguy80 - Thursday, June 1, 2017 3:59 PMSome server I inherited had multiple tempdb data files and 1 log file.He prob created to distribute IO but he put all files on same drive which doesnt do good . also its a VM ware with VMDK drives.
Is there a way to delete all extra tempdb files and just have 1 mdf and 1 ldf?
Multiple files addresses allocation contention issues with tempdb so multiple files actually does serve a purpose even if on the same drive. Having multiple data files for tempdb has been a common practice for awhile. The multiple files is now the default for tempdb when installing SQL Server 2016. The default number used is 8 or the number of logical cores, whichever is less.
There is some more information and other links in the following article:
Recommendations to reduce allocation contention in SQL Server tempdb database
Sue
June 2, 2017 at 6:57 am
sqlguy80 - Thursday, June 1, 2017 3:59 PMHe prob created to distribute IO but he put all files on same drive which doesnt do good.
Correct, if you want to distrbute the I\O then use multiple drives, however, that's not the main reason for allocating multiple data files to Tempdb.
The main reason is to alleviate allocation contention on certain pages within the database. These files are typically placed on the same drive.
sqlguy80 - Thursday, June 1, 2017 3:59 PM
Is there a way to delete all extra tempdb files and just have 1 mdf and 1 ldf?
Remove the files from the system catalogs using the command below, then restart the SQL Server instance
ALTER DATABASE ... REMOVE FILE ...
Monitor for allocation contention on TempDB and add files if necessary observing the best practice limits
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 2, 2017 at 10:00 am
If the disks are being sourced from a SAN, having separate drives for each tempdb file wouldn't matter, as each "drive" would probably be carved out of the same RAID array and physical disks in the SAN anyway.
June 2, 2017 at 11:36 am
sqlguy80 - Thursday, June 1, 2017 3:59 PMSome server I inherited had multiple tempdb data files and 1 log file.He prob created to distribute IO but he put all files on same drive which doesnt do good . also its a VM ware with VMDK drives.
Is there a way to delete all extra tempdb files and just have 1 mdf and 1 ldf?
To be honest, unless you have more than 8 files, I'd probably leave it. It's not going to cause problems at that number, and it helps reduce allocation contention in TempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply