Questions tagged [tempdb]
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.
334 questions
0
votes
1
answer
508
views
Could LCK_M_SCH_S cause a spike in read/write latency for tempdb files?
I noticed a big uptick in tempdb read/write. This was during the same period of time that someone ran Alter Index and I noticed that the biggest wait at that time was LCK_M_SCH_S.
I'm trying to track ...
- 209
20
votes
2
answers
3k
views
SQL Server in-memory optmized tempdb metadata memory usage continually growing
Problem
We have enabled sp_configure 'tempdb metadata memory-optimized' = 1, and now the tempdb meta data is taking over 400 GB on one of our servers and continues to grow. There are some drops in ...
- 488
1
vote
1
answer
963
views
When tempdb allocations are released?
In a Service Broker queue, when one message is proceed over time, we are executing a stored procedure which is performing some heavy operations using temporary tables.
I guess that the sp_WhoIsActive ...
- 4,318
2
votes
3
answers
5k
views
Urgent help with TempDB log file growth mystery
I need some urgent guidance for a tempdb issue for past last week:
The tempDB log file is growing starting a specific time, lets say 2 PM. And almost continue to grow upto 150 GB and then comes down ...
- 2,210
0
votes
1
answer
208
views
does SELECT with implicit transaction prevent VersionStore cleanup?
The JDBC driver usually sets the implicit transaction on in every query executed by the client.
I found many SELECT statements in suspended state with implicit transaction set to ON using Version ...
- 107
1
vote
1
answer
286
views
Avg write latency spikes for disk more than usual. Need help finding the culprit
We recently had increased in disk write latency reported. Though from our monitoring team it was not confirmed from which drive but doing some dmv query I see tempdb around avg 32 ms wait on write. ...
- 794
1
vote
0
answers
419
views
Error [36, 17, 145] occurred while attempting to drop allocation unit ID ##### belonging to worktable with partition ID #####
We regularly get this error on a SQL Server 2016 SP2 instance.
(Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise
...
- 11
4
votes
1
answer
307
views
sys.dm_tran_version_store_space_usage reports always zero space used
I was learning about RCSI when I discovered something strange on Azure SQL Database.
The DMV sys.dm_tran_version_store_space_usage always reports 0 as the space used by the version store, even if I ...
- 135
2
votes
1
answer
758
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
2
answers
630
views
reapply permissions post sql server restart
In our environment, users were granted read/write/ddladmin permissions on tempdb database (dev/qa/prod).
These permissions will be lost when SQL Services restart, as tempdb rebuilds every time. One ...
- 442
-2
votes
1
answer
605
views
Killed SPID on xp_LogInfo not rolling back and growing tempdb [duplicate]
I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task.
I checked the scheduled tasks and we had ...
- 15
9
votes
1
answer
832
views
2019 Enterprise - NOLOCK without NOLOCK, page latch time-out on tempdb and finally a dump [closed]
We have a very strange problem with a new SQL Server 2019 on 2 new physical machines:
Infrastructure:
Starting a new installation of SQL Server 2019 Enterprise (15.0.2000.5 / X64 on Windows Server ...
- 515
0
votes
1
answer
314
views
How can I tell if TempDb is too big?
Let's say that someone wants to follow sage advice and properly setup TempDb and TempDb's log. But let's also say that the chosen sizes are almost certainly too big/aggressive. E.g. pretend we've got ...
- 111
0
votes
1
answer
571
views
TempDB Version Store used by DB STARTUP background process
I have two availability group on the same SQL Server 2012 Instance (synchronous commit).
Trace flags 1118 and 1117 are both enabled.
TempDB has been growing for about two weeks: it starts from 36GB ...
- 107
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
1
vote
2
answers
293
views
Provisioning SQL Server 2019
I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with.
My build has to ...
- 13
2
votes
2
answers
8k
views
MSSQL TempDb full
I have a problem with my MSSQL cluster. When switching to SQL Server 2016 (from 2014) an error occurs with the tempdb, which is full after a certain time for no apparent reason.
Is there a proven way ...
- 125
1
vote
1
answer
15k
views
tempdb not releasing space
I have 1 tempdb mdf file, and 7 ndf files. They all grew to 63 GB, and filled up the 500 GBdrive (the .mdf and .ndf files say 99% free space.).
I ran sp_who2 'Active' and there is no active ...
1
vote
1
answer
778
views
Azure tempdb filling up while trying to convert a large table to columnstore... how to mitigate?
I have a large reporting table (approx 6B rows) taking up roughly 400GB of storage on an azure sql db. I've been trying to convert it to a columnstore table using the following commands:
insert Logs(...
- 1,271
1
vote
2
answers
501
views
Why does TempDB spill happen even though statistics are correct?
I read a great article published by Brent Ozar and came up with some questions related to memory grant. I am unable to address my questions in the comment section of his article, so I thought to get ...
- 1,303
2
votes
1
answer
340
views
Version Store usage and entries in dm_tran_active_snapshot_database_transactions
So I'm trying to understand why are having occasional problems with tempdb filling up with version store entries.
I'm closing in on the problem, and am now seeing entries in ...
- 256
5
votes
2
answers
1k
views
is there any point of moving tempdb files into different disk drive if all LUNs are from the same RAID pool?
According to best practices, it is recommended to move all tempdb (not just tempdb) files into different physical disk.
I have a virtual server which had originally 4 LUNs from the same RAID 10 pool....
- 1,303
0
votes
1
answer
125
views
Increase TempDB Files, Shrink VLF's with FCI
I am planning to resize and increase the number of tempDB files and shrink the VLF's on an FCI. In this case, the tempdb files are stored on the network drive, rather than the local disk. I couldn't ...
- 1
1
vote
1
answer
537
views
How do you minimize chance of collision between system-named constraints on temp tables?
We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures attempt to create temp tables with a system-named PK ...
- 143
2
votes
1
answer
4k
views
High write latency on tempdb datafiles only
I'm experiencing large write latency on our tempdb database, but on no other database.
(from sys.dm_io_virtual_file_stats) Tempdb log file latency is also listed as being below 5ms.
For comparison:
...
- 51
3
votes
2
answers
608
views
Should tempdb be larger on a secondary replica?
I am working on SQL Server 2012 with Availability Groups.
On the secondary replica, the size of the tempdb datafile is roughly double that of the primary. I am struggling to find information on ...
- 31
0
votes
1
answer
189
views
TempDB Configuration using 8 TempDB Data File
On a production server with 512 GB RAM , 2 Sockets 24 Cores (Total Logical Processors 48), i have configured to have 8 TempDB Data Files ( on Primary) with each 4096 MB growth of 1024 MB and Log File ...
- 11
1
vote
0
answers
391
views
SQL Server 2017, XEvent: sql_text IS NOT NULL
I'm attempting to use XEvents to find out which queries are causing my templog.ldf file to grow in size. I'm getting events, but I see the sizes of the files haven't changed, and that the sql_text ...
- 165
0
votes
2
answers
199
views
SQL Server Standard Edition CPU + Tempdb Configuration
I have SQL 2014 sp3 (Standard) With below CPU Details according to 8 CPU we have configured 8 Tempdb datafiles.
Based on my configuration could you please suggest what should be the CPU & Tempdb ...
- 1
0
votes
1
answer
394
views
SQL Server 2016 TempDB physical file size is different than SQL System Catalog And Proportional Fill Algorithm is not working as expected
When I see size of file physically on drive, it is different for one of the tempdb secondary data file when compared to size mentioned in system catalog. What can the reason for it?
In tempdb ...
36
votes
2
answers
8k
views
Is it generally faster to select into a temp table than selecting into an actual table?
I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity?
I thought I recall it saying something special about tempdb and ...
- 39.5k
1
vote
1
answer
136
views
65G SQL Server database has 5.5G of data in tempdb when not in use
I have a SQL Server instance with 1 production database (also ReportServer dbs) that is 65G. tempdb is 5.5G (well, actually much bigger but 5.5G is in use) when server is not in use. I believe this ...
- 11
0
votes
1
answer
821
views
Failed attempt to fetch logical page - tempdb [duplicate]
At least 2 times a week I get an error as the tempdb should be corrupt.
Failed attempt to fetch logical page xxxx in database 2.
This on all my 8 SQL Servers 2008 R2.
The strange thing is that ...
2
votes
0
answers
728
views
Test if I have too many TempDB Files
I know there are ways to test for tempdb contention to see if you need to add more tempdb files, but how can I test (and maybe prove) if I have too many tempdb files?
I've inherited a relatively ...
- 66
2
votes
1
answer
467
views
Not able to regulate/auto grow tempdb size
We have a SQL Server 2017 (RTM-CU15) AG group (3 servers, 2 HA synchronous) that has had performance issues for the past two months. During the resolution of an issue yesterday we found that the ...
- 129
0
votes
4
answers
5k
views
Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database
Can I change tempdb size to a specific value and can I shrink it during our daily working hours on our production database ?. Please tell me if it has bad effect on a production database.
My database ...
- 101
0
votes
0
answers
283
views
How does DBCC CHECKDB use tempdb?
I would like to know the working principle of DBCC regarding the tempdb. I want to know why it uses tempdb for what purpose. I didn't find any advanced article about that so maybe someone can shortly ...
- 1,303
2
votes
2
answers
2k
views
Cannot connect to my SQL Management studio after moving a tempDB
In quite a bad position here. This afternoon on my tempDB I ran the query:
sp_helpfile to locate where my MDF and LOG file were. My MDF was on my C: drive and my LOG file was on a D: drive so I've ...
- 123
0
votes
0
answers
1k
views
Operator used tempdb to spill data, spill level 1
Here I have a query which is spilling to tempdb.
I understand that the cardinality estimates are wrong and causing this spill. I've read few articles that mentions updating the statistics and ...
- 100
6
votes
2
answers
12k
views
Best approach for moving tempdb .ndf files
During a SQL Server installation, I "thought" I specified location of tempdb on 'F:' and not 'C:', but I can see that although tempdb.mdf and templog.ldf are on 'F:', the 3 .ndf secondary data files ...
- 93
2
votes
0
answers
316
views
Same temp table has created by different session and occurs blocking issue. how to avoid it?
It's a SQL Server 2012 EE.
One session (SID=90) executed a JOIN query, and it's generated a temp table internally by SQL Server.
But another sessions (SID=103, 109, 110,116) from totally different ...
- 61
2
votes
0
answers
62
views
Tempdb. There is no enough space on the disc encountered
Does anyone know what really happened here?
All of the sudden database was not accessible.
How to find transactions that cause tempdb filled up?
Seems like it happened right after SQL profiler ...
- 707
11
votes
4
answers
2k
views
Why would shrinking TEMPDB log lead to slowness?
I recently accidentally shrunk tempdb log to almost 0 after getting alerted that log drive filling up. I am told that it will lead to slowness. Can someone please explain why it will lead to slowness?
- 113
0
votes
0
answers
96
views
When does setting the SORT_IN_TEMPDB option actually improve performance? [duplicate]
My question is related to index rebuilding, mainly the SORT_IN_TEMPDB option.
BOL states that:
Even if other users are using the database and accessing separate disk addresses, the overall ...
- 1,303
3
votes
1
answer
1k
views
Does increasing the tempdb file size require a restart
I want to alter tempdb as such (i am increasing the size)
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'...
- 399
4
votes
2
answers
18k
views
Appropriate tempdb size?
DBCC CHECKDB is failing on a particular database because of
Could not allocate space for object 'dbo.SORT temporary run storage: 143479689576448' in database 'tempdb' because the 'PRIMARY' ...
- 399
2
votes
1
answer
2k
views
Is splitting tempdb files across drives necessary?
If I have 4 tempdb files split across 2 drives with the log on a third drive, is that necessary/helpful?
I want to persuade my manager to put all 4 tempdb files on one drive.
If one of those tempdb ...
- 399
0
votes
1
answer
2k
views
How to exclude tempdb from sp_MSforeachdb?
Below I have a code to get info about t-log backups for each database in the server. I want to exclude tempdb from this list.
exec sp_MSforeachdb 'SELECT server_name, sysdb.name AS DatabaseName, bkup....
- 1,303
0
votes
1
answer
580
views
Enabling write caching for TempDb data drives
Recently, we had issues with slowness for some queries where TempDb latencies on their respective drives had spikes.
We already have 8 tempdb files and trace flags 1117 and 1118 are not enabled. We ...
- 2,210