Monitoring Long Running Transactions in TempDB

TempDB database plays a major role in SQL Server. Therefore, it is extremely important to monitor the health of the TempDB database. One of the major challenges in TempDB is maintaining it's log file. If there are transactions that use the TempDB and if those are long-running transactions, there can be situations where the log file will grow. Since these transactions are not closing, log space will not be returned and the entire server will not be able to run queries that use the TempDB. 

Recently, one of the Clients had a similar problem. One query was running for more than four days and it had consumed TempDB log file. This has caused empty disk space and the entire server is halted for operations.

In this situation, the easiest and laziest thing to do is the restart the server. Restart will kill all the transactions and return TempDB back to the original size. This is not something that you can do for a system of 24x7. 

However, we choose not to restart but to identify the long-running query from the following simple query.

SELECT  se_tr.session_id,

sec.login_name,

trn.database_transaction_begin_lsn,

trn.database_transaction_begin_time,

trn.database_transaction_log_record_count,

 trn.database_transaction_log_bytes_used,

 trn.database_transaction_log_bytes_reserved,

 t.text,

 q.query_plan

FROM sys.dm_tran_database_transactions trn

INNER JOIN sys.dm_tran_session_transactions se_tr ON trn.transaction_id = se_tr.transaction_id

INNER JOIN sys.dm_exec_sessions sec ON se_tr.session_id = sec.session_id

INNER JOIN sys.dm_exec_connections con ON con.session_id = sec.session_id

LEFT OUTER JOIN sys.dm_exec_requests req ON req.session_id = sec.session_id

CROSS APPLY sys.dm_exec_sql_text  (con.most_recent_sql_handle) t

OUTER APPLY sys.dm_exec_query_plan (req.plan_handle) q

WHERE trn.database_id =DB_ID('TempDB') 

This gave the option to identify the long running query and we killed the relevent session. With that, TempDB log file was emptied and by shrinking the tempdb log file, we were able to gain the disk space. 

Further, we took a pro-active decision by enabling an alert, so that if a query runs for more than 8 hrs (configurable) that will be altered the DBA so that he can kill the session straightway. 

Configuring Files for TempDB database

First article on SQL Server 2016

http://www.sql-server-performance.com/2015/configuring-files-for-tempdb-database/

SQL Server 2016 : Getting tempdb a Little More Right

Aaron Bertrand has written a nice blog post about tempdb improvements in SQL Server 2016. You can read more from http://blogs.sqlsentry.com/aaronbertrand/sql-server-2016-tempdb-fixes/.

The most important feature for me is ability to configure multiple files to tempdb during the installation. Currently, you need to do this manually and restart the server which will not be required with SQL Server 2016. In the configuration it also check the number of cores and provide the guide line as well.

Collation Conflict - 3

I posted a blog post about Collation Conflict some time back. With the discussions on Temp tables and table variables we realized that we have an option with table variable as work around for the above problem.

However, we have much cleaner option in SQL Server 2012 by using contained database option.

Let us create the Contained database.

CREATE DATABASE [Danish]
CONTAINMENT = PARTIAL
COLLATE Danish_Norwegian_CI_AS
GO

Then let me run the previous script which failed in standard databases.

USE Danish
GO

CREATE TABLETable1
(IDINT IDENTITY,
NameVARCHAR(50)
)

INSERT INTOTable1
(NAME)
VALUES
('A'),
('B'),
('C')

CREATE TABLE#Temp1
(MaxIDNAME VARCHAR(50)
)

INSERT INTO#Temp1
SELECTMAX(Name)FROMTABLE1

SELECT*FROMTABLE1
WHERENAMEIN
(SELECTMAxIDNAME from#Temp1)

This time we don’t have any errors.

Actually, temp table  is created in the tempdb but will not have an issue as in the standard databases.

Temp Tables Vs Table Variables

I have posted couple of blog post on the above topic which are very popular.

Those blog posts were,

Temp Tables Vs Table Variables Vs CTE

What's the difference between a temp table and table variable in SQL Server-

Today I came across with nice picture which explains every thing from http://sqlserverplanet.com

Isn’t this very simple and easy to understand?

Collation Conflict 2

I posted a blog post about Collation Conflict some time back. With the discussions on Temp tables and table variables are on let us do this with a table variables.

Let us prepare the data set needed.

Then run the following using a table variable instead of temp table.

and this it is a success ( With using temp table, this will fail since tempDB in different collation)

So another place to use table variable and the debut continues …