Why the tempdb data file suddenly grows

Last week, the company's production bank of tempdb suddenly soared, resulting in disk space for 0, it is estimated that the relevant personnel run unreasonable SQL query caused.

Tempdb can be used in the following situations:

(1) temporary table established by the user. If you can avoid it, try to avoid it. If you use temporary tables to store large amounts of data and frequently access them, consider adding index to increase query efficiency.
(2) Schedule jobs. such as DBCC CHECKDB will take up more resources in the system and more use of tempdb. It's best to do it when SQL Server loading is lighter.
(3) Cursors. Cursors can severely affect performance should be avoided as much as possible.
(4) CTE (Common Table Expression). will also be executed in tempdb.

(5) sort_int_tempdb. This option is available when you set up index.
(6) Index online rebuild.
(7) Temporary worksheets and intermediate result sets. When the join is generated.
(8) The result of the order.
(9) After and INSTEAD of triggers.

So if someone queries multiple tables in a database using the Cartesian product, and sorts, it can cause a surge in tempdb, because a lot of the sort is done in tempdb.

To monitor the size of the files in tempdb or the log of tempdb, one of the following stored procedures is written:

First set up three tables:

--Saves the current tempdb data file and log file size if exists (SELECT * from sysobjects where id = object_id (N ' [check_tempdb_size] ') and Objectpro Perty (ID, N ' isusertable ') = 1) drop table [check_tempdb_size] Go Create table check_tempdb_size (id INT IDENTITY, Fi Leid int, size int, Name varchar (a), InsertDateTime datetime default GETDATE ())--saves information about the current DBCC OPENTRAN if exists (s  Elect * from sysobjects where id = object_id (n ' [check_tempdb] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) drop table check_tempdb Go CREATE table check_tempdb (ID INT IDENTITY, TypeName varchar (MB), Typevalue varchar (MB), Insertdate D Atetime default GETDATE ())--saves information about the current running process (database, login account, login machine, running SQL) if exists (select * from sysobjects where id = obj ECT_ID (n ' [Check_tempdb_script] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1) drop table Check_tempdb_script Create t
 Able Check_tempdb_script (ID int IDENTITY (1,1), spid int, ecid int, [Database] varchar, nt_username nchar (256), [Status] varchar (20), [Wait] varchar (m), [individualquery] varchar (max), [parentquery] varchar (max), program nchar (256), Hostname nchar (256 ), Loginame nchar (256), Nt_domain nchar (256), Start_time datetime, InsertDateTime datetime default GETDATE ())

Save the size of the data file box log file in the current tempdb in the Check_tempdb_size table

INSERT INTO Check_tempdb_size
Select Fileid,size,name,getdate ()
From tempdb.. Sysfiles