Tempdb Problem

Question

Tempdb Problem

Viewing 5 posts - 16 through 20 (of 20 total)

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

adrian.sudirgo SSC-Addicted Points: 427 More actions · Answer 1

michael vessey (5/18/2012)
also try looking to see how many conversations are still open
select * from sys.conversation_endpoints
maybe your application is not closing the conversations after the messages.
since you've got nothing on the queues, if there are lots of rows in conversation_enpoints then that would be the issue...(speculating here by the way)

Mike,

I tried running select * from sys.conversation_endpoints and it actually contains 5,698,396 rows of records! Took 14 minutes to run the query.

I copy paste 1 row as an example of the data (since I cannot paste table, I paste it downwards):

conversation_handle: 6F8E29D0-398C-E011-ABAA-0030482A0F25

conversation_id: 5866E54C-77C2-448F-87FA-000000AA4773

is_initiator: 0

service_contract_id: 65536

conversation_group_id: 6E8E29D0-398C-E011-ABAA-0030482A0F25

service_id: 65537

lifetime: 2079-06-19 13:42:06.030

state: DI

state_desc: DISCONNECTED_INBOUND

far_service: storeresult_svc

far_broker_instance: 0064A15F-5C18-44A2-B20C-B4734B604426

principal_id: 1

far_principal_id: 1

outbound_session_key_identifier: 949BD1A1-A8D3-4206-B0E9-701BF16AC569

inbound_session_key_identifier: 02D6C2F5-B53F-4CB8-BD59-1FC737049AE7

security_timestamp: 2011-06-01 10:58:30.107

dialog_timer: 1900-01-01 00:00:00.000

send_sequence: 0

last_send_tran_id: 0x000000000000

end_dialog_sequence: -1

receive_sequence: 2

receive_sequence_frag: 0

system_sequence: 0

first_out_of_order_sequence: -1

last_out_of_order_sequence: 0

last_out_of_order_frag: 0

is_system: 0

So, is this the cause of the problem?

What should I do? Delete it?

Thanks,

Adrian

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

bingo - here is your problem

your service broker conversations are not being closed, this puts memory pressure on and forcessql to dump these to tempdb - sql does not know if you intend to use them, so it won't dispose of them... simply caches them to tempdb

you need to issue the command

;END CONVERSATION 'FAC8DDF9-C29D-E111-BE26-FC63A9038E0C'

(insert your conversation handle into the quotes)

i normally do this if i have made a booboo in my code and you can script up the ends (you have to close aeach of them individually i think)

so i use the query

select top 10000 ';end conversation '''+convert(varchar(100),conversation_handle)+'''' from sys.conversation_endpoints

copy the results and execute them.... repeat until there are no conversations

then fix your service broker code and ensure it closes the conversation when it is done

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

oh - also

the state of those conversations

state_desc: DISCONNECTED_INBOUND

i think (and i may be incorrect) that the initiator of the conversation is not closing the conversation - which is why you have the problem

see this post

http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

http://books.google.co.uk/books?id=fx4JM99vKZ8C&pg=PA53&lpg=PA53&dq=state_desc:+DISCONNECTED_INBOUND&source=bl&ots=IpRTThulJv&sig=1N3DXono1r6jUCnNMQIPrFbPZ6s&hl=en&sa=X&ei=tWC2T6zYM8TbsgbD-6GXCA&ved=0CFMQ6AEwBQ#v=onepage&q=state_desc%3A%20DISCONNECTED_INBOUND&f=false

MVDBA

adrian.sudirgo SSC-Addicted Points: 427 More actions · Answer 5

That solved my problem! 🙂

Thanks a lot Mike and the others for helping me.

Regards,

Adrian