An SQL Server query statement increases tempdb by 55 GB and tempdbby 55 GB.

An SQL Server query statement increases tempdb by 55 GB and tempdbby 55 GB.

When I operated the server today, the lower right corner of the page prompts "disk C is not enough "!

Scared !~

Look at drive C, there are still 7 M !!! How can I lose such a large C disk? Cannot wait until the server moves!

The first reaction was probably due to a log problem, which may be a database log problem.

So it is normal to check logs.

dbcc sqlperf(logspace)

Check whether the system reports an error:

It is a tempdb problem, but the log is only a few mb. view the log status as prompted:

select name,log_reuse_wait_desc from sys.databases 

There are no operations in the database diary. It may be that the operation is complete.

There are not many virtual logs for the activity, about 10:

dbcc loginfo

Check the current situation of tempdb. It's amazing that the tempdb data file is 55 GB! Look at the figure above, that is, the sudden increase.

As a result, the log is immediately reduced, and the data file is reduced to about 1 GB.

Still not good, continue to change the size of shrinking, As long as less than 55G all change data to shrink, it can still shrink 9G!

Dbcc shrinkfile (N 'tempdev', 1024) -- unit: mbdbcc shrinkdatabase (tempdb, 1024); -- unit: MB

It seems that the contraction is not enough. You have to restart the server. Currently, there are many connections and no restart is required. Check the cause first.

Check the current various cursors, SQL statements, congestion, and so on. If nothing is found, the transaction should be finished.

View the distribution of tempdb records:

use tempdbgoSELECT top 10 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 information:

Session_id: Wait a moment to query information about the session.

Internal_objects_alloc_page_count: Data Page allocated to the internal object of the session

Internal_objects_dealloc_page_count: released data page

Login_name: The Login Name of the session

From internal_objects_alloc_page_count and internal_objects_dealloc_page_count, we can see that 7236696 pages are allocated to the session, and the calculation is as follows:

select 7236696*8/1024/1024 as [size_GB]

It turns out to be 55 GB, which is almost the same as the size of tempdb growth. It can be determined that this session is caused. Internal_objects_dealloc_page_count: You can see that the data of tempdb has been released.

By using the login name, you already know who is performing the operation. (This is one of the benefits of giving each relevant person his/her own login name. It can quickly track users and is operated by internal personnel)

If the above DMV is queried for too long from the event occurrence time, it may not be found. (In less than five hours, I will not be able to query the above sessions, so check the sessions as soon as possible)

Now let's take a look at the usefulness of session_id:

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

The last statement is displayed:

Copy it out. It is almost the maximum 8 tables in the database for inner join query !!

The code will not be pasted out.

What causes the increase of tempdb has been found. Tempdb is reduced from 55285MB to 47765 MB, but the problem may occur after the service is restarted.