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
59 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 ...
's user avatar
2 votes
1 answer
154 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 ...
  • 23
0 votes
0 answers
51 views

Best Practice to Perform DBCC CHECKDB [duplicate]

We are looking for an optimal strategy to perform consistency checks (DBCC CHECKDB) in our environment. Currently we run the following on each database once a day starting at 04:30 am (when workload ...
  • 1
-5 votes
1 answer
87 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 ...
  • 2,800
-2 votes
1 answer
95 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 ...
  • 2,800
2 votes
1 answer
96 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 ...
  • 31
1 vote
0 answers
113 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 / ...
1 vote
2 answers
91 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 ***...
  • 111
2 votes
1 answer
41 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 ...
  • 21
3 votes
0 answers
38 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, ...
  • 31
0 votes
2 answers
40 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 ...
6 votes
1 answer
535 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). ...
0 votes
1 answer
54 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. ...
  • 231
-2 votes
1 answer
73 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 ...
3 votes
3 answers
394 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 ...
  • 3,639
0 votes
0 answers
68 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 ...
0 votes
1 answer
59 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 ...
1 vote
0 answers
53 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.[...
  • 111
0 votes
0 answers
128 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 ...
0 votes
1 answer
109 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 ...
  • 2,800
0 votes
0 answers
87 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 ...
-1 votes
1 answer
101 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 ...
4 votes
3 answers
896 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 ...
  • 340
0 votes
1 answer
246 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 ...
  • 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 ...
1 vote
1 answer
158 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 ...
  • 3,639
2 votes
3 answers
955 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 ...
  • 2,137
0 votes
1 answer
65 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 ...
  • 97
1 vote
1 answer
94 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. ...
1 vote
0 answers
150 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 ...
  • 11
4 votes
1 answer
163 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 ...
2 votes
1 answer
305 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 ...
  • 273
2 votes
1 answer
954 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 ...
  • 273
0 votes
2 answers
134 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 ...
  • 340
0 votes
1 answer
302 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 ...
9 votes
1 answer
504 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 ...
  • 387
0 votes
1 answer
249 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 ...
  • 111
0 votes
1 answer
270 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 ...
  • 97
0 votes
0 answers
116 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 ...
  • 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 ? ...
1 vote
2 answers
160 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 ...
2 votes
2 answers
657 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 ...
  • 25
0 votes
1 answer
5k 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 ...
1 vote
1 answer
339 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(...
0 votes
0 answers
75 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% ...
1 vote
2 answers
129 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 ...
  • 1,193
2 votes
1 answer
151 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 ...
  • 256
5 votes
2 answers
344 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....
  • 1,193
0 votes
1 answer
79 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 ...
1 vote
1 answer
215 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 ...
  • 143

1
2 3 4 5
7