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

1
vote
1answer
57 views

SQL Server TempDB size issue

SQL Server reported that our tempdb is out of space. So I had a quick google search and found this on technet https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx Determining the ...
8
votes
1answer
1k 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 ...
1
vote
0answers
44 views

ms/write on tempdb data files are dead slow

I don't know why writes on my tempdb data files are extremely slow. ms/write on each of the tempdb data files reaches to more than 800 ms!!! that's even after I moved the 8 equally sized (7GB) tempdb ...
2
votes
0answers
14 views

Service broker internal spid hogging tempdb

We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker ...
4
votes
1answer
95 views

Should we really turn on trace flag 1118 for every SQL instance?

It has been recommended that every SQL instance have TF 1118 turned on What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL ...
0
votes
1answer
138 views

Moving TempDB data and log files

I had to move tempDB files around. Both data and log file were originally on c:\ drive. I moved the mdf file to E:\ drive and log file to F:\ drive. This was successful and I was able to get SQL ...
1
vote
1answer
453 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 ...
6
votes
1answer
420 views

MS SQL high tempdb disk I/O during merge replication of BLOBs

Having a merge publication for replicating BLOBs (sql type - image), got very high tempdb disk I/O for my size of data. Publication is download-only and has no filters. High disk I/O is caused by ...
3
votes
1answer
82 views

Is IFI compatible with tempdb growth?

Is IFI (Instant File Initialization) compatible with tempdb growth? Recently I needed to increase my tempdb by 40 Gb and it took 15+ minutes to complete. I verified that the user had already been ...
6
votes
3answers
148 views

Does alter column change datatype use tempdb version store?

I was running alter column from INT to BIGINT, my table size is 250 GB and after 1 hour I got the version store error because tempdb was full. As far as I know it only takes your memory pages and log ...
0
votes
0answers
32 views

Error while accessing TempDB Properties from SSMS

While trying to access TempDB's properties from SSMS (2014 SP1 SSMS) the following error comes up. Any ideas how to fix this? TempDB is owned by 'sa' and I am logged in with the 'sa' account. TITLE:...
11
votes
2answers
16k 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?
0
votes
1answer
45 views

Increasing the number of tempdb files question about initial size

I am planning on increasing the number of tempdbs we have on our sql cluster. I do have one question that I can't seem to find a clear cut answer to... I have one tempdb that is over 50GB and I want ...
3
votes
1answer
81 views

TempDB uses a lot of space in buffer pool

I'm running a SQL Server 2014 with 2 cores and a max memory of 6GB. When I'm checking what's in the buffer pool, I see that TempDB uses 1GB of memory. That's the query I used: SELECT COUNT(*) AS ...
2
votes
1answer
64 views

Difference size DBCC Page and DBCC Fileheader

I'm trying to read the current database size using dbcc command. In my test I execute following commands: DBCC Fileheader('tempdb',1) => size = 988152 DBCC Page('tempdb',1,0,3) => size = 5112 ...
2
votes
0answers
31 views

SAN Admin vs. DB Admin free allocated tempdb

I really want to understand. My SAN-Admin told me I have to make sure my TempDB does not grow to max hard disk space. I have 380 Gig on a dedicated disk, on a dedicated LUN, on a dedicated machine. My ...
4
votes
4answers
172 views

tempdb growth and shrinking tempdb

I’m a DBA that is fairly new to SQL Server coming over from a UNIX/Informix. I have a question about tempdb growth: From what I can see on regular databases if I start with a database that is 10 GB ...
0
votes
1answer
246 views

LDF files vs. TRN files vs. Tempdb.MDF+LDF files.:

We have a dilemma… We have all of our MDF and LDF files in the same…\192.168.6.22\d$\Program Files\Microsoft SQL Server\DATA\Production...folder. We have all of our TRN and BAK files on the…\192.168.6....
2
votes
1answer
51 views

Temp DB space allocated- will it be reclaimed ever?

I have observed that the tempdb in the production environment has grown over to 100 GB after initializing it. I understand that due to server load and some expensive queries have resulted to the ...
2
votes
1answer
1k views

The connection is broken and recovery is not possible - sql server error

I am having the following sql server error, while creating tempDB files. Any ideas? The connection is broken and recovery is not possible. The client driver attempted to recover the ...
1
vote
2answers
416 views

TempDB default number of files in SQL Server 2016

Noticed the docs for SQL Server 2016 CTP2 state "by default, setup adds as many tempdb files as the CPU count or 8, whichever is lower." https://msdn.microsoft.com/en-us/library/bb510411(v=sql.130)....
1
vote
1answer
211 views

'Logical file name “MyDB” is already in use' Even though the database doesn't exist [closed]

I recently was given a SAN with a couple of RAID arrays to apply to my database server to increase performance. I have (and will only ever have) one user database called DDAS, in addition to the ...
8
votes
1answer
1k 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 ...
6
votes
1answer
118 views

How to solve blocking on tempdb

I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2). Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all ...
13
votes
1answer
230 views

TempDB contentions

We have an active OLTP 40GB database on SQL Server 2014 SP1. Queries are found to be slow with IO_Completion waits, Disk Queue Length rising to 900, and SQL Server stops responding. What we tried: ...
2
votes
0answers
30 views

Very high avg_write_stall_ms for tempdb [duplicate]

What could be causing (and how to fix) my tempdb files (8 equal size) to have [avg_write_stall_ms] of 7300ms and the next highest data file is 47ms. System is Hyper-V fail-over cluster, SQL 2012 ...
10
votes
1answer
182 views

Does CREATE TABLE SomeSchema.#TempTableName have a bug?

Simple test-bed: USE tempdb; GO /* This DROP TABLE should not be necessary, since the DROP SCHEMA should drop the table if it is contained within the schema, as I'd expect it to be. */ ...
7
votes
3answers
8k 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 and one log file. They are all located on the same dedicated drive. When I right click on tempdb in SQL Server Management ...
7
votes
1answer
3k views

Broken tempdb location and can't recover

Made a mistake and mistyped an alter database command for tempdb. Now the instance won't start. I can't start in single user mode using -m as it states the tempdb could not be found. I tried using: ...
5
votes
2answers
7k views

Issues with TempDB mdf file ever increasing

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and ...
7
votes
4answers
937 views

DDL contention on TempDB

I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
2
votes
1answer
67 views

Removing Tempdb .ndfs

Tempdb is configured with 8 files and we are reducing them to 4. I read many blogs where SQL Server will allow you to remove excess .ndf's if you run the 4 dbcc drop and free statements, then run the ...
11
votes
1answer
4k views

SQL Server TempDB behaviour in large memory environment

Reading over this question reminded me of a question I had a while ago. We have a SQL Server that has 512GB of RAM, the main database is 450 GB. We see quite a lot of action in TempDB (ok, I think ...
1
vote
2answers
77 views

Locked TempDB Log

So we're running into an issues where TempDB's log is currently locked up. After checking log_reuse_wait_desc, we see that it is locked from an active transaction. Now the weird part is that when we ...
6
votes
2answers
13k 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. ...
6
votes
2answers
190 views

sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table

I think I need some help as I run out of ideas. In my database I have a simple table like this: CREATE TABLE dbo.Invoices ( CashDiscounts MONEY, Cost MONEY ) GO and a stored ...
37
votes
3answers
85k 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.
1
vote
2answers
308 views

How are these tempdb tables automatically created after each SQL Server restart?

I have a session state management implementation using SQL Server. There is an ASPState database, and ASPStateTempApplications & ASPStateTempSessions tables in the tempdb database. When I restart ...
5
votes
1answer
117 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 ...
2
votes
0answers
133 views

How to use variables in a PL/pgSQL function with dynamic queries?

...and when must I use EXECUTE ... quote_ident(variable)? Taking an input to the function as text and storing part as a variable: wtype := split_part(message, ' ', 4); realval := split_part(message,...
1
vote
2answers
470 views

Understanding the usage of version store in tempdb

We are trying to analyze the growth of tempdb on one of our SQL server 2005 Ent SP4. I know tempdb would need the space depending upon the transactions, and how queries are designed to use it. We ...
1
vote
0answers
181 views

Moved tempdb, now File 'tempdev2' does not exist

I moved the tempdb first locating the files: use master select name, physical_name from sys.master_files where database_id = DB_ID('tempdb'); GO then I moved each file: use master go alter ...
4
votes
4answers
3k 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 ...
8
votes
3answers
254 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) ...
2
votes
2answers
256 views

Moving one TempdB on 3 instance server

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

Controlling how tempdb activity is split across drives (tempdb on both solid state and spinning drives)

I'm seeing the following tempdb I/O behavior over a one hour period: There is a fair amount of disk I/O generated by multiple DW workloads executing on the machine, some of which cannot fit in the ~...
3
votes
1answer
91 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 ...
0
votes
2answers
157 views

Chaning tempdb settings on production database

I have to change the initial size and autogrowth of my tempdb on production. Along, with that I have to add new files to tempdb. What is the right way to do it? As it is production and I don't want to ...
3
votes
1answer
63 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 ...
4
votes
1answer
244 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 ...