SQL Server One query statement causes tempdb to grow by 55G

Operating the server today, suddenly the lower right corner of the prompt "C disk space is insufficient"!

A fright! ~

Take a look at the C drive, and 7m!!!. How could this big C-disk space be gone? You can't wait for the server to move!

The first reaction might be a log problem, most likely a database log problem

So look at the log, are not big, normal.

DBCC SQLPERF (LOGSPACE)

Check the system error:

Is the tempdb problem, but just looked at the log only a few m, as prompted to view the log status:

Database journaling now has nothing to do, maybe it's done.

The active virtual log is also not many, 10 or so:

DBCC LOGINFO

View current tempdb situation, surprise, tempdb data file 55g! Look at the graph above, which is a sudden increase.

So immediately shrink the log, shrink the data file, shrink out about 1G.

Or not, continue to constantly change the size of shrinking, as long as less than 55G are changed data to shrink, unexpectedly still can shrink 9g!

DBCC shrinkfile (N ' tempdev ', 1024)--in units of MBDBCC shrinkdatabase (tempdb, 1024);--MB

Temporarily relieved, seems to be unable to shrink. All said to restart the server is not the only line, the current connection is more, there is no restart. So first check out what caused the cause.

View current cursors, SQL, clogging, etc., nothing found, the transaction should be executed.

To view the allocation of tempdb records:

Use Tempdbgoselect Top ten t1.session_id,                                                    T1.internal_objects_alloc_page_count,  T1.user_objects_alloc_page_ Count,t1.internal_objects_dealloc_page_count, T1.user_objects_dealloc_page_count,t3.login_name,t3.status, T3.total_elapsed_timefrom sys.dm_db_session_space_usage  T1 INNER join sys.dm_exec_sessions as t3 on t1.session_id = t3.session_id where (t1.internal_objects_alloc_page_count>0 or T1.user_objects_alloc_page_count >0or T1.internal_objects_dealloc_page_count>0 or t1.user_objects_dealloc_page_count>0) Order by T1.internal_ Objects_alloc_page_count desc

There are four key messages:

session_id: Wait until you can find information about the session

Internal_objects_alloc_page_count: The data page assigned to the inner object of the session

Internal_objects_dealloc_page_count: Data pages that have been released

Login_name: The logon name of the session

As can be seen from Internal_objects_alloc_page_count and Internal_objects_dealloc_page_count, the session was assigned 7236696 pages, calculated as follows:

Select 7236696*8/1024/1024 as [SIZE_GB]

55G, almost identical to the size of the tempdb growth, can be determined by this session. Internal_objects_dealloc_page_count can see that it has been released, and take up tempdb data has been released.

With the login name, you already know who is in action. (This is one of the benefits of giving each relevant person a sign-in name, which can be traced quickly to the user, internal to the operator)

Now look at the usefulness of this session_id:

Select P.*,s.text from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text (p.sql_handle) swhere spid = 1589

See finally there is a statement:

Copy out, almost the largest database of 8 tables do inner JOIN connection query !!

The code is not posted.

The problem of tempdb enlargement has been identified for some reason. tempdb shrinks from 55285MB to 47765MB, but the size of the problem is delayed restart the service is looking at.

SQL Server One query statement causes tempdb to grow by 55G