none
SQL Server 2016 TEMPDB Configurations RRS feed

  • Question

  • Hi Folks,

    We are using below SQL Server Edition on Windows Server 2016 Standard

    Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor) 

    One of user complaint that tempdb drive getting full so fast.

    When I checked tempdb file configuration, I found below things which is highlighted in screenshot. It looks that somebody has done wrong configuration of tempdb files. What needs to be done in order to resolve tempdb full issue.

    The query which was shared by user is a select query in which many operations ( SUM , AVG etc ) performed using LEFT JOIN, INNER JOIN and GROUP BY clause.

    Please suggest how to resolve the issue??

    Wednesday, May 2, 2018 1:44 PM

All replies

  • Hi Folks,

    We are using below SQL Server Edition on Windows Server 2016 Standard

    Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor) 

    One of user complaint that tempdb drive getting full so fast.

    When I checked tempdb file configuration, I found below things which is highlighted in screenshot. It looks that somebody has done wrong configuration of tempdb files. What needs to be done in order to resolve tempdb full issue.

    The query which was shared by user is a select query in which many operations ( SUM , AVG etc ) performed using LEFT JOIN, INNER JOIN and GROUP BY clause.

    Please suggest how to resolve the issue??

    Tempdb getting filled is not necessarily an issue tempdb is only going to be used as the query you run. The only thing unnecessary in the screenshot is additional log file. It can be removed there is hardly any improvement with multiple log files. Plus the location of one of the tempdb log files is in drive which is supposed to hold data file and vice versa

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, May 2, 2018 1:55 PM
    Moderator
  • Sorry didnt understand

    What according to you is wrong here?

    As I see couple of files are in different drive but that is not the cause for making tempdb full

    Tempdb can become full due to multiple issues

    like say long running uncommited transaction, utilizing lots of temporary tables (explicit/implicit due to internal operations like sort), spilling over of data,DBCC commands etc

    So you need to analyze what is causing it and take necessary steps. May be break bigger transaction into smaller chunks etc


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, May 2, 2018 2:02 PM
  • Thank You Shashank :)
    Wednesday, May 2, 2018 3:41 PM
  • Thank You Visakh :)

    Will check further

    Wednesday, May 2, 2018 3:41 PM
  • I would say that the most likely reason for tempdb running out of space,is that the drive it is located on is too small, or it is crammed with other junk.

    What does "sp_helpdb tempdb" return when you get the error that tempdb is full?

    Wednesday, May 2, 2018 9:25 PM