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.

38 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
0answers
137 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 ...
4
votes
0answers
408 views

Adding more tempdb data files gives more write latency

I am facing problems with tempdb please help and provide your suggestions. Initially, I have found tempdb contention problem in my server (Sql server) by using Simple talk Script. By using that ...
4
votes
1answer
326 views

Service broker internal spid hogging tempdb

We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker ...
4
votes
1answer
135 views

CONVERT_IMPLICIT when inserting a Geography datatype into a Geography datatype column on Temp Table

@@Version Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) If I try to insert data into a geography datatype in a temp table I get a CONVERT_IMPLICIT, converting a Geography datatype to a ...
2
votes
0answers
106 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
0answers
72 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 ...
2
votes
0answers
215 views

Aggressive Tempdb spill process

We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures. Tested on SQL2016 and SQL2017 Enterprise (recent ...
2
votes
0answers
36 views

SAN Admin vs. DB Admin free allocated tempdb

I really want to understand. My SAN-Admin told me I have to make sure my TempDB does not grow to max hard disk space. I have 380 Gig on a dedicated disk, on a dedicated LUN, on a dedicated machine. My ...
2
votes
0answers
380 views

How to use variables in a PL/pgSQL function with dynamic queries?

...and when must I use EXECUTE ... quote_ident(variable)? Taking an input to the function as text and storing part as a variable: wtype := split_part(message, ' ', 4); realval := split_part(message,...
2
votes
0answers
345 views

tempdb objects creator wanted, bufferpool issue caused by tempdb objects

I have a number of tables in tempdb named like #A0030B77 #A01D908C #A06D1FCB #A0F72FB0 #A1614404 #A1EB53E9 #A205D8FE #A255683D How (if) can I get spid of the process which created them? These tables ...
1
vote
0answers
60 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 ...
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 ...
1
vote
0answers
46 views

Restricting users to execute queries that bloat up tempdb

we have many a times faced a scenario where bad queries using tempdb just bloats up this system database filling 700 GB of tempdb in like couple of hours. I know there is a scope to tune them up. But ...
1
vote
0answers
1k views

SQL Server: Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'

I've just put in a request for our storage engineers to add more physical space for the LUN with tempdb. In this case, it was definitely needed. However, I'm disturbed by the SCOM report which flagged ...
1
vote
1answer
175 views

Filestream directory exists for a partition but the corresponding partition does not exist in the database

I am investigating a high memory usage issue of tempdb on a production SQL Server 2017 Enterprise (64-bit) deployment. This is similar to an issue discussed here. The server has two production DBs ...
1
vote
0answers
594 views

SQL TempDB PCI-E NVMe card - 4K or 64K

Our primary OLTP workload is tempdb heavy (both reads & writes) based on virtual_file_stats DMV, and based on SPs all of which heavily use #tmp tables. The server has 400GB of memory, more than ...
1
vote
0answers
333 views

Sys.dm_io_virtual_file_stats shows high latency for tempdb writes

We just got a new SAN and I was looking at sys.dm_io_virtual_file_stats to make sure we are seeing an improvement compared to our last SAN. I noticed that all the tempdb files are seeing avg write ...
1
vote
0answers
579 views

Time-out tempdb

When the job is running, it takes a time-out error. Usually it works, sometimes get any errors. Job schedule : Occurs every day 3 minute Time-out occurred while waiting for buffer latch type 3 for ...
1
vote
1answer
59 views

IDERA suggests to alter procedures in tempdb

Idera analysis is suggesting I: USE [tempdb] GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO ALTER PROC ##zDP_235302... Is this really a good idea? Most of the "fixes" their diagnostic software ...
1
vote
0answers
74 views

Using TempDB in SP causes to compile plan PER CONNECTION

uses sp_blitzcache by Brent Ozar SQL SERVER 2012 (11.0.6020.0) Steps to reproduce: create table b (b int) create table #b (b int) create procedure Y as begin insert into b select 1 select * from b ...
1
vote
0answers
985 views

SQL Server tempdb high IO

Using SQL Server 2016 Enterprise, having just recently upgraded from 2008 R2. The server has 192GB of memory, 24 logical codes, and is running around 30 databases with a total size of around 500GB. ...
1
vote
0answers
76 views

tempdb contention issue and excessive pagewrites/s

Still removing the rust from my SQL Server skills, so please bear with some stupid questions here and there... Customer has a SQL Server 2008 R2 running in an Azure VM with 8 cores and 14GB RAM. ...
1
vote
1answer
85 views

Remove Local RAID Drive from MS SQL Windows Cluster

We recently stood up a production SQL Server 2014 Enterprise on a two-node Windows Failover cluster (Active/Passive). We had some SSD's laying around and decided to use them for the tempdb for one of ...
0
votes
1answer
33 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
40 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
0answers
31 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% ...
0
votes
0answers
20 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
43 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, ...
0
votes
1answer
30 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 ...
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 ...
0
votes
0answers
25 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 ...
0
votes
0answers
143 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 ...
0
votes
1answer
26 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) ...
0
votes
0answers
75 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 ...
0
votes
0answers
34 views

SQL Server tempDB data file is growing very fast. 120gb

My tempDB is now over 120GB. It has grown 65GB in a couple of hours. It is not the log but the actual DB that is growing. Reports show 99% is unused space. I tried restarting the server that SQL ...
0
votes
0answers
2k views

Unexplainable write latency tempdb

I have a virtual SQL Server 2016 SP2 CU2 with SAN storage. TempDB has 8 files. I know it is recommended to put TempDB on SSD but TempDB is hardly used so spinning disks are cheaper. I've noticed that ...
0
votes
0answers
320 views

Moving tempdb from shared to local drive in a failover cluster

We have an existing failover cluster that I would like to have the tempdb in local drive. I'm not sure how to proceed. My thought process is below: Create two drives on each node one for tempdb and ...
0
votes
1answer
89 views

Increasing tempdb data files on 2 node Availability Group

I am planning to create additional tempdb files for 2 nodes in a SQL 2012 Availability Group. Unfortunately we do not have Instant File Initialisation turned on so the growth of the tempdb data files ...