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

27
votes
2answers
62k 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.
17
votes
2answers
29k 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
7k 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 ...
9
votes
2answers
12k 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?
9
votes
1answer
4k views

SQL Server TempDB behaviour in large memory environment

Reading over In-memory SQL Server log file reminded me of a question I had a short while ago. We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of ...
8
votes
3answers
164 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
9k 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 ...
7
votes
1answer
311 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
134 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?
6
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 ...
6
votes
1answer
212 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
3k 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:? ...
6
votes
1answer
133 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
822 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 ...
6
votes
2answers
7k 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 & one log file. They are all located on the same dedicated drive. When I right click on tempdb in SQL Server Management ...
5
votes
2answers
6k 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 ...
5
votes
2answers
6k 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
1answer
527 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
2k 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
718 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
215 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 ...
5
votes
1answer
52 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
1answer
330 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
1answer
18k 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 ...
5
votes
0answers
63 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 ...
4
votes
3answers
544 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
1answer
102 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
10k 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
107 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
177 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
1k 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 ...
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
721 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
3answers
4k 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 ...
3
votes
1answer
486 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
412 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
161 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
2answers
2k 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 ...
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
56 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 ...
3
votes
1answer
53 views

When are the unnecessary row versions being removed from the version store

Sorry for my bad English :) I need a help with understanding version store`s cleaning. I have the following situation. 12:00 Start a select statement under the snapshot isolation level and not ...
3
votes
1answer
158 views

PAGELATCH_EX on 2:1:128

We are having problems on SQL Server 2012 with contention on tempdb page 2:1:128, which seems to be related with system table sysobjvalues. We have about 400 sessions waiting on this resourse ...
3
votes
1answer
66 views

Suspect (Torn) Pages recorded in TEMPDB? (SQL Server 2005 SP3)

I have a virtualised SQL Server 2005 (SP3) server that reported the following msg: Database_Name, Database_Id, File_Id, Event_Type, Event_Type_Desc, Error_Count, Last_Update_Date tempdb, 2, 1, 3, ...
3
votes
2answers
6k 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. ...
2
votes
1answer
913 views

Why does an AS cube rebuild use so much TempDB?

I have a 50GB SQL 2008 database (only 36 of that is data) with a 24GB Analysis Server cube that rebuilds nightly. The cube rebuilds have been using more and more of the TempDB on the SQL server each ...
2
votes
2answers
1k views

Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008

If I give all RAM to SQL Server, it will use it's own pattern to determine what RAM to allocate for what. If I allocate portion of RAM as RAM Disk and put tempdb into that disk it will force RAM ...
2
votes
2answers
387 views

How to quantify TEMPDB usage in relation to other databases hosted on same hardware?

Working on a project to decide if configuration 1 or 2 is the best way to organise disks for my SAN: Config1 (sql 2008): 8 disks @ raid 10 data 2 disks @ raid 1 logs 2 disks @ raid 1 tempdb 2 disks ...
2
votes
1answer
1k views

TempDB on Solid State Drives on SQL Server Cluster

Can solid state drives be used for the TempDB on a SQL Server failover cluster if they are local drive on both machines? It is supported in 2012 but our cluster is a SQL 2005. The application that it ...