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
6
votes
0answers
118 views

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

We have a very strange problem with a new SQL Server 2019 on a 2 new physical machines: Infrastructure: Starting a new installation of SQL Server 2019 Enterprise (15.0.2000.5 / X64 on Windows Server ...
0
votes
1answer
35 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 ...
0
votes
1answer
28 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 ...
0
votes
0answers
39 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 ...
0
votes
1answer
115 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
2answers
61 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
2answers
100 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 ...
0
votes
1answer
91 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
1answer
45 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
0answers
30 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
2answers
49 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 ...
2
votes
1answer
67 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 ...
4
votes
2answers
97 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....
0
votes
1answer
44 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 ...
0
votes
0answers
19 views

Why do tempdb spills still occur even with good row and data size estimates (better than actuals)?

We're seeing tempdb DB spills for some hashing operations. If the estimates are indeed good as shown what would be the next thing(s) to look for? Looking for a generic answer without having to ...
0
votes
0answers
42 views

Why is for some sessions count of deallocated pages in tempdb bigger than allocated?

Why is for some sessions count of deallocated pages in tempdb bigger than allocated? I used query select session_id, user_objects_alloc_page_count, user_objects_dealloc_page_count, ...
1
vote
1answer
41 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 ...
2
votes
1answer
63 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: ...
3
votes
2answers
79 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 ...
0
votes
1answer
27 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 ...
1
vote
0answers
51 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 ...
0
votes
2answers
54 views

SQL Server Standard Edition CPU + Tempdb Configuration

I have SQL 2014 sp3 (Standard) With below CPU Details according to 8 CPU we have configured 8 Tempdb datafiles. Based on my configuration could you please suggest what should be the CPU & Tempdb ...
0
votes
1answer
34 views

SQL Server 2016 TempDB physical file size is different than SQL System Catalog And Proportional Fill Algorithm is not working as expected

When I see size of file physically on drive, it is different for one of the tempdb secondary data file when compared to size mentioned in system catalog. What can the reason for it? In tempdb ...
34
votes
2answers
4k views

Is it generally faster to select into a temp table than selecting into an actual table?

I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity? I thought I recall it saying something special about tempdb and ...
1
vote
1answer
54 views

65G SQL Server database has 5.5G of data in tempdb when not in use

I have a SQL Server instance with 1 production database (also ReportServer dbs) that is 65G. tempdb is 5.5G (well, actually much bigger but 5.5G is in use) when server is not in use. I believe this ...
0
votes
1answer
212 views

Failed attempt to fetch logical page - tempdb [duplicate]

At least 2 times a week I get an error as the tempdb should be corrupt. Failed attempt to fetch logical page xxxx in database 2. This on all my 8 SQL Servers 2008 R2. The strange thing is that ...
2
votes
0answers
95 views

Test if I have too many TempDB Files

I know there are ways to test for tempdb contention to see if you need to add more tempdb files, but how can I test (and maybe prove) if I have too many tempdb files? I've inherited a relatively ...
2
votes
1answer
108 views

Not able to regulate/auto grow tempdb size

We have a SQL Server 2017 (RTM-CU15) AG group (3 servers, 2 HA synchronous) that has had performance issues for the past two months. During the resolution of an issue yesterday we found that the ...
0
votes
4answers
517 views

Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database

Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database ?. Please tell me if it has bad effect on a production database. My database ...
0
votes
0answers
24 views

How does DBCC CHECKDB use tempdb?

I would like to know the working principle of DBCC regarding the tempdb. I want to know why it uses tempdb for what purpose. I didn't find any advanced article about that so maybe someone can shortly ...
2
votes
2answers
380 views

Cannot connect to my SQL Management studio after moving a tempDB

In quite a bad position here. This afternoon on my tempDB I ran the query: sp_helpfile to locate where my MDF and LOG file were. My MDF was on my C: drive and my LOG file was on a D: drive so I've ...
0
votes
0answers
134 views

Operator used tempdb to spill data, spill level 1

Here I have a query which is spilling to tempdb. I understand that the cardinality estimates are wrong and causing this spill. I've read few articles that mentions updating the statistics and ...
2
votes
2answers
2k views

Best approach for moving tempdb .ndf files

During a SQL Server installation, I "thought" I specified location of tempdb on 'F:' and not 'C:', but I can see that although tempdb.mdf and templog.ldf are on 'F:', the 3 .ndf secondary data files ...
2
votes
0answers
68 views

Same temp table has created by different session and occurs blocking issue. how to avoid it?

It's a SQL Server 2012 EE. One session (SID=90) executed a JOIN query, and it's generated a temp table internally by SQL Server. But another sessions (SID=103, 109, 110,116) from totally different ...
1
vote
0answers
45 views

Tempdb. There is no enough space on the disc encountered

Does anyone know what really happened here? All of the sudden database was not accessible. How to find transactions that cause tempdb filled up? Seems like it happened right after SQL profiler ...
11
votes
4answers
2k views

Why would shrinking TEMPDB log lead to slowness?

I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?
0
votes
0answers
38 views

When does setting the SORT_IN_TEMPDB option actually improve performance? [duplicate]

My question is related to index rebuilding, mainly the SORT_IN_TEMPDB option. BOL states that: Even if other users are using the database and accessing separate disk addresses, the overall ...
2
votes
1answer
197 views

Does increasing the tempdb file size require a restart

I want to alter tempdb as such (i am increasing the size) USE [master] GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 10240KB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'...
1
vote
2answers
249 views

Appropriate tempdb size?

DBCC CHECKDB is failing on a particular database because of Could not allocate space for object 'dbo.SORT temporary run storage: 143479689576448' in database 'tempdb' because the 'PRIMARY' ...
2
votes
1answer
205 views

Is splitting tempdb files across drives necessary?

If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful? I want to persuade my manager to put all 4 tempdb files on one drive. If one of those tempdb ...
0
votes
1answer
420 views

How to exclude tempdb from sp_MSforeachdb?

Below I have a code to get info about t-log backups for each database in the server. I want to exclude tempdb from this list. exec sp_MSforeachdb 'SELECT server_name, sysdb.name AS DatabaseName, bkup....
0
votes
1answer
145 views

Enabling write caching for TempDb data drives

Recently, we had issues with slowness for some queries where TempDb latencies on their respective drives had spikes. We already have 8 tempdb files and trace flags 1117 and 1118 are not enabled. We ...
12
votes
2answers
640 views

What is hot spotting in the context of adding files to tempdb?

I'm trying to find out whether it is possible to add tempdb files to a SQL Server without having to restart the SQL Server service. I saw this answer here on Database Administrators: Tempdb Add files ...
16
votes
2answers
2k views

What happens when there is no available physical memory left for SQL Server?

While googling I found some conflicting information. Some sites state that when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB (see: SQL Server:...
0
votes
1answer
25 views

tempdb move issue

In preparation for moving many of my databases in a data warehouse to READ COMMITTED SNAPSHOT isolation level (CDC and Reads contend for locks) I added a drive and moved my tempdb there (mdf and ldf) ...
1
vote
2answers
391 views

Need to move TempDB to another drive. But its files cannot be dropped or shrunk

Today I had a task to move all TempDB files to a new, smaller drive (RAMdisk). I analysed TempDB files space usage - not all the space inside of its files was used so I decided that new drive is ok. ...
1
vote
1answer
584 views

TempDB Transaction log not releasing space

Every time I complete a transaction in tempdb, the temp log grows, however the log doesn't seem to release the space once the transaction has completed.The log usage percent is 43.4% but seems to ...
0
votes
1answer
128 views

TempDB file overflow

This is my experience with TempDB. The Algorithm works better when the TempDB File sizes are the same size. We were seeing heavy TempDB contention on a TempDB file that was larger than the others. We ...
0
votes
0answers
71 views

Setting up TempDB for more than one instance of SQL Server

I've been given the task of transferring the contents of a SQL Server instance from one Windows server to another Windows Server. One of the many things I need to do on the new server is set up the ...
1
vote
1answer
988 views

SQL Server sys.dm_tran_active_transactions long running transaction worktable

today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no ...

1
2 3 4 5 6