Questions tagged [tempdb]

TempDB is part of MS SQL Server. It's a system database that functions as a storage area for temporary tables, internal objects and row versions.

Filter by
Sorted by
Tagged with
-2 votes
1 answer
19 views

Does tempdb recommended count depend on the total processors on the computer or on the processors in a single NUMA node?

The number of secondary data files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same ...
user avatar
  • 2,130
2 votes
1 answer
32 views

temp DB file does not exist after moving

I am using SQL server 2019 AG. I am facing an issue after moving temp DB files from one drive to another after locating the file paths by this script: Use master GO SELECT name ...
user avatar
  • 31
1 vote
0 answers
89 views

Any benefit creating temp tables with random prefixes in SQL Server to control latch waits on tempdb database

Recently I came across reading in one of the blogs that if a temp table name is prefixed with different/random names (in different procedures) that could benefit in reducing the concurrency issues / ...
user avatar
1 vote
2 answers
23 views

Change location of innodb_temporary

I want to change the path where the ibtmp1 is stored in mysql 8.0.25 on debian 11. How to do this? I see: mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G ***...
user avatar
  • 111
2 votes
1 answer
38 views

Alter the collation of a column in temp-file

I have a routine in a DB that uses a temp-file. But the collation of the DB doesn't match the collation of the temp-DB. I can't change the collation of the DB, it's a client DB. I tried this: BEGIN ...
user avatar
  • 21
3 votes
0 answers
31 views

Monitor tempdb iops on Azure VM

I've inherited SQL on Azure VMs where not all the tempdb files are on the ephemeral D: drive. I'd like to move them back there as that is the MS recommended location for latency reasons. However, ...
user avatar
  • 31
0 votes
2 answers
37 views

configure size temdb [duplicate]

I have a disk space of 743 GB which contains the tempdb database there was a request to set the max size tempdb to 669GB keeping 10% percent as free disk space I have 8 tempb file + log file how can I ...
user avatar
6 votes
1 answer
220 views

DBCC CHECKDB on a user database: Time-out occurred while waiting for buffer latch type 2 for page (X:XXX), database ID 2

Our Ola Hallengren IntegrityCheck job failed due to a buffer latch timeout while running DBCC CHECKDB on a user database. However, the buffer latch timeout reported was in TempDB (database ID 2). ...
user avatar
0 votes
1 answer
49 views

Unable to connect to Database Engine after trying to move tempdb

I've just made a boo-boo in my personal SQL instance. I wanted to move tempdb files to C:\tempdb.mdf and C:\templog.ldf respectively. The alter database ...modify file... command ran successfully. ...
user avatar
  • 231
-2 votes
1 answer
60 views

SQL TempDB swells to unmanageable size [closed]

We're using SQL server 2014 SP4. Out of several stored procedures, there's a particular one that creates about 30 very large temp tables (wasn't created by myself). The process is run via daily SQL ...
user avatar
3 votes
3 answers
129 views

How to detect tempdb usage?

On-premise, we are using SQL Server 2019 Standard Edition under READ_COMMITTED isolation level and I want to test how one database with perform with READ_COMMITTED_SNAPSHOT (the Azure default). The ...
user avatar
  • 3,609
0 votes
0 answers
61 views

Excessive TempDB Usage When Using Pivot + Temp Tables

I have a stored procedure that is acting quite strangely, it basically works in this way: --Populate #tmpActualsVertical with data(in this example 84,652,788 rows) SELECT ...
user avatar
0 votes
1 answer
51 views

Can an odd number of stripes in RAID can affect tempdb performance if I have an even number of data files in tempdb?

This is out of curiosity: since it is recommended to configure tempdb data files in an even number of sets so that it can get aligned with NUMA nodes evenly, I just wanted to know if it is the case ...
user avatar
1 vote
0 answers
48 views

SQL Server 2017: sort_warning event fires when running a simple execution plan with no sorts and hash joins

sql gurus, here is a script (fragment, object creation & data population postponed): update #chart_estimate_ set [His seas data] = 1 from #chart_estimate_ ce where exists (select top 1 r.[...
user avatar
  • 111
0 votes
0 answers
115 views

SQL Server 2014 - Disk Space Issue

Currently we have a drive on your server that stores all user DB's data files along with one tempdb data file. (D: drive) It has 1.4 TB total space with only 98Gigs free space. The biggest DB size is ...
user avatar
0 votes
1 answer
83 views

How does proportional fill algorithm help with tempdb performance once the files are full?

Having about 8 tempdb data files is a good practice because of large number of object creation and deletions that happen in the tempdb. So sql server can use the data files in parallel. Given this ...
user avatar
  • 2,130
0 votes
0 answers
72 views

Stored Procedure fails on tempdb-diskspace when executed by SQL Agent

I developed a bunch of ETL procedures which are executed by an overlaying SQL Script and write Data to separate DB. The Script uses a single Transaction, ensuring we only get complete data. This ...
user avatar
-1 votes
1 answer
60 views

Need best practices to maintain TempDB which is growing to 100GB now, is moving Tempdb to 35GB drive have any limitations? [closed]

Can moving TempDB to smaller drive cause failures? How can TempDB be configure to use 35GB drive without any issues? Please suggest if something needs to be taken care for this smooth transition of ...
user avatar
4 votes
3 answers
649 views

How can we restrict tempdb usage for specific users or queries on SQL Server?

In our SQL Server production environment some users run ad hoc queries that run huge data extracts into tempdb and fills it up, causing issues on a production server. And as these reports/queries run ...
user avatar
  • 324
0 votes
1 answer
181 views

Could LCK_M_SCH_S cause a spike in read/write latency for tempdb files?

I noticed a big uptick in tempdb read/write. This was during the same period of time that someone ran Alter Index and I noticed that the biggest wait at that time was LCK_M_SCH_S. I'm trying to track ...
user avatar
  • 209
19 votes
2 answers
2k views

SQL Server in-memory optmized tempdb metadata memory usage continually growing

Problem We have enabled sp_configure 'tempdb metadata memory-optimized' = 1, and now the tempdb meta data is taking over 400 GB on one of our servers and continues to grow. There are some drops in ...
user avatar
1 vote
1 answer
90 views

When tempdb allocations are released?

In a Service Broker queue, when one message is proceed over time, we are executing a stored procedure which is performing some heavy operations using temporary tables. I guess that the sp_WhoIsActive ...
user avatar
  • 3,609
2 votes
3 answers
493 views

Urgent help with TempDB log file growth mystery

I need some urgent guidance for a tempdb issue for past last week: The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down ...
user avatar
  • 2,127
0 votes
1 answer
48 views

does SELECT with implicit transaction prevent VersionStore cleanup?

The JDBC driver usually sets the implicit transaction on in every query executed by the client. I found many SELECT statements in suspended state with implicit transaction set to ON using Version ...
user avatar
  • 97
1 vote
1 answer
64 views

Avg write latency spikes for disk more than usual. Need help finding the culprit

We recently had increased in disk write latency reported. Though from our monitoring team it was not confirmed from which drive but doing some dmv query I see tempdb around avg 32 ms wait on write. ...
user avatar
1 vote
0 answers
116 views

Error [36, 17, 145] occurred while attempting to drop allocation unit ID ##### belonging to worktable with partition ID #####

We regularly get this error on a SQL Server 2016 SP2 instance. (Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise ...
user avatar
  • 11
4 votes
1 answer
143 views

sys.dm_tran_version_store_space_usage reports always zero space used

I was learning about RCSI when I discovered something strange on Azure SQL Database. The DMV sys.dm_tran_version_store_space_usage always reports 0 as the space used by the version store, even if I ...
user avatar
2 votes
1 answer
250 views

System SPID running for hours filling up the TempDB Log

I've been trying to track down the source of my tempdb log filling up every few weeks where the tempdb log space used will continue to grow until it reaches 99% and stays there for hours while the log ...
user avatar
  • 273
2 votes
1 answer
691 views

Unexplained sustained space used in the TempDB log file

A few days ago the amount of used space in both the tempdb data and log files started growing rapidly. After 30 minutes, the tempdb log remained around 99% space used for about 7 hours. Towards the ...
user avatar
  • 273
0 votes
2 answers
67 views

reapply permissions post sql server restart

In our environment, users were granted read/write/ddladmin permissions on tempdb database (dev/qa/prod). These permissions will be lost when SQL Services restart, as tempdb rebuilds every time. One ...
user avatar
  • 324
0 votes
1 answer
234 views

Killed SPID on xp_LogInfo not rolling back and growing tempdb [duplicate]

I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task. I checked the scheduled tasks and we had ...
user avatar
9 votes
1 answer
456 views

2019 Enterprise - NOLOCK without NOLOCK, page latch time-out on tempdb and finally a dump [closed]

We have a very strange problem with a new SQL Server 2019 on 2 new physical machines: Infrastructure: Starting a new installation of SQL Server 2019 Enterprise (15.0.2000.5 / X64 on Windows Server ...
user avatar
  • 382
0 votes
1 answer
200 views

How can I tell if TempDb is too big?

Let's say that someone wants to follow sage advice and properly setup TempDb and TempDb's log. But let's also say that the chosen sizes are almost certainly too big/aggressive. E.g. pretend we've got ...
user avatar
  • 111
0 votes
1 answer
211 views

TempDB Version Store used by DB STARTUP background process

I have two availability group on the same SQL Server 2012 Instance (synchronous commit). Trace flags 1118 and 1117 are both enabled. TempDB has been growing for about two weeks: it starts from 36GB ...
user avatar
  • 97
0 votes
0 answers
78 views

Use of tempdb for bulk upsert operation

We are using SSIS for ETL purpose. There is a shortcoming in SSIS as it doesn't provide any in built component which will bulk upsert the destination. The alternative to it to use Staging tables which ...
user avatar
  • 23
0 votes
1 answer
1k views

TempDB Log File Growth - questions

Problem: On one of the production boxes, TempDB transaction LOG file grew unexpectedly, filling the drive It was 32 GB but now it is 240 GB Questions: What is the mechanism of TempDB log file usage ? ...
user avatar
1 vote
2 answers
136 views

Provisioning SQL Server 2019

I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with. My build has to ...
user avatar
2 votes
2 answers
312 views

MSSQL TempDb full

I have a problem with my MSSQL cluster. When switching to SQL Server 2016 (from 2014) an error occurs with the tempdb, which is full after a certain time for no apparent reason. Is there a proven way ...
user avatar
  • 25
0 votes
1 answer
4k views

tempdb not releasing space

I have 1 tempdb mdf file, and 7 ndf files. They all grew to 63 GB, and filled up the 500 GBdrive (the .mdf and .ndf files say 99% free space.). I ran sp_who2 'Active' and there is no active ...
user avatar
1 vote
1 answer
261 views

Azure tempdb filling up while trying to convert a large table to columnstore... how to mitigate?

I have a large reporting table (approx 6B rows) taking up roughly 400GB of storage on an azure sql db. I've been trying to convert it to a columnstore table using the following commands: insert Logs(...
user avatar
0 votes
0 answers
71 views

How do I debug an "Operator used tempDB to spill" warning when statistics appear to be ok

I have a section of a complex query plan where a Sort operator appears directly after an Index Seek and SSMS shows a "Operator used tempDB to spill" warning. It is reported to be taking 70% ...
user avatar
1 vote
2 answers
104 views

Why does TempDB spill happen even though statistics are correct?

I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get ...
user avatar
  • 1,163
2 votes
1 answer
123 views

Version Store usage and entries in dm_tran_active_snapshot_database_transactions

So I'm trying to understand why are having occasional problems with tempdb filling up with version store entries. I'm closing in on the problem, and am now seeing entries in ...
user avatar
  • 256
4 votes
2 answers
240 views

is there any point of moving tempdb files into different disk drive if all LUNs are from the same RAID pool?

According to best practices, it is recommended to move all tempdb (not just tempdb) files into different physical disk. I have a virtual server which had originally 4 LUNs from the same RAID 10 pool....
user avatar
  • 1,163
0 votes
1 answer
68 views

Increase TempDB Files, Shrink VLF's with FCI

I am planning to resize and increase the number of tempDB files and shrink the VLF's on an FCI. In this case, the tempdb files are stored on the network drive, rather than the local disk. I couldn't ...
user avatar
1 vote
1 answer
178 views

How do you minimize chance of collision between system-named constraints on temp tables?

We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures attempt to create temp tables with a system-named PK ...
user avatar
  • 143
2 votes
1 answer
979 views

High write latency on tempdb datafiles only

I'm experiencing large write latency on our tempdb database, but on no other database. (from sys.dm_io_virtual_file_stats) Tempdb log file latency is also listed as being below 5ms. For comparison: ...
user avatar
  • 51
3 votes
2 answers
224 views

Should tempdb be larger on a secondary replica?

I am working on SQL Server 2012 with Availability Groups. On the secondary replica, the size of the tempdb datafile is roughly double that of the primary. I am struggling to find information on ...
user avatar
0 votes
1 answer
51 views

TempDB Configuration using 8 TempDB Data File

On a production server with 512 GB RAM , 2 Sockets 24 Cores (Total Logical Processors 48), i have configured to have 8 TempDB Data Files ( on Primary) with each 4096 MB growth of 1024 MB and Log File ...
user avatar
1 vote
0 answers
181 views

SQL Server 2017, XEvent: sql_text IS NOT NULL

I'm attempting to use XEvents to find out which queries are causing my templog.ldf file to grow in size. I'm getting events, but I see the sizes of the files haven't changed, and that the sql_text ...
user avatar

1
2 3 4 5
7