Stack Overflow
Stack Overflow

Questions tagged [tempdb]

126 questions
28
votes
4 answers

Temp Table collation conflict - Error : Cannot resolve the collation conflict between Latin1* and SQL_Latin1*

I can't update temp table. This is my query CREATE TABLE #temp_po(IndentID INT, OIndentDetailID INT, OD1 VARCHAR(50), OD2 VARCHAR(50), OD3 VARCHAR(50), ORD VARCHAR(50), NIndentDetailID INT, ND1 VARCHAR(50), ND2 VARCHAR(50), ND3…
thevan
  • 9,298
  • 52
  • 128
  • 193
18
votes
2 answers

Why is this query slow the first time after I start the service?

Ok. Here's what I try to run: USE tempdb; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4; This is one of those "make me a numbers table"…
Atario
  • 1,305
  • 13
  • 23
16
votes
4 answers

SQL Server tempdb optimization tips for a new server?

I am planning a fresh installation of SQL Server 2005 on a new machine, which I have to order. I know that tempdb tuning is very important to the overall performance of the SQL Server instance. I've read that it's best practice to create as many…
splattne
  • 100,048
  • 51
  • 202
  • 247
16
votes
3 answers

How tempDB works?

I am trying to understand the tempDB and following are the doubts popping in my mind. What is the lifetime of data in tempDB? Say a query is doing some Order By and uses tempDB for performing that. After this query finishes, someone else also…
peakit
  • 25,979
  • 25
  • 58
  • 77
12
votes
3 answers

Failure SQL query insufficient disk space

Msg 1101, Level 17, State 10, Line 12 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the…
JsonStatham
  • 8,163
  • 26
  • 88
  • 160
12
votes
6 answers

Why does my tempdb reset permissions when the server is rebooted?

The past two times we have rebooted our sql server, our website has gone down. The reason appears to be because the tempdb is getting recreated and the ASPState user is losing permission to read/write to the tempdb (it is an ASP site and session…
Rachel
  • 122,023
  • 59
  • 287
  • 465
11
votes
3 answers

Do CTEs use any space in tempdb?

Do CTEs use any space in tempdb or does it use memory exclusively? I've tagged the question with both mssql 2005 and 2008 as I use both.
deutschZuid
  • 1,013
  • 1
  • 15
  • 31
11
votes
5 answers

SQL Server 2005 Memory Pressure and tempdb writes problem

We are having some issues with our production SQL Server. Server: Dual Quad Core Xeon 8 GB RAM Single RAID 10 Array Windows 2003 Server 64-bit SQL Server 2005 Standard 64-Bit There is about 250MB of free RAM on the machine right now. SQL Server has…
Brian
  • 741
  • 2
  • 10
  • 25
8
votes
2 answers

When a variable is declared in a T-SQL stored procedure, is it kept in memory or tempdb?

We're trying to optimize some of our T-SQL stored procedures to reduce tempdb contention, but I can't figure out how non-table variables are stored by SQL server: What about simple data types like INT and DATETIME? It feels like they'd live in…
Evan M
  • 2,365
  • 1
  • 27
  • 35
7
votes
1 answer

SQL Server Tempdb LOG file growing

On a SQL Server 2000 system, I have a templog.ldf file that seems to grow without bound! But when I check, there are never any open transaction in the tempdb (using DBCC OPENTRAN), nor do I ever use explicit transactions within tempdb. I do,…
Apt605
  • 144
  • 1
  • 2
  • 10
6
votes
1 answer

SQL Server 2008 TempDB on other HD

I would like to make all stuff related to TempDB be stored on a separate HD. I have this new HD with a 500 Gb size as my E:\ drive. How would I use or move TempDB from one drive to another…
edgarmtze
  • 23,307
  • 73
  • 221
  • 364
6
votes
3 answers

Azure VM SQL Server Tempdb on Temporary Storage

We're setting up SQL servers in the Azure cloud using VMs. When we were determining the best setup for our data/logs/tempdb we ran into many blog posts that recommend placing the tempdb on the Temporary Storage drive provided by Azure. However…
IvanL
  • 2,375
  • 1
  • 22
  • 37
6
votes
4 answers

SQL Server 2005- Investigate what caused tempdb to grow huge

The tempdb of my instance grew huge eating up all the available disk space and causing applications to go down. Had to restart the instance in emergency. However, I want to investigate and dig deep as to what caused the temp db to grow huge all of…
AK2
  • 71
  • 1
  • 2
  • 7
5
votes
3 answers

Is it safe to delete the tempdb.mdf file manually?

In SQL Server 2008, I am trying to BULK INSERT a CSV that's about 1GB in size. As I'm doing so, it's creating a huge tempdb.mdf file. Right now, it's 35GB for this 1GB CSV file. I've tried the various solutions that Microsoft provides, and they…
mlissner
  • 14,662
  • 15
  • 82
  • 149
4
votes
5 answers

Persistent temp tables in SQL?

Is it possible to have a 'persistent' temp table in MS-SQL? What I mean is that I currently have a background task which generates a global temp table, which is used by a variety of other tasks (which is why I made it global). Unfortunately if the…
Mark
  • 562
  • 3
  • 5
  • 20
1
2 3
…
8 9