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
0 votes
1 answer
53 views

76% of the RAM usage on my server is TEXT_MIX_PAGE in tempdb. What does this indicate?

I quite like dbatools, so I gave Get-DbaDbMemoryUsage (docs here) a try on my test SQL Server 2019 server. Specifically, I ran this Get-DbaDbMemoryUsage -SqlInstance [MyServer] -IncludeSystemDbs | ...
J. Mini's user avatar
  • 109
-1 votes
1 answer
73 views

Can Query Store Show Spills To Tempdb?

I know that Query Store can show tempdb usage as of SQL Server 2017, but can it show spills to tempdb? I'm highly confident that the plan cache can.
J. Mini's user avatar
  • 109
0 votes
1 answer
72 views

What could be causing tempdb version store growth on primary node in the absence of open/active transactions on readable secondary db?

This is a SQL server 2022 CU10 enterprise edition server, 2 node AG with readable secondary. 30 user dbs are in the AG. Via an inhouse SQL monitoring tool I observed tempdb version store growth on ...
variable's user avatar
  • 3,593
3 votes
3 answers
613 views

Given that CREATE PROCEDURE must be the first statement of its batch, what is the use of Temporary Stored Procedures?

I was recently made aware of temporary stored procedures and became shocked that I've never seen them used before in my career. I was very disappointed when I remembered that CREATE PROCEDURE must be ...
J. Mini's user avatar
  • 109
5 votes
1 answer
1k views

how to kill a rogue spid that is already killed?

I have an obstinate spid that I cannot kill and it is preventing the transaction log of my tempdb to get truncated this is how I found this rogue spid: if object_id('tempdb..#OpenTranStatus','U') is ...
Marcello Miorelli's user avatar
-4 votes
1 answer
62 views

Is the role of "tempdb" in SQL Server similar to that of "swap" in MySQL and Linux?

do we have the "tempdb" concept in other database technologies or it is just in SQL Server.
Milad Hamid Elhaei Sahar's user avatar
0 votes
1 answer
103 views

Difference between forwarded_fetch_count and forwarded_record_count

I have been scratching my head, re-reading MSFT help and I still cannot understand the difference between forwarded_fetch_count and forwarded_record_count in sys.dm_db_index_operational_stats and sys....
Sranda's user avatar
  • 360
3 votes
2 answers
655 views

TempDB GAM contention for SELECT queries

I have problem manifesting itself in following fashion, my TempDB knowledge does not cover this (yet): An analytical query, that runs in SSMS for about 200ms, keeps running on SQL server, when ...
Sranda's user avatar
  • 360
0 votes
0 answers
490 views

Why is error message stating TEMPDB has insufficient disk space in filegroup 'DEFAULT' when my TEMPDB filegroup is named 'PRIMARY'

I have a remote user getting this error message when running a query. Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the ...
Don's user avatar
  • 161
0 votes
0 answers
391 views

tempdb data files don't shrink

Our tempdb used 99% of all available space on disk, but DBCC SHRINKFILE doesn't help. This is how much disk space tempdb files take: But when I check how much space is really used, I see this: I ...
GriGrim's user avatar
  • 302
0 votes
2 answers
219 views

What's the issue with keeping a SQL Server login's default database as 'master'

I've read recommendations to switch non-sysadmins login's default database to something other than master. Tempdb is often recommended. If a login/user has no permissions by default for their default ...
Marcus's user avatar
  • 59
0 votes
0 answers
562 views

Transaction aborted when accessing versioned row in table 'dbo.sysmail_profile' in database 'msdb'

I'm having some problens with SQL Database mail, basicaly anything that I try to do gives me an error like this one below: Transaction aborted when accessing versioned row in table 'dbo....
Igor  Amato's user avatar
0 votes
0 answers
186 views

How does shrinking of tempdb log file work?

On the following documentation page: https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-tempdb-database?view=sql-server-ver16 It states that: Assume that tempdb has two files:...
Fajela Tajkiya's user avatar
2 votes
1 answer
250 views

Does ALLOW SNAPSHOT ISOLATION need to be set on Tempdb itself?

I'm in a debate over whether you also need to set ALLOW SNAPSHOT ISOLATION on tempdb itself, or only on user databases for the functionality to kick in (in addition to code referencing this)? Most ...
stack_henk's user avatar
3 votes
1 answer
844 views

How To Optimize a Query That Aggregates Then Duplicates a Large Number of Records in a Single Table

In the following post, J.D. brings up that I have a poorly performing query. I am running this query on SQL Server 2019 Standard Edition (query plan was generated in Development Edition) Let's take a ...
David Rogers's user avatar
2 votes
1 answer
103 views

AWS to Azure Degraded Performance With "Hash Match (Aggregate) Step"/"Temporary Disk" TempDB Performance Issue

Ok, so I have a series of AWS VM's, mostly using the z1d.3xlarge server class with built in SQL Server 2019, these servers each contain a NVMe of various size. (Not surprisingly) I've placed my SQL ...
David Rogers's user avatar
0 votes
1 answer
48 views

Can pregrowing tempdb hurt performance?

Late yesterday afternoon I added 4 tempdb data files to the existing 4 for a total of 8 (16 processors on the SQL server). I also pre-grew them to near 100% of the available space. Today I'm contacted ...
Don's user avatar
  • 161
2 votes
0 answers
102 views

Are tempdb allocations released after transaction is commited?

In addition to this I am again wondering if the code in a transaction and loop release the tempdb allocations. For the template below: SET NOCOUNT ON; WHILE EXISTS(....) BEGIN BEGIN TRY; ...
gotqn's user avatar
  • 4,016
0 votes
1 answer
599 views

Tempdb Log is FULL [duplicate]

So my tempdb log has 0% free when I checked my Disk Management in my windows server, I went to SSMS and checked the disk usage report GUI and it shows me that my transaction log is 99% free and the ...
WhoIsNotActive's user avatar
1 vote
0 answers
102 views

High parallel tempdb disk usage

We recently added some secondary tempdb files to reduce allocation contention, this seems to have worked; however we are now getting lots of slowdowns related to I/O (WRITELOG and SQLTRACE_FILE_BUFFER ...
Richard Harris's user avatar
2 votes
0 answers
96 views

Service Broker enabled on tempdb in SQL Server

I am seeking an answer what might me the reason/scenario to have Service Broker enabled on tempdb. I have a legacy SQL Server and it is enabled there. Is it safe to disable it? Is it possible to ...
Bartosz Siemasz's user avatar
1 vote
1 answer
627 views

Long TRUNCATE operations

On Microsoft SQL Server 2019, there is a lot of long temp tables TRUNCATE operations with time over 5..10 seconds. Easy query like "TRUNCATE TABLE #tt22" can take over 15 seconds. It's an ...
aqis's user avatar
  • 11
3 votes
1 answer
493 views

Does large tempdb file size have impact on sql server startup time?

Sql server tempdb data and log initial file size is configurable. Assuming I set it to large values (say 10GB) then will sql server take more time to start compared to if the data/log initial file ...
variable's user avatar
  • 3,593
2 votes
1 answer
681 views

Is it ok to have 8 tempdb data files on 4 logical processor?

I aware of the general rule of thumb, which is the no. of tempdb data files = no. of cores. But I'm running SQL server 2016 with 4 logical processors and 8 tempdb data files. Just wondering if this ...
user avatar
2 votes
1 answer
2k views

Query stuck on 0% ROLLBACK for 2 days

kill with statusonly returns: transaction rollback in progress. Estimated rollback completion: 0%. The query creates a temp table and runs on local server without using xp_cmdshell. CPU & reads ...
Kati's user avatar
  • 23
-5 votes
1 answer
231 views

Does enabling readable secondary in the availability group put any load on the primary server's tempdb?

Enabling readable secondary will result in addition of 14byte extra information to each row in the primary as well as secondary. This will also automatically enable the snapshot isolation level on the ...
variable's user avatar
  • 3,593
-2 votes
1 answer
750 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 ...
variable's user avatar
  • 3,593
2 votes
1 answer
489 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 ...
Tala 's user avatar
  • 41
1 vote
0 answers
301 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 / ...
S.D.'s user avatar
  • 746
1 vote
2 answers
756 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 ***...
dr0i's user avatar
  • 121
2 votes
1 answer
162 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 ...
Florian's user avatar
  • 31
3 votes
0 answers
73 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, ...
Simon's user avatar
  • 31
0 votes
1 answer
216 views

How does blocking increase sos_scheduler_yield wait?

What is the correlation between blocking and CPU? Because I am reaching the conclusion that the blocking is causing my sos_scheduler_yield wait type to increase, but I would like to understand the ...
xhr489's user avatar
  • 805
0 votes
2 answers
50 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 ...
Abdallah Mehdoini's user avatar
6 votes
1 answer
3k 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). ...
Eitan Blumin's user avatar
0 votes
1 answer
145 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. ...
PTL_SQL's user avatar
  • 325
-2 votes
1 answer
266 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 ...
Depth of Field's user avatar
3 votes
3 answers
2k 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 ...
gotqn's user avatar
  • 4,016
0 votes
0 answers
209 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 ...
David Rogers's user avatar
0 votes
1 answer
69 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 ...
GAURAV RATHOD's user avatar
1 vote
0 answers
81 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.[...
Globe's user avatar
  • 111
0 votes
0 answers
176 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 ...
sqllover2020's user avatar
0 votes
1 answer
187 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 ...
variable's user avatar
  • 3,593
0 votes
0 answers
128 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 ...
TheDudeWithHat's user avatar
-1 votes
1 answer
371 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 ...
Humanities World's user avatar
5 votes
3 answers
2k 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 ...
Kris's user avatar
  • 422
0 votes
1 answer
463 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 ...
user80151's user avatar
  • 209
20 votes
2 answers
3k 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 ...
SQLing4ever's user avatar
1 vote
1 answer
720 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 ...
gotqn's user avatar
  • 4,016
2 votes
3 answers
3k 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 ...
BeginnerDBA's user avatar
  • 2,169

1
2 3 4 5
7