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 set to 2048 MB with 1024 MB growth and Trace Flag 1117 configured in the startup. the OLTP database of approx. 100 GB data file is been accessed by Dynamics AX application. the AX application server is on another server. The concern is that the AX application is running very slow even after rebuilding/reorganizing indexes and setting the MAXDOP as per the AX Dynamics recommendation. For which i tried to find the Avg Write performance of all database from sys.dm_io_virtual_file_stats (io_stall_write_ms / num_of_writes < 20) it shows above 100 ms. though the read performance is well below 10 ms. The TempDB is stored in the different Disk Drive (which is dedicate cluster storage H Drive - free space 66 GB ) and other DB Files are stored on different Disk Disk ( Cluster Storage Drive). What needs to be done in this case to improvise the write performance. Do i increase the TempDB Data File size more? **attaching the image of the output from sys.dm_io_virtual_file_stats.** ?

I've read recommendations to switch non-sysadmins login's default database to something other than master. Tempdb is often recommended. If a login/user has no permissions by default for their default database, why is it sometimes recommended to change the default database away from master?

I have multiple temporary tables stored in the

schema for an SSIS pipeline that runs daily. The pipeline extracts data from multiple tables and stores it in the temp tables, which the data of the temp tables is later used to store in a different database. My question is about the performance of the creation and deletion of the temp table, I want to know which approach is more optimal. Which of the following approaches is the better option? And what are the disadvantages and advantages of each one? 1-

temp table if it doesn't already exist -->

temp table from previous data before inserting new data -->

the new data into temp table. 2-

temp table if it exists --> use the statement

INTO

to insert data directly without creating a temp table in a separate statement. edit: the tables in the tempdb are created like this

TABLE tempdb..Table1

On Microsoft SQL Server 2019, there is a lot of long temp tables TRUNCATE operations with time over 5..10 seconds. Easy query like "TRUNCATE TABLE #tt22" can take over 15 seconds. It's an intermittent problem, sometimes it disappears for a day after restarting the server . What could be the reason? UPD: According to the latest performance measurements, long TRUNCATE operations are observed not only in temporary tables, but also in physical ones.

We have a alert threshold of tempdb usage on drive level at 90% and getting lot of alerts. But the first problematic point is the tempdb usage occupied whole drive of 500GB among Log file only occupied 95% and not able to increase the log file for other works on tempdb. While we checked the open transactions we seen sleeping sessions are there 2days and 1day before time stamps and not released the space to other transactions. I think it's worst behavior the sleeping sessions blocked the space and released to the others. We need the script for Is really those sessions are occupieing the LOG file space and not released them. (I came to know jdbc drivers sessions suddenly drops and so that the sleeping sessions are not released the space to other transactions). Can you provide Is there any script to identify the same.

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 properties we are also seeing same thing as we say via Query. So query should not be an issue. SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB], CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name], (f.growth*8)/1024 AS [AutoGrowth In MB] FROM sys.database_files AS f WITH (NOLOCK) LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE); Also another major issue is proportional fill algorithm is not working as expected as couple of files are having more size when compared to other files. What can be the reason for it?

I am planning to create additional tempdb files for 2 nodes in a SQL 2012 Availability Group. Unfortunately we do not have Instant File Initialisation turned on so the growth of the tempdb data files will be subject to PREEMPTIVE_OS_WRITEFILEGATHER waits for up to 30 minutes (based on testing). I am concerned that these waits will affect the application/user experience so here is my proposal. - Make the change on Node B - Disable synchronisation - Failover to Node B on successful completion - Make the change on Node A - Fail back to Node A on successful completion - Enable synchronisation. Does anyone see any issues with this ? Thanks

Integrity checks are important on a regular basis to find out errors . Even on servers involved in availability groups . we can normally find out how long it took to run? It is a known fact that dbcc checkdb use a great deal of resources . So I have run it to one of my databases - 170 Gb size. with estimate only :

dbcc checkdb(DEV_My_Database) with estimateonly

It came out: > DBCC results for 'DEV_My_Database'. Estimated TEMPDB space (in KB) > needed for CHECKDB on database DEV_My_Database = 44320313. DBCC > execution completed. If DBCC printed error messages, contact your > system administrator. However, I never saw any extra usage of tempdb as everything seemed to fit in there for this purpose. How can I work out the right usage of resources, including tempdb, for my dbcc ?

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 each with a FILESTREAM. As part of my investigation, I ran DBCC CHECKDB ([db_name]) WITH ESTIMATEONLY; to check if tempdb is sized correctly. The output was as follows. > DBCC results for 'db_name'. > Estimated TEMPDB space (in KB) needed for CHECKDB on database db_name = 53. > Msg 7933, Level 16, State 1, Line 1 > Table error: A FILESTREAM directory ID b1e50dcc-0511-4488-8eeb-ea34966edfab exists for a partition, but the corresponding partition does not exist in the database. > Estimated TEMPDB space (in KB) needed for CHECKDB on database db_name = 16379997. > CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object. > DBCC execution completed. If DBCC printed error messages, contact your system administrator. I was surprised by the error considering that the senior DBA has all of Ola Hallengren's scripts running on a schedule. No errors were reported by these scheduled scripts and despite the error, the DBs are still functioning with the FILESTREAM. I continued my troubleshooting on a test environment with a restore of the full production DB backups. As expected the same error is present. Consequently, I ran DBCC CHECKDB ([db_name]) WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY; but no errors were found. I wanted to execute DBCC CHECKFILEGROUP but as per the documentation , it cannot be executed on a FILESTREAM filegroup. # Question # Is the FILESTREAM-partition-does-not-exist-in-database-error a problem? Can it be resolved? It does not seem that any of the other DBCC commands detect a problem.

Enabling readable secondary will result in addition of 14byte extra information to each row in the primary as well as secondary. This will also automatically enable the snapshot isolation level on the secondary which will result in additional load on the secondary server's tempdb. Is there any chance this will put any load on the primary server's 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 explain or at least share links for the article about that.

On my SQL Server 2019 Enterprise Edition server, when I run select * from sys.master_files I see that *tempdb* has 8 data files and there are 8 data files in the folder. However, when I run select * from tempdb.sys.database_files I only see 1 data file. Also, when I select the properties of *tempdb*, and look at the files, I see only 1 data file. I can delete the 7 extra files, so they are not locked or in use by SQL Server. When I restart the SQL Server service, all 8 data files appear and the whole thing repeats. There are no startup scripts or SPs that run when the service starts. What is going on?

I was looking at some `worktable' from the query below by my friend Martin

SELECT st.session_id
        ,at.*,
 case transaction_type   
      when 1 then 'Read/Write'   
      when 2 then 'Read-Only'    
      when 3 then 'System'   
      when 4 then 'Distributed'  
      else 'Unknown - ' + convert(varchar(20), transaction_type)     
 end as tranType,    
 case transaction_state 
      when 0 then 'Uninitialized' 
      when 1 then 'Not Yet Started' 
      when 2 then 'Active' 
      when 3 then 'Ended (Read-Only)' 
      when 4 then 'Committing' 
      when 5 then 'Prepared' 
      when 6 then 'Committed' 
      when 7 then 'Rolling Back' 
      when 8 then 'Rolled Back' 
      else 'Unknown - ' + convert(varchar(20), transaction_state) 
 end as tranState, 
 case dtc_state 
      when 0 then NULL 
      when 1 then 'Active' 
      when 2 then 'Prepared' 
      when 3 then 'Committed' 
      when 4 then 'Aborted' 
      when 5 then 'Recovered' 
      else 'Unknown - ' + convert(varchar(20), dtc_state) 
 end as dtcState
FROM    sys.dm_tran_active_transactions at
  left JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id
ORDER BY at.transaction_begin_time

one thing that called my attention is the fact that these transactions don't have a corresponding session? how can I find out where they are coming from? There is no session associated with them.

I have a tempdb configured with 8 datafiles and 1 log file. Originally the datafiles were all set to the same size, however, they have grown over time and are now all different sizes. I was able to shrink a few back to the original file size, but there are 4 that won't shrink. All the files show 99% free space in them. I've verified that there are no open transactions in the database and that there is nothing currently using tempdb. How can I shrink those last few -- I wouldn't worry about it at all except the database is approaching the 2 TB limit. Thanks!

We have recently migrated from a version of SQL Server 2014 to a SQL Server 2019 CU 26. In all our tests everything went well before performing the migration but when we made the move to production we began to have serious problems with the TempDB contention. Many processes that used tempdb blocked each other and the entire system was very slow. We have uninstalled CU 26 from SQL Server 2019 and we are with SQL Server 2019 RTM, in this case the SQL is behaving in a better way, it shows me that it is already using the TempDB datafiles and it shows me better behavior but still I have performance problems. The production database uses TempDB in almost all its processes, since it is impossible for me to rollback to the version of SQL Server 2014, is there a way for SQL to treat TempDB as it did in version 2014 when it is in a SQL Server 2019? The database engine has 12 TempDB datafiles.

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. I looked at what the log_reuse_wait_desc was and found it to be ACTIVE_TRANSACTION I started to wonder if for some reason the log file was filling up and the auto grow not kicking in, and after some research I found that the log file should still grow even during an ACTIVE_TRANSACTION. I found an article on a similar issues where the tempdb log ran out of space: http://sqltimes.wordpress.com/2014/07/05/sql-server-error-messages-the-transaction-log-for-database-tempdb-is-full-due-to-active_transaction/ Here they issued a CHECKPOINT to resolve the issue on tempdb. I know a CHECKPOINT flushes dirty pages to disk, however I do not understand how this would fix the ACTIVE_TRANSACTION problem? Furthermore I also do not know why we get this alert when there is plenty of space. Is there a situation where a tempdb can fill and auto grow not work for some reason?

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 one that had been running for over a day, usually it runs for less than a minute, and it was showing a status of "Executing: 0(Unknown)" in the job activity monitor. When I tried to stop the Job in Activity Monitor nothing happened, it just kept on running and filling up tempdb to almost the full size of the disc. Knowing that the job itself could just be stopped and re-run without affecting the business, I, perhaps too hastily, killed the SPID, thinking this would "release" space back to tempdb and I would then restart the services out of hours to shrink the files. Unfortunately this didn't work and the SPID has been in a Killed/rollback state for nearly a day now whilst the tempdb files grow (I arranged for the disk they sit on to be increased whilst I looked into it). When I check the process for the SPID it appears to be running xp_LogInfo with the last batch relating to insert #nt select distinct domain+N'\'+name, sid, sidtype from OpenRowset(TABLE NETUSERGETGROUPS, @acctnam. I believe that the owner of the SQL Job may have reset their password when the job kicked in to cause this (this is just me guessing) which has caused it to get stuck in a loop. The CPU and MemUsage in sysprocesses hasn't changed for over a day now and the LastWaitType is showing as PREEMPTIVE_OS_AUTHORIZATIONOPS. When running DBCC INPUTBUFFER for the SPID I previously killed I am receiving this: EventType - Language Event Parameters - 0 EventInfo - (@P1 nvarchar(128),@P2 uniqueidentifier)EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @P1, @job_id = @P2 I have now restarted the SQL Server Agent Service which stopped the job running but the tempdb log continues to grow. What can I do to resolve this? When running DBCC OPENTRAN(TEMPDB) I get the below, the spid it relates to is the one I killed:

Transaction information for database 'tempdb'.
Oldest active transaction:
SPID (server process ID): 205
UID (user ID) : -1
Name : INSERT EXEC
LSN : (5293:26478:20)
Start time : Oct 19 2020 3:30:02:193PM
SID : 0x0105000000000005150000005f9b10c13b99fcfc4ad890fd8e220200

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

kill {spid} with statusonly returned "SPID 205: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds."

Two things I'd like to know: - how do you safely move tempdb with minimal downtime? - how many tempdb files do you need? Is it 1 file per core? So quad-core = 4 tempdb files, creating three new ones?

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 needed that file larger in the case of run away queries (caused by people not logging off their machines properly - not the SQL Server) which occasionally caused TempDB to fill. We switched to 16 TempDB files (16 CPU's) on RamDisk all the same size with No Autogrowth. We setup a job to kill the spid using the most TempDB resources, once TempDB was 80% full. In working with Microsoft, their technician tested if a TempDB file was created on one TempDB file it would overflow to a second TermpDB file if it needed too (I was concerned about this). Our SQL Server is a Read-Only AlwaysOn and we run some pretty big reports that use large amounts of TempDB. Our new TempDB design has been in production for over 6 months, with no issues. Occasionally, we have a spid cancelled due to TempDB over 80% full, but not very often. ### The question Curious, is there some SQL code I can use to quickly determine if files are crossing multiple TempDB files? We are on SQL Server 2017 CU 12.

We are using SQL Server 2019 with a third party application that makes heavy use of the tempdb database. And we have lot of PAGELATCH_xx waits on tempdb. Memory-Optimized Tempdb Metadata is enabled. Just checked that this query returns 1:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

But we still have tempdb contention. Example of [script from Madeira Toolbox](https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/Identify%20Tempdb%20Contention.sql) output: Is this expected behavior? What could be done to eliminate PAGELATCH_xx waits on tempdb? Should increasing the number of tempdb files help? (the documentation says yes, but tried increasing it from 16 to 32, it doesn't seem to make much of a difference). Should faster tempdb storage (with low latency) help? (I think not, but I can be wrong) Found an interesting presentation: [SQL Server 2022: System Page Latch Concurrency Enhancements](https://www.youtube.com/watch?v=2FYyOk27ZxM)
Will upgrading to SQL Server 2022 solve the problem?