All Questions
Tagged with sql-server-2017 tempdb
12 questions
0
votes
1
answer
215
views
Can Query Store Show Spills To Tempdb?
I know that Query Store can show tempdb usage as of SQL Server 2017, but can it show spills to tempdb? I'm highly confident that the plan cache can.
- 853
2
votes
1
answer
760
views
System SPID running for hours filling up the TempDB Log
I've been trying to track down the source of my tempdb log filling up every few weeks where the tempdb log space used will continue to grow until it reaches 99% and stays there for hours while the log ...
- 293
2
votes
1
answer
3k
views
Unexplained sustained space used in the TempDB log file
A few days ago the amount of used space in both the tempdb data and log files started growing rapidly. After 30 minutes, the tempdb log remained around 99% space used for about 7 hours. Towards the ...
- 293
0
votes
1
answer
3k
views
TempDB Log File Growth - questions
Problem:
On one of the production boxes, TempDB transaction LOG file grew unexpectedly, filling the drive
It was 32 GB but now it is 240 GB
Questions:
What is the mechanism of TempDB log file usage ?
...
- 5,964
0
votes
1
answer
410
views
TempDB file overflow
This is my experience with TempDB. The Algorithm works better when
the TempDB File sizes are the same size.
We were seeing heavy TempDB contention on a TempDB file that was larger than the others. We ...
- 7
3
votes
1
answer
6k
views
SQL Server sys.dm_tran_active_transactions long running transaction worktable
today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no ...
- 705
2
votes
0
answers
368
views
Aggressive Tempdb spill process
We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures.
Tested on SQL2016 and SQL2017 Enterprise (recent ...
- 165
2
votes
1
answer
4k
views
Should TempDB SSD Drive be formatted with 64 Kb Allocation Unit Size?
We have installed dedicated SSD drives for TempDB
First question - should these drives be formatted with 64 Kb allocation size ? Currently it shows 4 Kb (Bytes per Cluster = 4096)
Does TempDB ...
- 5,964
1
vote
1
answer
2k
views
Why do LocalTemp table showing with lengthy underscore under Temporary Tables of tempdb in SSMS 2017 Express Edition? [duplicate]
In my Microsoft SQL Server 2017 Express Edition, I have created a local temp table using below create statement,
create table #localtemptable
(tempid int,tempname varchar(20) )
After creating #table,...
4
votes
2
answers
2k
views
Contention on tempdb due to high concurrency temp table creation from multiple databases?
I have an environment with ~100 databases, all with the same schema. There is a stored procedure on each database that creates a #temp table and drops it, and is running very often (every 30 seconds ...
- 399
3
votes
1
answer
565
views
Disk sizing considerations for 'tempdb' - Growing substantially two times a year
I have an HR software on which an employee of the HR department runs long and complex analyses and calculations about two times in a year. Because of this the tempdb is growing to 500GB and even more. ...
1
vote
1
answer
534
views
Filestream directory exists for a partition but the corresponding partition does not exist in the database
I am investigating a high memory usage issue of tempdb on a production SQL Server 2017 Enterprise (64-bit) deployment. This is similar to an issue discussed here. The server has two production DBs ...
