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
1answer
19 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
votes
0answers
9 views

TempDB Log Reclaims TempDB Log Space

I see that when our TempDB Log hits 70% full, SQL Server starts a process to Clear the TempDB log. Where is the 70% full value set?
1
vote
0answers
37 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
0answers
31 views

Using sp_whoisactive, when should I be alarmed about a query?

As the title states, when should I be alarmed about a query that shows up when running sp_whoisactive? What do you look at? I know the answer usually is "It depends" but let's say you ran it and ...
0
votes
2answers
48 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
31 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 ...
33
votes
2answers
3k 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
47 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
87 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 ...
1
vote
0answers
56 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
87 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
214 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
23 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
139 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
96 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 ...
1
vote
2answers
431 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
59 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
44 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
31 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
87 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
100 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
120 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
171 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
93 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
614 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
192 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
281 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
94 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. ...
0
votes
0answers
52 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
456 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 ...
0
votes
0answers
30 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 ...
2
votes
2answers
104 views

A very strange thing in the tempdb [closed]

In all the following scenarios on my laptop, all the tables in the SSMS Tables folder will be deleted from tempdb: Instance Restart; Stop & Start SQL Server Service; End Task SQL Server process ...
1
vote
1answer
253 views

SQL Server tempdb log file isn't autogrowing, instead getting error “The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'” [closed]

FYI: moved from Stack Overflow https://stackoverflow.com/questions/56350491/sql-server-tempdb-log-file-isnt-autogrowing-instead-getting-error-the-transac I'm using SQL Server 2016 and am encountering ...
1
vote
1answer
210 views

Troubleshooting TempDB for Performance Bottlenecks

While trying to investigate some performance issues with our SQL Server database, I've noticed our wait statistics seem high for ASYNC_IO_COMPLETION and PAGEIOLATCH_*. In Resource Monitor I noticed ...
5
votes
3answers
204 views

“tempdb metadata memory-optimized” configuration option

Checking SQL Server 2019 CTP 2.5 (15.0.1500.28) I found this new configuration option and was wandering what is behind it. Couldn't find any information so far and was wondering if you know where to ...
3
votes
1answer
495 views

Log for database tempdb is not available

I have been struggling with this issue from so many months now, I am saying its not a database issue and assigning this case to Storage and Operating system team and they are assigning it to me ...
1
vote
1answer
434 views

In memory table vs temporary table - benefits if temdb in already on ram disk

In the docs, we have example of how temporary tables can be replaced with in-memory tables and because a memory-optimized table: is stored only in memory, and has no component on disk involves no IO ...
0
votes
0answers
41 views

My Temp db is full [duplicate]

What to do when tempdb is full ? I would like to know if tempdb has become full and what to do if you can't truncate the log? I want to understand the process why you can't truncate it.
1
vote
3answers
61 views

When I connect to DB, always connects as tempdb

I am trying to run a sybase DB query from a jsp session. I can connect to the DB fine, but when I run my query, by default it always runs against tempdb. Is there a way I can make a connection to ...
1
vote
0answers
191 views

How we can clear tempdb without restart MSSQL service [duplicate]

While checking my tempdb details, its showing that almost 90 GB is consumed. So I tried clearing all caches and perform shrinking, but still it's consuming 90 GB of space. My drive became full where ...
2
votes
0answers
166 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 ...
1
vote
1answer
525 views

Should TempDB SSD Drive be formatted with 64 Kb Allocation Unit Size?

We have installed dedicated SSD drives for TempDB First question - should these drives be formatted with 64 Kb allocation size ? Currently it shows 4 Kb (Bytes per Cluster = 4096) Does TempDB ...
1
vote
1answer
136 views

Understanding the TempDB growth

Need to understand below behavior of tempdb:- Due to some poor running queries which caused Tempdb bloating, we had shrink and tried to adjust the sizes of each tempdb file: Before issue: no of ...
2
votes
1answer
2k views

How to clear temp database content in SQL Server?

As the title implies, I am going to clear the content of my temp database in SQL Server. SolarWinds is generating a critical alert and I want to clear the tempdb workfiles and worktables. How can I ...
4
votes
1answer
129 views

SQL Server 2016 still uses mixed_extent_allocation in TempDB

Today I was troubleshooting a problem on TempDB on SQL Server 2016 SP2 using extended events and added the mixed_extent_allocation and transaction_log event to my trace. I expected ...
5
votes
3answers
1k views

How to inspect global temp table from outside the transaction it is created in

To help debugging a batch of SQL (that is run inside a transaction), inside the transaction I dump some data into a global temporary table. The global temporary table is created inside the transaction ...
3
votes
1answer
464 views

SQL Server 2016 with high tempdb usage and low RAM usage

I've got a small instance of SQL Server Standard that I'm running HammerDB TPC-H (1 GB) benchmarks on. It's got some configuration issue though where it's writing everything into tempdb on disk, and ...

1
2 3 4 5 6