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.

learn more… | top users | synonyms

30
votes
3answers
74k 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.
20
votes
2answers
34k 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 ...
12
votes
3answers
9k views

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 ...
11
votes
1answer
4k views

SQL Server TempDB behaviour in large memory environment

Reading over this question reminded me of a question I had a while ago. We have a SQL Server that has 512GB of RAM, the main database is 450 GB. We see quite a lot of action in TempDB (ok, I think ...
10
votes
2answers
15k views

Safely moving and creating new tempdb files

Two things I'd like to know, how do you safely move tempdb with minimal downtime? Also, how do you create new tempdb files? Is it 1 per core? So quad-core = 4 tempdb files, creating three new ones?
10
votes
1answer
153 views

Does CREATE TABLE SomeSchema.#TempTableName have a bug?

Simple test-bed: USE tempdb; GO /* This DROP TABLE should not be necessary, since the DROP SCHEMA should drop the table if it is contained within the schema, as I'd expect it to be. */ ...
8
votes
3answers
216 views

IF statement not skipping TempDB when Looping through Databases with sp_MSForEachDB

[SQL Server 2012 SP2 EE] Why does the following script gives me an error relating to tempdb? exec sp_MSForEachDB ' IF ( (select database_id from sys.databases where name = ''?'') > 4) ...
8
votes
3answers
11k views

Best practice for tempdb log file

I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file. Here's the strategy that I'm presently using with my ...
8
votes
1answer
31k views

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 ...
8
votes
1answer
548 views

Very high transactions per second

Our production server runs on average 4,000 transactions per second. In the past few days the average has jumped to 175,000 transactions per second. That is not a typo, it's 175K per sec. Looking at ...
7
votes
4answers
920 views

DDL contention on TempDB

I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
7
votes
2answers
7k 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 ...
7
votes
1answer
319 views

Sudden increase in Page Writes/s after patching / rebooting

I was given a SQL Server 2008R2 to run, but my DBA skills are rusty after some years away from the day-to-day ops, so please bear with my (somewhat) silly questions. This SQL Server is running in ...
7
votes
1answer
3k views

Broken tempdb location and can't recover

Made a mistake and mistyped an alter database command for tempdb. Now the instance won't start. I can't start in single user mode using -m as it states the tempdb could not be found. I tried using: ...
7
votes
2answers
2k 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 ...
7
votes
2answers
4k views

tempdb logs on same drive as tempdb data or with other logs? [duplicate]

For many reasons I only have 3 hard drives (RAIDed and in an Always-On AG) for all my database files: D: Data E: Logs F: Tempdb Should the tempdb log file go on F: with the data file(s) or on E:? ...
7
votes
1answer
156 views

TEMPDB Log Files [duplicate]

Do I want to store the tempdb log files on the same drive as my user database log files, the same drive as the tempdb data file, or on a separate drive?
7
votes
3answers
8k 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
1answer
246 views

Does tempDB disk perf matter if you have plenty of unused memory?

We have a SQL Server 2012 Enterprise setup with a large amount of RAM (256 GB), and at this time fairly small databases (about 17GB). Right now we are experiencing performance issues due to various ...
6
votes
2answers
137 views

sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table

I think I need some help as I run out of ideas. In my database I have a simple table like this: CREATE TABLE dbo.Invoices ( CashDiscounts MONEY, Cost MONEY ) GO and a stored ...
6
votes
1answer
591 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 ...
6
votes
1answer
144 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
2answers
960 views

Major Discrepancy Between SQLIO IOPS/Throughput Stats and DBCC CHECKDB Operations

I'm conducting time trials on a new SSD array running both SQLIO tests and a real-world workload of DB restores and DBCC CHECKDB calls. I'm seeing a major discrepancy between the IOPS and throughput ...
5
votes
2answers
8k 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 ...
5
votes
2answers
6k 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 ...
5
votes
1answer
653 views

Dropping large clustered primary key fills up tempdb

Due to one of our tables growing outside of the range of an INT, something that wasn't predicted when the DB was produced, we're need to change the PK field to a BIGINT instead. We are however ...
5
votes
1answer
3k 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 ...
5
votes
1answer
873 views

SQL Server 2012 Local SSD TempDB Slow

I have a production deployment using local SSDs for tempDB. I have 2 SSDs in a RAID1 configuration. I am seeing average reads of 1-2ms but the average writes are showing as 1377ms on all four of my ...
5
votes
1answer
86 views

How can I minimize impact of expensive queries in Database A on Databases B, C, D

How can I configure SQL Server so that activity in 'Database A" has minimal impact on users in 'Database B' The Context Our (very large) production SQL Servers have a lot of databases on them ...
5
votes
2answers
10k views

TempDB Log Space and ACTIVE_TRANSACTION

Our monitoring solution (SCOM) is currently flagging that the tempdb log is running out of space. However we have auto grow set to 1GB chunks for the log and we have 25GB of space left on the drive. ...
5
votes
1answer
345 views

When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?

Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed) I have a table that's got 19 ...
5
votes
0answers
66 views

How to solve blocking on tempdb

I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2). Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all ...
4
votes
3answers
6k 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 ...
4
votes
3answers
563 views

Creating new TempDB files

I would like advice on what would be the best setup for our SQL Server 2005 TempDB. At the moment our Temp DB consists of a single MDF file of 3 GB and a single log file of 120 MB on the D: drive of ...
4
votes
2answers
3k 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 ...
4
votes
1answer
187 views

Tempdb change settings and recommendation

In my production database I have below configuration in TempDb and needless to say I am facing a lot of IO issues on the drive . So, as per recommendation I am planning to change it Change #1 ...
4
votes
1answer
2k views

823 errors on tempdb

Have a couple Fiber channel SAN-attached cluster servers which have been intermittently reporting errors like: 2012-05-09 16:31:54.13 spid74 Error: 823, Severity: 24, State: 2. 2012-05-09 ...
4
votes
2answers
6k views

SQL Server 2008 R2 - Set tempDB size on full disk

One of my SQL Server 2008 R2 instances had a job that went mad last night, ended up with the job auto-growing tempDB until it ran out of disk space. To compound the issue I noticed that tempDB is ...
4
votes
1answer
11k views

TempDB data files don't shrink well on SQL 2008

So ideally you want to pre-size your TempDB data and log files appropriately so that this isn't a concern, but sometimes a rogue developer runs a crazy huge query on a production server during work ...
4
votes
1answer
186 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 ...
4
votes
1answer
193 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 ...
4
votes
2answers
2k views

Objects in Tempdb that are not associated with a session

I have a SQL Server 2005 instance where my tempdb i apparently holding on to objects that are not in use anymore. The database has 24gb total and is apparently unable to go below 8gb in use. For ...
4
votes
1answer
317 views

MS SQL high tempdb disk I/O during merge replication of BLOBs

Having a merge publication for replicating BLOBs (sql type - image), got very high tempdb disk I/O for my size of data. Publication is download-only and has no filters. High disk I/O is caused by ...
3
votes
2answers
4k views

SQL Server 2005 tempdb Time out occurred while waiting for buffer latch type 4 (pagenum)

Three separate processes have failed in the last two days with very similar errors: Time-out occurred while waiting for buffer latch type 4 for page (1:1189832), database ID 2. Time-out occurred ...
3
votes
2answers
842 views

What are the options of reducing tempdb latency?

Took the job of monitoring and tununing a SQL Servers 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed. All of them are running inside virtual machines. One SQL Server per a ...
3
votes
1answer
546 views

How to determine cause of runtime increase given two query plans with SpillToTempDb warning

Two actual query plans were captured for two executions of one query: Plan "Fast" took ~1s and occurs about 90% of the time. Plan "Slow" took ~16s. Since the graphical plans look ...
3
votes
1answer
504 views

Tempdb data file disappearing

I recently moved my Temp database files onto a separate drive (I had two data files and one log file). However, after restarting the server, my second data file disappeared both from the file list on ...
3
votes
2answers
180 views

Post mortem on tempdb transaction log after instance restart?

TempDB transaction log filled up and I had to restart SQL Server as a result. After the restart, is there any way to see what caused the transaction log to fill up in the first place?
3
votes
4answers
2k 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 ...
3
votes
1answer
69 views

DBCC CHECKDB Estimation Alternative

I am in the process of rolling out DBCC CHECKDB across our instances. Each database will eventually either have a DBCC CHECKDB or DBCC CHECKDB PHYSICAL_ONLY. The way I have been gauging whether a ...