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.)
SPID 100
internal_objects_alloc_page_count 1,723,696
internal_objects_dealloc_page_count 1,853,176
last_request_start_time 8/26/2014 11:56:30
last_request_end_time 8/26/2014 11:56:30
login_time 12-08-2014 20:48:36
cpu_time 314,627
memory_usage 2
reads 524
writes 378,758
logical_reads 23,635,695
Text CREATE PROC sys.sp_MSadd_distribution_history
client_net_address <local machine>
program_name Replication Distribution History
status sleeping
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]
tempdb 128801.31 MB 127796.11 MB
[reserved] [data] [index_size] [unused]
6984 KB 3632 KB 1576 KB 1776 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://qa.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?
SvrName spid cmd lastwaittype InternalPageCount InternalUsageMB UserPageCount UserUsageMB
RTL_Server 13 BRKR TASK BROKER_TO_FLUSH 7299280 57025 -116 0
BO_Server 10 BRKR TASK SLEEP_TASK 13053880 101983 -962 -7
BO_Server 18 BRKR EVENT HNDLR BROKER_EVENTHANDLER 1532712 11974 -119 0
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
FROM sys.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
ORDER BY (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.
SELECT TOP (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))
FROM sys.dm_db_Session_space_usage
WHERE internal_objects_alloc_page_count > 0
AND internal_objects_dealloc_page_count > 0
ORDER BY internal_objects_alloc_page_count DESC
/*
session_id AllocPages DeallocPages AllocatedGBs DeallocatedGBs
352 3567416 10171352 27.217 77.601
150 2354440 6348352 17.963 48.434
396 535880 1500912 4.088 11.451
*/
SELECT session_id, [program_name]
FROM sys.dm_exec_sessions
WHERE session_id IN (150, 352, 396)
/*
session_id program_name
150 Replication Distribution History
352 Replication Distribution History
396 Replication 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