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
7 views

A transaction has been utilizing the version store for X minutes

For one of our servers we are getting below critical alert from Idera Diagnostic manager saying: A transaction has been utilizing the version store for 93 minutes on ABCKP1. The current version ...
7
votes
1answer
293 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 ...
1
vote
1answer
39 views

TempDB Best Practices For Shared Storage [closed]

Microsoft recommends separating the SQL Data Files from the Temp Files to limit device contention but what about cases involving shared storage. I have a shared environment where the data and temp ...
1
vote
1answer
62 views

Tempdb growing, however it has a lot of free space

On a sql server dedicated to a Dynamics CRM database, I've been seeing some odd behavior. While monitoring growth using the following query as basis, I've noticed tempdb has grown quite frequently ...
3
votes
1answer
75 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 ...
0
votes
0answers
14 views

tempdb Log Continuing to Fill, but No Long Running Transactions In Flight [duplicate]

I'm suspecting a report is generating a large data set with sorting in tempdb, causing the tempdb tlog to fill. I'm trying to identify the session that's generating the log growth but DBCC OPENTRAN is ...
0
votes
0answers
27 views

Sql Server 2014 AppRole not able to create temptable in tempdb

we have created Application Roles in sql server 2014 and grant the "Execute" permission on the tables and stored procedures. when i execute sp_setAppRole and then the stroredprocedure. the simple ...
1
vote
1answer
110 views

Tempdb files don't exist

I have a strange behaviour on one of my SQL Server's tempdb. It has six tempdb files, according to SSMS and queries, all pointing to S directory. Each of them have ~5gb of initial size. The weird ...
2
votes
1answer
28 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
121 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
23 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
76 views

Moving a SQL Server TempDB of AlwaysOn members [closed]

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
369 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
110 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
175 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 ...
2
votes
1answer
2k 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
120 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
68 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
85 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
6k 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
114 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
75 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
348 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 ...
3
votes
2answers
666 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. ...
4
votes
1answer
532 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
186 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
145 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
138 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
31 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
630 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
3k 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
2k 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
327 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
325 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
655 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
115 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
548 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
289 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
109 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
58 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
138 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
360 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
1k 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
118 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
186 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
896 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
227 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?