April 8, 2019 at 3:20 am
Hi ,
One of my production Server tempdb is getting full abnormally. When I checked about consume tempdb space so I found there is four "Delete" command running continuously and wait type is "BROKER_RECEIVE_WAITFOR".
I checked about the Delete command its actual a Stored procedure which is sending and receiving message and command is written there is "WAITFOR"
Is there anyway related which is causing to tempdb full.
Kindly request to help me on this.
Thanks,
Arooj
April 8, 2019 at 3:15 pm
BROKER_RECEIVE_WAITFOR is typically not a problem and means that something is using Service Broker but there are no messages for it to process.
To research why TEMPDB is filling up, here are some troubleshooting tips:
1. to see high level TEMPDB usage:
BROKER_RECEIVE_WAITFOR is typically not a problem and means that something is using Service Broker but there are no messages for it to process.
To research why TEMPDB is filling up, here are some troubleshooting tips:
1. to see high level TEMPDB usage:
USE tempdb;
SELECT SUM(unallocated_extent_page_count) AS FreePages,
CAST(SUM(unallocated_extent_page_count)/128.0 AS decimal(9,2)) AS FreeSpaceMB,
SUM(version_store_reserved_page_count) AS VersionStorePages,
CAST(SUM(version_store_reserved_page_count)/128.0 AS decimal(9,2)) AS VersionStoreMB,
SUM(internal_object_reserved_page_count) AS InternalObjectPages,
CAST(SUM(internal_object_reserved_page_count)/128.0 AS decimal(9,2)) AS InternalObjectsMB,
SUM(user_object_reserved_page_count) AS UserObjectPages,
CAST(SUM(user_object_reserved_page_count)/128.0 AS decimal(9,2)) AS UserObjectsMB
FROM sys.dm_db_file_space_usage;
2. to see what sessions are using the most space:
SELECT top 10 s.login_name, ssu.*
FROM sys.dm_db_session_space_usage ssu
INNER JOIN sys.dm_exec_sessions s ON ssu.session_id = s.session_id
--ORDER BY (ssu.user_objects_alloc_page_count + ssu.internal_objects_alloc_page_count) DESC
ORDER BY (ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count) + (ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count) DESC;
3. to see largest temp tables:
SELECT s.name AS schema_name, t.object_id, t.name AS table_name,
t.create_date, i.name AS index_name, SUM(ps.row_count) as row_count,
SUM(used_page_count) / 128 AS total_mb, SUM(used_page_count) * 8 AS total_KB
FROM tempdb.sys.tables t
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.index_id IN (0,1)
GROUP BY s.name, t.object_id, t.name, t.create_date, i.name
ORDER BY total_KB desc, s.name, t.name[/code]
this should at least get you started to piece together what is using TEMPDB
this should at least get you started to piece together what is using TEMPDB
April 9, 2019 at 12:11 am
Hi Chris,
Thanks for the response. I ran second query and got the same stored procedure which I mentioned in my first post. The code has written as Waitfor and Receive message and timeout is given as in millisecond.
When I investigate further from SQL log , I got there is some disk I/O error message:
"SQL Server has encountered 14817 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\MSSQL11.abcde\MSSQL\Data\abcde.mdf] in database [abcde] (5). The OS file handle is 0x0000000000000B08. The offset of the latest long I/O is: 0x00003309eb0000"
Is this causing to grow tempdb and what is the solution for it. Please advise.
Thanks,
Arooj
April 9, 2019 at 2:52 pm
That message indicates your IO subsystem is overloaded. It cannot handle the load. This could lead to things taking longer, which could mean more concurrency in tempdb.
What is abnormal? Do you have a regular size that tempdb uses? Is there monitoring to confirm this? If things are growing abnormally, you might need to run queries, such as those above, over a few time periods to understand where the load is.
April 9, 2019 at 3:57 pm
Is there anyway related which is causing to tempdb full. Kindly request to help me on this. Thanks, Arooj
Arooj,
if your tempdb files are configured to autogrow and "default trace" is running, it is possible to find out who and when caused tempdb to grow up to the limit.
Default trace contains events "Data/Log File Growth".
https://www.sqlservercentral.com/articles/default-trace-a-beginners-guide
April 10, 2019 at 12:49 am
Yes, its growing abnormally. Due to space issue on temp drive , we thought tempdb require more space so we asked server team to uplift the space . They have added 200 GB but still the issue is remain same . Now, tempdb consuming all 400 GB of space. we are worrying , why this is growing because every time we can't ask to extend the space and we have other instance as well on that server but their tempdb is working fine.
April 10, 2019 at 3:16 am
Andrey,
I checked default trace and found missing stats in tempdb:
"NO STATS:([tempdb].[dbo].[#calculateRequestNewResourceNestedSetStatementTransitionsNonMemberSet_______________________________________________0000000E4593].[SetKey])"
Is this a causing the issue? Please confirm.
April 16, 2019 at 12:21 am
I could find , one of the table is having million of records and when we tried to open index fragmentation for same table sql server is not responding and after sometime its shows nothing.
Index rebuild never be success because of this table and it gets failed due to insufficient disk space but we have around 200 GB free space out of 1.18 TB.
Is there any issue on this table. Is this reason to get tempdb full. please help to find out cause. Thanks in advance!
Arooj Ahmad
April 17, 2019 at 2:43 pm
Andrey, I checked default trace and found missing stats in tempdb: "NO STATS:([tempdb].[dbo].[#calculateRequestNewResourceNestedSetStatementTransitionsNonMemberSet_______________________________________________0000000E4593].[SetKey])" Is this a causing the issue? Please confirm.
Arooj, hi
I'm pretty sure that this finding doesn't relate to the issue.
this message tells us that there's missing statistics on column SetKey of table #calculateRequestNewResourceNestedSetStatementTransitionsNonMemberSet
April 17, 2019 at 2:50 pm
I could find , one of the table is having million of records and when we tried to open index fragmentation for same table sql server is not responding and after sometime its shows nothing. Index rebuild never be success because of this table and it gets failed due to insufficient disk space but we have around 200 GB free space out of 1.18 TB. Is there any issue on this table. Is this reason to get tempdb full. please help to find out cause. Thanks in advance! Arooj Ahmad
Arooj
This table either causes the problem with tempdb (50%), or not (50%).
It depends a bit on do the issue with tempdb and your "open index fragmentation" happen in the same time.
If you rebuild the index with option "sort_in_tempdb", then it can be a reason.
Anyway, there are two ways to get to the bones : long way (guessing) and short way (monitoring, see post of Chris Harshman )
Regards,
Andrey.
April 17, 2019 at 5:01 pm
You may want to check the article and see if the issue applies in your case - make sure to read the beginning and query sys.dm_db_task_space_usage. There is a CU to apply if it applies:
Sue
May 9, 2019 at 3:10 pm
I had this problem a while back and it was down to a badly written Service Broker.
Which was setup as a fire and forget. The initiator hangs up before the the receiver has told it it has completed. So this small bit of information will hang about in memory before being flushed to TEMPDB and will never be deleted unless you restart the SQL service.
select * from sys.dm_db_task_space_usage
where internal_objects_alloc_page_count <> 0
See if there is a SPID with high allocation and low deallocation.
If its wait is usually something like Broker _FLUSH from time to time.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply