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

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

ASPStateTempApplications and ASPStateTempSessions tables creation in tempdb after sql server restart

I have a implemented SQL server session state management. So created ASPState database and ASPStateTempApplications and ASPStateTempSessions tables in TEMPDB database. When I restart SQL Service, ...
0
votes
1answer
12 views

Tempdb tables, table in database or even cte for usp for reporting

I am about to start a project for SSRs report base on a USP. The procedure will have to use two cursors to compare orders v stock position (similar to a mrp run ): I am thinking of storing the data ...
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 ...
0
votes
2answers
60 views

Is it normal for tempdb to not give up disk space when it releases things?

So my tempdb is getting huge. Like 200+ GB. USE tempdb GO EXEC sp_spaceused database_name database_size unallocated space tempdb 211668.88 MB 206803.45 MB It's using a SIMPLE ...
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 ...
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 ...
1
vote
1answer
525 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. ...
1
vote
2answers
88 views

SQL Server TempDB Growth issue

We have an OLTP database (running SQL Server 2008 R2) that has 6 tempdb files. All of them have an Autogrowth by 10%, restricted growth to 10000mb. At one point, someone changed one of the growths to ...
0
votes
1answer
49 views

SSMS 2008 / 2014 showing different values for TempDB file sizes

Here's a fun one. Last week I reconfigured the initial file sizes in our tempdb database to 1GB each. We had an event a few weeks back that locked up our server due to a table with no indexes ...
1
vote
2answers
63 views

How Can I Read Where TempDB's Files Will Be Created After Restoring Master DB?

I'm restoring to an alternate server in a DR drill and have restored master but can't restart the instance, even with /f and /m switches. The log is indicating tempdb can't be created, probably ...
2
votes
1answer
1k 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 ...
0
votes
1answer
86 views

Number of TempDB files if we can't use parallelism

Currently we have 4 cores and 4 tempdb files. I just came on as the DBA about 2 months ago and, for some reason, 3 of the tempDB files are 2GB and 1 is 1GB. Obviously, that is not best practice. ...
0
votes
1answer
56 views

TempDB Query being Blocked

I have been monitoring Blocking issues on our SQL Server database. Very often the below query is running on tempdb and is blocked with LCK_M_S What is this query used for and why does it run ...
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 ...
1
vote
2answers
196 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 ...
0
votes
0answers
20 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
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 ...
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 ...
0
votes
0answers
17 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
102 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
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 ...
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
443 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 ...
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 ...
0
votes
0answers
52 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
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 ...
1
vote
1answer
242 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
558 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 ...
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?
2
votes
2answers
437 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
235 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
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 ...
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 ...
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?
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 ...
0
votes
1answer
887 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
91 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
175 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
787 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
190 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
266 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 ...
-2
votes
2answers
846 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
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 ...
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
0answers
610 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 ...
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:? ...
3
votes
1answer
334 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
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 ...