Stack Overflow
Stack Overflow

Questions tagged [tempdb]

138 questions
1
vote
1 answer

While pivoting lots of rows tempDB gets full in SQL server

I have a query which retrieves around 184K rows and stores into Temporary table. Now, In second query I am first retrieving all the data from Temporary table and pivoting it. While pivoting I am getting below error. Msg 1105, Level 17, State 2,…
Jay Desai
  • 821
  • 3
  • 15
  • 42
1
vote
0 answers

View manual file growth events

My tempdb data file has grown significantly in size and I'd like to get an understanding of who made the change, this is a fairly recent event and there are no autogrowth events in the disk usage reports so it would suggest that the file was grown…
Krishn
  • 813
  • 3
  • 14
  • 28
1
vote
1 answer

How to find the sql that caused the temp db to fill the complete drive from history?

We have noticed temp had filled up the drive on which it was placed. It had happened two days ago, is there a way to find the SQL that cause the temp do to grow. The SQL was failed as it couldn't get more space as it already filled up 250GB drive.
user1595858
  • 3,700
  • 15
  • 66
  • 109
1
vote
0 answers

quit query on tempdb full

I want to run most of my queries in a way that they return with an error message if not enough tempdb available. Is there a way to do this? The default behaviour of waiting for tempdb to have more space. But this is useless because if other…
Patrick Fromberg
  • 1,313
  • 11
  • 37
1
vote
0 answers

SQL Server 2008 r2 TempDB data file is not releasing space back to OS

Temp db data file is not clearing or deallocating space to OS after DBCC CheckDB "myDB" process completed successful. I used SP_WhoIsActive script to diagnose and found out DBCC CheckDB process is causing temp db growth but it is only happening for…
Jared
  • 11
  • 5
1
vote
2 answers

When will Temp DB regain de-allocated space?

I understand that on restart of SQL server (or) shrinking the Database will release the de-allocated memory, unfortunately that is not a suitable workaround for my client. If the temp db size is 10mb is grown into 500mb after execution of a…
Saravana
  • 13
  • 6
1
vote
1 answer

How to find duplicates in a large table based on matching and non matching fields?

I have a very large table with more than 10 million records. I want to find duplicates based on some fields matching and some fields not matching in it. The query currently I am using is below: SELECT DISTINCT MainTable.[lineitemid] FROM …
Manigandan Dorairaj
  • 108
  • 8
1
vote
1 answer

How can I confirm if some tempdb files I found are redundant?

On a sql server I have inherited responsibility for I am getting low on space on the C: drive (about 5gb but I was running profiler and it told me it needed to stop because of low space on C) I have found some tempdb files being stored in C:\Program…
MrVimes
  • 3,212
  • 10
  • 39
  • 57
1
vote
1 answer

Size of SQL Server tempDB for Data Warehouse

Is there an expected size of tempDB for a Data Warehouse application. Is 10 gigs excessive? It's hit by frequent large queries. The Data Warehouse itself is 50 gigs. I'm using SQL Server 2000
cindi
  • 4,571
  • 8
  • 31
  • 38
1
vote
1 answer

Where to put log files for tempdb?

In my current project we came up with a question where to put log file for tempdb. Usually we put data file and log file on a dedicated drive in folder like t:\tempdb. On one server we have tempdb logs that were put in the log folder for user user…
dman
  • 73
  • 1
  • 7
1
vote
2 answers

tempdb SQL Server locking

Our application runs alongside another application on a customers machine. We have put some efforts regarding avoiding long-running locks in tempdb since this obviously affects concurrency badly. The other application, however does things like:…
Jens Nordenbro
  • 31
  • 1
  • 3
1
vote
2 answers

How to return single rowset from SQL Server cursor?

I have a query below that works in SQL Server to select the difference between a value in one column and the value from another column, but two rows earlier in the source table. The source table has three columns (PK bigint, Pv float, Cash…
Jon G
  • 4,083
  • 22
  • 27
1
vote
3 answers

Local vs Global temp tables - When to use what?

I have a report which on execution connects to the database with my_report_user username. There can be many end-users of the report. And in each execution a new connection to the database will be made with my_report_user (there is no connection…
peakit
  • 28,597
  • 27
  • 63
  • 80
1
vote
1 answer

Can sufficient increase of available RAM eliminate usage of tempdb when querying read-only database in SQL Server?

When I look at resource usage, SQL Server does a lot of writes into tempdb for some big queries. I don't use temp tables or table variables or any writes, I execute only select statements of read-only database on that server. So I wonder if I…
alpav
  • 2,972
  • 3
  • 37
  • 47
0
votes
1 answer

sybase tempdb log segment filling

I have a Sybase ASE server that hangs every week or so, indicating tempdb log segment is full. I have tried everything. trunc log on chkpt is enabled and it works correctly resetting used_pages about every 60 seconds or so. The problem is, not all…
billsfan80
  • 61
  • 2
  • 7
1 2 3
…
9 10