Tempdb has a big accident record-sqlserver

Source: Internet
Author: User

Today, I get an alert message that the disk space is full and it feels strange. How could a new machine be installed that is running out of disk space? login and look.

You can see that tempdb is already 65G, and the display is hundred percent available. This is very strange, why this is the case. Given that the disk is full, restart SQL Server immediately, modify the initial value of tempdb to 10G, and then reclaim the available space to the OS, It looks like it's over. In fact, this is not the case, and my tempdb has been exploding for the next day. What's going on here? The program that I run now is basically not, just as a copy of the Subscriber. How did this happen? And then landed and looked at the database statistics:

SELECT 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_time
From 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 >0
or t1.internal_objects_dealloc_page_count>0
or t1.user_objects_dealloc_page_count>0)
ORDER BY T1.internal_objects_alloc_page_count Desc

View the amount of tempdb the database session occupies, and then view the details based on the SPID, and then investigate the specific information for the session

According to the information I found a session that took up a lot of temporary space and looked at the details of the session. The SELECT statement is as follows:

Select 1

It's weird, right? Two questions to consider:

1: Why do you keep executing this statement?

2: What is this statement for?

According to the above statistics we can find out the login name and program name, the initial confirmation is that the program caused, and then according to the above two questions to query

The following code was found:

The red line is not the original, a database link link is not released later. So the consistent link database does not release the entire session and conversation resources. Then add a reboot program later. Continue monitoring and find that the problem has been solved.

Tempdb has a big accident record-sqlserver

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.