Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

336 questions
81
votes
4 answers

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
prasanth
  • 1,033
  • 2
  • 9
  • 12
37
votes
2 answers

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 storing the data in memory?
J.D.
  • 40,776
  • 12
  • 62
  • 141
32
votes
3 answers

How to find the SQL statements that caused tempdb growth?

The tempdb of a server (SQL Server 2008) increases to 500GB+ several times every month. Is it possible to find out which SQL statements caused this problem? The problem is usually not caused by create table #temp...; insert into #temp... or select…
u23432534
  • 1,565
  • 4
  • 20
  • 31
31
votes
2 answers

Best practice to shrink Tempdb in a production environment

What is best practice to use when shrinking Temporary db in SQL Server 2008? Is it risky to use the following? use tempdb GO DBCC FREEPROCCACHE -- clean cache DBCC DROPCLEANBUFFERS -- clean buffers DBCC FREESYSTEMCACHE ('ALL') -- clean system…
Mcol
  • 459
  • 1
  • 4
  • 4
22
votes
3 answers

Safely moving and creating new tempdb files

Two things I'd like to know: how do you safely move tempdb with minimal downtime? how many tempdb files do you need? Is it 1 file per core? So quad-core = 4 tempdb files, creating three new ones?
Stuart Blackler
  • 4,540
  • 7
  • 30
  • 43
20
votes
2 answers

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 memory usage, but generally it keeps growing it's…
SQLing4ever
  • 488
  • 4
  • 8
18
votes
2 answers

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session is holding which temp table? Ideally a query…
SQLMIKE
  • 437
  • 1
  • 5
  • 10
17
votes
2 answers

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: Demystifying TempDb and recommendations). But…
Rauf Asadov
  • 1,313
  • 14
  • 36
15
votes
1 answer

Sort spills to tempdb but estimated rows equals to actual rows

On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb. When you take a look at the query plan…
Frederik Vanderhaegen
  • 2,122
  • 1
  • 17
  • 36
15
votes
2 answers

SQL Server tempdb on RAM Disk?

Our vendor application database is very TempDB intensive. The server is virtual (VMWare) with 40 cores and 768GB RAM, running SQL 2012 Enterprise SP3. All databases including TempDB are on Tier 1 SSD in SAN. We have 10 tempdb data files, each…
d-_-b
  • 1,184
  • 1
  • 12
  • 25
14
votes
1 answer

What frequency of hash/sort spills into tempdb is concerning?

Our enterprise application uses SQL Server for data storage and is primarily an OLTP system. However, an important component of our application generates a significant OLAP workload. Our write latency to tempdb is about 100ms. This trend holds over…
Matthew Rodatus
  • 243
  • 2
  • 5
14
votes
4 answers

System disk run out of space when running heavy SQL queries on SQL Server 2012

I'm quite new to SQL Server 2012, I would be grateful if someone can help. I have restored a copy of a huge database to SQL Server 2012 and I tried to run some simple queries against it. I'm trying to run a SELECT query against a database table of…
royv
  • 397
  • 1
  • 4
  • 7
14
votes
1 answer

TempDB contentions

We have an active OLTP 40GB database on SQL Server 2014 SP1. Queries are found to be slow with IO_Completion waits, Disk Queue Length rising to 900, and SQL Server stops responding. What we tried: Restart instance and with in a minute it start…
Aasim Abdullah
  • 2,975
  • 4
  • 25
  • 40
13
votes
1 answer

Why does truncating a temp table at the end of the stored procedure that creates it free tempdb space faster?

SQL Server caches temp tables created within stored procedures and merely renames them when the procedure ends and is subsequently executed. My question has to do with when the tempdb space is released. I've read that the table is truncated at the…
Mark Freeman
  • 2,293
  • 5
  • 32
  • 54
13
votes
4 answers

Allocate space for object 'dbo.SORT temporary run storage

DBCC CHECKDB returns: Could not allocate space for object 'dbo.SORT temporary run storage: 140737951236096' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the…
LeraningDBA
  • 331
  • 1
  • 3
  • 16
1
2 3
…
22 23