How to free up internal objets from tempdb?

Question

Post reply

How to free up internal objets from tempdb?

ricardo_chicas

SSCertifiable

Points: 5024
More actions
September 29, 2014 at 2:01 pm

#299691

Hello all
We have a Sql server 2008 R2 SP2, CU 11, 192gb of ram and 16 cores
The space assigned to tempdb is 200gb, 160 for mdf files, splitted into 16 10gb files and 40gb for the log
When I run this query:
select
reserved_MB=(unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. ,
unallocated_extent_MB =unallocated_extent_page_count*8/1024.,
internal_object_reserved_page_count,
internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.
from sys.dm_db_file_space_usage
I get results like this:
reserved_MBunallocated_extent_MBinternal_object_reserved_page_countinternal_object_reserved_MB
8192.0000002544.812500 714104 5578.937500
The internal objects are consuming most of the space and that will continue to grow until it get to use up all the available space in a couple days
As a test I stopped all the processing at that server and killed all the spids, even with that the space was not released, can anyone please give me an idea about how to recover that space? so far the only "solution" has been restarting the service and of course that is not acceptable
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

arnipetursson SSCertifiable Points: 6557 More actions · Answer 1

When you stopped all processing, were there still any open transactions?

What non-application connections were still there?

Did you look in sys.objects in tempdb?

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 2

Hi

Not a single open transaction, and the objects at tempdb were tiny

I am trying with this approach, blaming the service broker:

SELECT@@SERVERNAME SvrName, P.spid, P.cmd, P.lastwaittype,

InternalPageCount= internal_objects_alloc_page_count - internal_objects_dealloc_page_count,

InternalUsageMB= (internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128,

UserPageCount= user_objects_alloc_page_count - user_objects_dealloc_page_count,

UserUsageMB= (user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128,

--P.waittime,

--P.open_tran,

--P.HostName,

--P.[program_name],

--P.loginame,

--blocked,

--P.cpu,

--P.physical_io,

ISNULL(QT.text, '') TSQL

FROMsys.sysprocesses P

INNER JOIN sys.dm_db_task_space_usage TSU

ON P.SPID = TSU.Session_ID

OUTER APPLY sys.dm_exec_sql_text(P.SQL_Handle) QT

WHERE(internal_objects_alloc_page_count - internal_objects_dealloc_page_count + user_objects_alloc_page_count - user_objects_dealloc_page_count) > 0

ORDERBY (internal_objects_alloc_page_count - internal_objects_dealloc_page_count + user_objects_alloc_page_count - user_objects_dealloc_page_count ) DESC

That returned this:

spidcmd lastwaittypeInternalPageCountInternalUsageMB

13BRKR TASK SLEEP_TASK 987031277111

But I dont know why and what will be the effect on the current processing of something like this:

ALTER DATABASE MyReallyImportantDatabase SET NEW_BROKER

arnipetursson SSCertifiable Points: 6557 More actions · Answer 3

Found an earlier thread: http://www.sqlservercentral.com/Forums/Topic1374671-1281-1.aspx

And this on MSDN:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c4414a6-213b-4a69-8dcd-f162bc979b3b/service-broker-filling-tempdb?forum=sqlservicebroker

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

Did you try to shrink tempdb after killings processes?

That might release the space.

If it still not released, give it more time...it might still be rolling back.

Regards,
SQLisAwe5oMe.