August 26, 2014 at 6:53 am
Hi,
How can I reclaim the TempDB space and prevent sys.sp_MSadd_distribution_history from using up so much TempDB?
Using Michael Valentine Jones's queries I found that Internal Objects are using a lot of TempDB space.
Results (Pivoted)
[Version Store Pages Used]2,592
[Version Store Space MB]20
[User Object Pages Used]704
[User Object Space MB]6
[Internal Object Pages Used]15,185,504
[Internal Object Space MB]118,637
[Unallocated Pages Used]1,168,808
[Unallocated Space MB]9,131
[Total Pages]16,357,608
[Total Space MB]127,794
And
[Version Store %]0.016
[User Object %]0.004
[Internal Object %]92.835
[Free Space %]7.145
[Total Pages]130,860,864
Nagaraj Venkatesan's more detailed query gave me these results.
(Pivoted. Only showing 1 of the 5 SPIDs. The rest are pretty much the same.)
SPID100
internal_objects_alloc_page_count1,723,696
internal_objects_dealloc_page_count1,853,176
last_request_start_time8/26/2014 11:56:30
last_request_end_time8/26/2014 11:56:30
login_time12-08-2014 20:48:36
cpu_time314,627
memory_usage2
reads524
writes378,758
logical_reads23,635,695
TextCREATE PROC sys.sp_MSadd_distribution_history
client_net_address<local machine>
program_nameReplication Distribution History
statussleeping
TempDB only has 1 data file.
It was default size. (8MB 1MB growth and 1MB 10% growth). (Yes, I know....)
Now 127802 MB 8000 MB growth and 1000 MB 1000 MB growth. (Still a single file)
We are doing transactional replication from 1 publisher (Also distributor) to 3 subscribers in the same datacenter.
Publisher data does not change too much. (Customer data and configurations)
Latency is consistently low.
Any idea's on how to tackle this without restarting the service?
Cheers
August 27, 2014 at 3:47 am
I'm not sure if the queries I am using are accurate.
Can anyone vouch for them?
EXEC sp_SpaceUsed
[database_name][database_size][unallocated space]
tempdb128801.31 MB127796.11 MB
[reserved][data][index_size][unused]
6984 KB3632 KB1576 KB1776 KB
Judging by these results, I should be able to reduce the data file size.
How can I confirm which internal processes are holding on to the TempDB space?
September 11, 2014 at 8:51 am
Turns out Service Broker was the culprit.
Having ended all the disconnected conversations, the TempDB did NOT release internally reserved pages.
http://www.sqlservercentral.com/Forums/Topic1599547-1550-1.aspx?Update=1
Does anyone know how I can force SQL to release the internal pages? (Without restarting the service)
Also, is it normal for a system spid to have a negative UserPageCount?
SvrNamespidcmdlastwaittypeInternalPageCountInternalUsageMBUserPageCountUserUsageMB
RTL_Server13BRKR TASK BROKER_TO_FLUSH 729928057025-1160
BO_Server10BRKR TASK SLEEP_TASK 13053880101983-962-7
BO_Server18BRKR EVENT HNDLRBROKER_EVENTHANDLER 153271211974-1190
Cmd "BRKR TASK" alternates between lastwaittypes "BROKER_TO_FLUSH" and "SLEEP_TASK"
Code used to produce the above results. (2 different servers)
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
Thanks
November 25, 2015 at 8:41 am
Problem's there again. Googling brought me back to my own post
Last time the SQL service was restarted to reduce the size. That happened during a major version update. No updates coming up for a while.
SELECTTOP (3)
session_id,
AllocPages= internal_objects_alloc_page_count,
DeallocPages= internal_objects_dealloc_page_count,
AllocatedGBs= CAST(internal_objects_alloc_page_count / 128.0 / 1024.0 AS Dec(9,3)),
DeallocatedGBs= CAST(internal_objects_dealloc_page_count / 128.0 / 1024.0 AS Dec(9,3))
FROMsys.dm_db_Session_space_usage
WHEREinternal_objects_alloc_page_count > 0
AND internal_objects_dealloc_page_count > 0
ORDERBY internal_objects_alloc_page_count DESC
/*
session_idAllocPagesDeallocPagesAllocatedGBsDeallocatedGBs
35235674161017135227.21777.601
1502354440634835217.96348.434
39653588015009124.08811.451
*/
SELECTsession_id, [program_name]
FROMsys.dm_exec_sessions
WHEREsession_id IN (150, 352, 396)
/*
session_idprogram_name
150Replication Distribution History
352Replication Distribution History
396Replication Distribution History
*/
Any help would be appreciated
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply