Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CDC and sys.dm_db_task_space_usage.internal_object_alloc_page_count Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 4:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:48 PM
Points: 228, Visits: 554
Gurus -

I have CDC setup (2008 R2) and the job is set to run continuous. The job calls the cdc scan procedure with default parameters
maxtran = 500
maxscans = 10
continuous = 5
pollinginterval = 5

Attached are the properties of the sys.dm_db_file_space_usage and sys.dm_db_task_space_usage and sys.dm_db_session_space_usage.

In "sys.dm_db_task_space_usage" the "internal_object_alloc_page_count" column increases gradually and the "internal_object_dealloc_page_count" doesnot change.

This intern means that the session uses space in the tempdb that gradually increases. It used to be in MB's now have moved to GB's.
But this doesnot add up to the output from sys.dm_db_file_space_usage (429840 in dm_db_task_space_usage is much higher that the sum of the four rows for internal_object_reserved_page_count column in sys.dm_db_file_space_usage) -Yes I have 4 tempdb's

Questions:-
1) Why is the dealloc not happening for the CDC job - (It deallocates for other processes that are continuously running like replication jobs in our system)
2) Why is the counts not making sense

Appreciate help understand!

Thanks






  Post Attachments 
CDC and sys.dm_db_task_space_usage.internal_object_alloc_page_count.gif (7 views, 28.94 KB)
Post #1376722
Posted Monday, October 29, 2012 12:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:48 PM
Points: 228, Visits: 554
Any Thoughts ?
Post #1378399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse