Is it a good practice to limit the tempdb data files size when the drive is getting full

Kranthi DBA 221 Reputation points
2021-01-15T06:41:32.477+00:00

I am seeing an issue wherein the tempdb drive is given only 50 GB size(4 tempdb data files are present within the drive) and the drive is getting filled up during maintenance job run due to increase in tempdb file sizes. I was told by the server team that they cannot increase the drive size and instead asked me create additional files in some other drive which has enough free space.

I have created additional 4 files in a separate drive, but the old drive is still getting full. Can I limit the max file of the datafiles to avoid disk space issues? Please advise if there would be any performance issues for doing so.

I have already asked the client if I can move all of the files to the new drive, but client is not okay with that, so that option is ruled out.

Thanks in Advance!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,925 questions
{count} votes

Accepted answer
  1. Martin Cairney 2,241 Reputation points
    2021-01-15T07:32:46.023+00:00

    When you specify the files, ensure they ALL have the same initial size and growth size. You want to ensure a proportional fill of the files.

    Work out the total size you need for the processing (for example let's say 80 GB). From that also work out the space you have available on the original drive (for this example, let's say 20GB). Now the 20GB you had available is for 4 files - so each is allowed up to 5GB. Therefore to keep your balanced fill you need to create 60GB worth of files with each file allowed up to 5GB -> you need 12 files and not just the 4.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Shashank Singh 6,246 Reputation points
    2021-01-15T06:48:17.82+00:00

    I have created additional 4 files in a separate drive, but the old drive is still getting full.

    Stop the autogrowth of these old files and it will start filling the new files on new drive. Although it is poor idea to add tempdb files without any consideration but if you don't have nay choice go ahead . Idont know why your client is not allowing to move tempdb files to new drive, is the new drive slow ?

    1 person found this answer helpful.
    0 comments No comments

  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-01-15T09:43:48.49+00:00

    Hi @shobana sivakumar ,

    > Can I limit the max file of the datafiles to avoid disk space issues?

    If the size of old files reach the limit size, it will start fill the additional 4 files in a separate drive. But suggest you adjust your environment follow below suggestion to get the best performance from that shared resource and protect your SQL Server instance from any unexpected growth in the TempDB database data or log files that may fill the underlying disk space. These configurations include:

    • The number of the TempDB data files should match the number of logical processors, up to eight files, on the machine where the SQL Server instance is installed. If the number of logical processors on that machine is greater than eight, set the number of data files to eight, with the ability to extend it by multiple of 4 in case you have a TempDB contention issue
    • Make sure that the TempDB files have equal size within the same filegroup to provide the highest possible degree of parallel operations efficiency, due to the Proportional Fill writing mechanism that is used to write the data inside the data files.
    • Configure the TempDB database data and log files with a reasonable initial size and auto-growth amounts that fits the expected amount of data that will be written on these files, and the different types of workloads performed in your SQL Server instance, including user activities and index maintenance operations, in order to avoid the frequent small increment amounts that affect the overall instance performance
    • Locate the TempDB files on a fast I/O subsystem in order to avoid any I/O bottlenecks
    • Locate the TempDB on a separate disk drive, away from the user databases and other system databases. In this way, the heavy read and write operations performed on TempDB will not affect or be affected by the workload of the user databases, enhancing the overall SQL Server instance performance.

    Please refer to blog How to detect and prevent unexpected growth of the TempDB database and MS document Performance improvements in tempdb for SQL Server to get more information.

    Best regards,
    Cathy


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments No comments