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

15
votes
2answers
20k 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 ...
11
votes
3answers
3k 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
1answer
3k 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
2answers
8k 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?
7
votes
1answer
101 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
1k 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
2answers
2k 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
162 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
1answer
96 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
442 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
2k 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
283 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
1k 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
160 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
286 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
2answers
5k 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
3answers
5k 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 ...
4
votes
2answers
3k 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 ...
4
votes
3answers
511 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
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
5k 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
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
3k 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
1answer
333 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
2answers
120 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
1answer
271 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
1answer
8k 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 ...
2
votes
1answer
755 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
853 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
351 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
2answers
435 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 ...
2
votes
1answer
786 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 ...
2
votes
2answers
2k 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 ...
2
votes
3answers
1k 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 ...
2
votes
1answer
56 views

Why - read but not update ops fail in case of lacking space in tempdb database?

MSDN online article "Choosing Row Versioning-based Isolation Levels" states: "tempdb must have enough disk space for the version store. If there are very long-running transactions, all the ...
2
votes
2answers
174 views

Moving one TempdB on 3 instance server

I have a large virtual SQL Server (Full 2008R2). I run 3 SQL instances and would like to relocate the TempdB database file to another location, splitting off from the TempdB log file. The trouble I ...
2
votes
1answer
219 views

Is it normal for tempdb TPS to be higher than all other databases combined?

I'm investigating some performance issues on a production DB here, and I've noticed a slightly odd thing that I'm not sure is weird or not. If I run a quick Performance Monitor trace for the ...
2
votes
1answer
223 views

sys.syshobtcolumns Help

Does anyone have a good link to documentation regarding how the syshobtcolumns and syshobt system tables are populated. The reason for asking is that we are receiving pagelatch_sh and pageliolatch_ex ...
2
votes
1answer
27 views

Moving a SQL Server TempDB of AlwaysOn members

We have two SQL Server instances on Azure Windows virtual machines. Always on availability group is configured between them. We need to move TempDB on both instances, is there a risk that something ...
2
votes
1answer
70 views

TempDB not selectable for DBCC maintenance plan

I'm trying to clean up some of our maintenance plans and noticed that our TempDB is not getting it's weekly dose of DBCC CHECKDB goodness. I've tried to go into the maintenance plan and make sure ...
2
votes
1answer
996 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 ...
2
votes
1answer
119 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 ...
2
votes
1answer
270 views

TempInsertStateItemShort procedure on ASPState is taking more than 1 second to execute

I'm working on a .NET website who runs in load balance within more than one server and because of that I need to have my session on SQL Server. In the last 2 weeks I've notice that the procedure ...
2
votes
1answer
16 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, ...
2
votes
2answers
167 views

Where to place tempdb and logfiles

I have fallowing configuration for my SQL 2014 server: SSD RAID 1 - for data files SSD RAID 1 - for tempdb? logfiles? HDD (SAS 15K) RAID 1 - for tempdb? logfiles? I noticed that sql uses tempdb ...
2
votes
0answers
357 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 ...
2
votes
0answers
609 views

TempDB MDF file huge after update script ran

On the weekend we had a huge deployment run that affected a large chunk of our system. It was all run in one massive transaction (probably the wrong thing to do). As a result our TempDB DatabaseFile ...
2
votes
0answers
19 views

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

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 ...
2
votes
0answers
216 views

tempdb objects creator wanted, bufferpool issue caused by tempdb objects

I have a number of tables in tempdb named like #A0030B77 #A01D908C #A06D1FCB #A0F72FB0 #A1614404 #A1EB53E9 #A205D8FE #A255683D How (if) can I get spid of the process which created them? These ...
1
vote
2answers
147 views

SQL 2008 RTM Temp Tables

Today I implemented a monitoring solution on our SQL server using Cacti and the first thing i noticed is the growth in the number of temporary tables, in just 3 hours on our test system we had an ...