TempDB data file size increasing.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

TempDB data file size increasing.

Author

Topic

Martyn123
Starting Member

29 Posts

Posted - 2011-08-19 : 03:25:17

Hi,

I am facing the high tempdb datafile space usage even though all the temp tables are dropped properly.

Following are the output after running various dmvs on tempdb

sys.dm_db_file_space_usage

output:usr_obj_kb internal_obj_kb version_store_kb freespace_kb
3648 9311296 0 95537984

**all the space is taken up by internal objects

SELECT top 1 *
FROM sys.dm_db_task_space_usage

sessionid databaseid internal_object_alloc_page_count
12 2 1163912

internal_object_dealloc_page_count
116

**when I checked the name of the task which is not deallocating the pages by using the following query:

use master
go

exec sp_who2 '12'

status dbname command diskio lastbatch
background master BRKR TASK 230363 08/11 09:27

So please someone help me out in resolving this issue; if at all service broker is the real culprit and why it is not deallocating the allocated pages and how to proceed further.

Thanks
Martyn

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 04:26:47

Doesn't help with finding and fixing the culprit, but Stop/Start SQL Service should (check that it does!) return TEMPDB to the configured initial size.

Sachin.Nand

2937 Posts

Posted - 2011-08-19 : 05:05:31

Have a look here.

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-sb/100/Service-Broker-tempdb-filling-up

Looks like you have a lot of orphan conversations.

PBUH

Martyn123
Starting Member

29 Posts

Posted - 2011-08-19 : 06:38:17

Thanks Sachin for replying to my post.

I have already cleared all the conversations from the table sys.conversation_endpoints as suggested in the link you shared; but I am still getting the same values when I execute the following query:

SELECT top 1 *
FROM sys.dm_db_task_space_usage

sessionid databaseid internal_object_alloc_page_count
12 2 1163912

internal_object_dealloc_page_count
116

why the pages are not getting de-allocated ?

Thanks
Martyn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources