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
67
votes
4answers
167k views

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.
26
votes
2answers
59k views

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 ...
14
votes
2answers
25k views

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 ...
6
votes
1answer
9k views

Find transactions that are filling up the version store

we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases. Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be ...
3
votes
1answer
2k views

how to find out what process(es) caused the autogrowth of a database or a database file?

I have a process(es) hungry for tempdb, but I am struggling to identify this process. any ways I could achieve this? We have been alerted as Tempdb has now grown into the space you reclaimed on ...
6
votes
2answers
13k views

Issues with TempDB mdf file ever increasing

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and then ...
4
votes
1answer
3k views

Tempdb Add files require Restart

Does adding Tempdb files require a restart? We would like to Add files on the same drive, Not Delete and Not modify. We tested in Dev and QA environment, restart was not required. However for ...
9
votes
3answers
8k views

How might a corrupt partition in TempDB result in DBCC CHECKDB reporting no issue?

One of our SQL Servers reported the following error recently: DATE/TIME: 2/25/2013 9:15:14 PM DESCRIPTION: No catalog entry found for partition ID 9079262474267394048 in database 2. The ...
1
vote
1answer
3k views

Why does FREEPROCCACHE let you shrink tempdb?

Somebody did something stupid and tempdb exploded in size. Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked). ...
8
votes
2answers
10k views

Splitting TempDB into multiples files equal to number of CPUs

The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files. By having ...
22
votes
3answers
25k views

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?
9
votes
2answers
18k views

TempDB will not shrink. No Open Transactions

I have a TempDB on SQL 2008 that has gotten very large (>40gb) and I want to shrink it down. I have used the dbcc shrinkdatabase, dbcc shrinkfile and the shrink command through Management Studio. I ...
6
votes
3answers
10k views

What are the most effective techniques to reduce SQL Server disk space use when updating all rows in a very large table?

Given a SQL Server table with a large number of rows no columns with large-value data types multiple indexes more allocated space than available for the largest possible transaction log size a single-...
4
votes
2answers
10k views

Tempdb autogrowth settings

Following on from a recent question that I asked on Tempdb. I was wondering how to correctly set the autogrowth property when adding and moving the tempdb files? I ask because I would like to let SQL ...
6
votes
1answer
256 views

Does RCSI use less or same space than SNAPSHOT? Why?

Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server... MSDN online article "Choosing Row Versioning-based ...
6
votes
1answer
1k views

IO request time and less write latency

I am facing issues related to constantly growing log file due to which I am getting error. When I checked SQL Log I found below messages (error log is filled of these messages almost 90%) SQL ...
11
votes
2answers
8k views

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 ...
10
votes
3answers
530 views

Sort spills to tempdb due to varchar(max)

On a server with 32GB we are running SQL Server 2014 SP2 with a max memory of 25GB we have two tables, here you find a simplified structure of both tables: CREATE TABLE [dbo].[Settings]( [id] [...
10
votes
1answer
5k views

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 ...
7
votes
2answers
6k views

what is the best practice for tempdb autogrowth?

I have a client's server with about 15 databases on this instance with a data total of about 100 GB. One of the databases is for a sysaid app. his temp db files are one mdf one ndf and of course an ...
7
votes
2answers
7k views

Should I check for existence of temp tables in Stored Procedures?

Is there any edge case in which it would be recommended to explicitly check for ,drop and create temp tables at the start of a Stored Procedure instead of just creating them? Similarly, is there a ...
8
votes
2answers
602 views

SQL Server tempdb on SSD showing IO

We recently separated out our tempdb files to a new SSD and have started seeing: 5348 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\tempdb\tempdb4.ndf] . We ...
7
votes
2answers
8k views

TempDB Version Store cleanup

From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all ...
6
votes
3answers
2k views

Recommendations for more than one tempdb file

I would like to split tempdb into two files with same size, to improve performance of the database. I wonder if these two data files can be on the same disk, separately from the logs and users data ...
2
votes
1answer
4k views

How to find which query is causing tempdb growing? [duplicate]

Good day. I'm trying to discover which query is causing tempdb growing too much (around 100gbyte). In my new company, people is using a lot of dynamic Excel files with various query. I'm pretty ...
7
votes
3answers
10k views

SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative value

Please help as this issue is driving me nuts... Tempdb has four data files and one log file. They are all located on the same dedicated drive. When I right click on tempdb in SQL Server Management ...
6
votes
3answers
27k views

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 ...
4
votes
4answers
5k views

tempdb in sql server

Using SQL Server 2000, I am getting the following error. The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space." How to solve it? I am ...
4
votes
1answer
584 views

Splitting SQL Server tempdb to use a second drive when first drive is full

My SQL Server instance has a small 32 GB SSD which is mostly unusedw while the remaining drives are SAN storage. I considered moving my tempdb to the SSD but my fear is the odd occasion when the ...
3
votes
3answers
4k views

if block fails to create temp table in a procedure

I'm attempting to do this in a procedure: DECLARE @a bit = 1; BEGIN SELECT * INTO #aTemp FROM OPENROWSET( ... ); IF @a = 0 BEGIN SELECT ... INTO #bTemp FROM #aTemp; END ...
1
vote
1answer
458 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
vote
1answer
5k views

'Logical file name “MyDB” is already in use' Even though the database doesn't exist [closed]

I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the ...
0
votes
1answer
584 views

Risks associated with automatically shrinking TempDB as a SQL agent job?

I have a situation where I have a constraint on TempDB to not exceed 350 GB in size. I'm using autogrowth 10 (percent) on TempDB, as I've read that it is supposed to be best practice on databases ...
-4
votes
2answers
2k views

How to improve TempDb on RAMDisk showing performance on par with physical hard drive [closed]

Given 2 SQL Server Instances where the second instance is configured with a RAMDisk for tempdb and the following test case. -- Create source data select top(1000000) * /* ~10 cols */ into #t1 from ...