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

0
votes
0answers
18 views

SQL Server 2005 templog.ldf hits max size [duplicate]

I have a SQL Server 2005 database, Win2008 R2 server. The max file size limit on the temp db has been 6GB for years, without issues. We heavily customize our database with custom code monthly. ...
2
votes
0answers
94 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
96 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 ...
0
votes
0answers
16 views

How to identify the creator of the temporary table in SQL Server 2005 [duplicate]

On my server many users performing complex queries using tempdb. Sometimes a failed specifying boundary values ​​obtained are very large tables. So I want to know who created this temporary table. ...
0
votes
0answers
44 views

SLEEP_TASK and PAGELATCH_XX peaking after upgrade to R2 SP1

I wanted to just add a comment to this question: SLEEP_TASK Wait Type in SQL Server - What does it indicate? But I'm having not only SLEEP_TASK wait but also PAGELATCH_SH and PAGELATCH_EX wait types. ...
2
votes
1answer
73 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 ...
1
vote
0answers
30 views

Performance Metrics for TempDB [duplicate]

I've been tasked with gathering performance metrics on a few database servers that have one TempDB data file. It seems prevailing wisdom is to have one TempDB data file per core up to a certain ...
6
votes
2answers
222 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
544 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 ...
2
votes
3answers
413 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 ...
0
votes
0answers
50 views

TempDB causing high IO writes - dependent on server's max memory setting [duplicate]

I have a mirrored SQL Server 2012 database. The first server has a hugh tempdb write IO load when the maximum memory limit is set above 8192 mb. These 8192 where the original setting for both the ...
3
votes
1answer
202 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 ...
1
vote
1answer
142 views

Different results from sys.dm_db_file_space_usage with database prefix

To make it simple: According to MSDN (link) among other sources, you can get the amount of current free space in tempdb by using the following bit of TSQL: -- MSDN version SELECT ...
1
vote
1answer
290 views

Does MAXDOP affect the number of tempdb data files I should create?

From what I have read, best practices for configuring tempdb includes creating multiple data files. The rule of thumb as I understand it is if you should create the same number of data files as you ...
6
votes
1answer
75 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?
2
votes
2answers
223 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 ...
0
votes
1answer
134 views

TEMPDB Round Robin is not working!! It is filled up

I have a 16 Core CPU processor and I have created 8 tempdb datafiles with initial size as 2,000 MB with 10% growth restricted to 10,000 MB. The log file is unrestricted and it is in simple recovery ...
6
votes
1answer
74 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 ...
2
votes
1answer
53 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 ...
3
votes
2answers
94 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?
5
votes
1answer
172 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 ...
0
votes
1answer
482 views

Create temporary tables per session in a DB other than tempdb

Using SQL Server, Is there a way to create a temporary table per user session in a user database in the same exact way they are created in tempdb? Using tempdb is fine in most cases, but some of the ...
1
vote
0answers
69 views

remote query bloats tempdb [closed]

I have someone who retrieves data from a linked server to a new table as following: * newly edited - turns out there's multiple queries executed * select * into dbDestination.dbo.tableDestination ...
1
vote
1answer
146 views

SQL Server 2008R2 Cluster: Spread tempdb over several files AND shrink original single file

On my SQL 2008 R2 active/passive cluster, I've received an error message regarding I/O requests on tempdb.mdf taking longer than 15seconds. Googled that and it as suggested I spread tempdb over ...
2
votes
1answer
556 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 ...
0
votes
1answer
116 views

Under what conditions does SQL Server encrypt tempdb?

When does tempdb get encrypted with Transparent Data Encryption? What configuration, etc, causes tempdb to be encrypted this way?
1
vote
1answer
188 views

Simple update query balloons tempdb

I am trying to do a bulk update of a column from a csv file, however when I do the actual update operation my tempdb balloons to over 20 GB in size (overtaking the entire size of the SSD the tempdb is ...
-3
votes
2answers
612 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 ...
6
votes
2answers
759 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 ...
2
votes
1answer
212 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
0answers
400 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 ...
4
votes
2answers
1k 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:? ...
3
votes
1answer
265 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 ...
5
votes
3answers
3k 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 ...
2
votes
2answers
128 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 ...
5
votes
1answer
244 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 ...
4
votes
2answers
824 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 ...
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 ...
5
votes
2answers
3k 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
1answer
967 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 ...
4
votes
2answers
2k 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 ...
1
vote
2answers
365 views

Should a SQL Server TempDB space be allowed to be reclaimed?

If I don't have space concerns at the moment, should I size my TempDB as a large space initially and not let it's space be reclaimed? What's the best way determine what the optimal initial size for ...
2
votes
0answers
193 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
327 views

Size of TempDB Microsoft SQL Server 2005

Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full. I am creating large temporary tables in a couple of queries. Once I am finished running queries, ...
1
vote
2answers
138 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 ...
2
votes
1answer
179 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 ...
1
vote
3answers
349 views

tempdb usage in SQL Server

I have a SELECT INTO statement writing to a database which is in SIMPLE recovery mode, and is not tempdb. Will this use tempdb at all? The SQL is something like USE TGTDB GO SELECT * INTO ...
9
votes
1answer
2k 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 ...
4
votes
3answers
471 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 ...
11
votes
3answers
2k 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 ...