Transaction hanging in SQL Server 2008 running on Windows Server 2003

When I try to execute any SQL statement in Windows server 2003 with BEGIN TRAN and COMMIT TRAN, the query is getting HANG.

If I comment the BEGIN TRAN and COMMIT TRAN, then everything is working fine.

Is there any problem with the MSDTC settings or what could be the issue?

Thanks & waiting for your response.

more ▼

asked Nov 20, 2009 at 03:27 AM in Default

avatar image

1 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

This is SQL Server 2008?

Then I'd suggest you gather data using the dynamic management objects. Start with sys.dm_exec_requests. It'll show you the status of running commands and, most importantly, the exact things that a process is waiting on. With that you can figure out what's going wrong.

Also, while it's possible to connect "up" as it were, running queries against 2008 from the 2000 interface, you're going to run into problems doing this. I recommend strongly against it. Use the correct version and connect "down" but not "up"

more ▼

answered Nov 24, 2009 at 10:13 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

I second the suggestion to try SSMS 2008 if connecting to SQL Server 2008. Not only might it remove some problems, SSMS 2008 is much more feature rich than the 2000 tools.

Jan 19, 2010 at 01:42 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

Can you clarify how long you've waited for the transaction to complete? Also, can you detail:

  • What the query is doing
  • What sp_who2 shows for your SPID while the query is running
  • What steps you've already tried to solve the problem


more ▼

answered Nov 20, 2009 at 06:13 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

Hi, Changed the Msdtc settings , I can you send you that attachment if you share the mail Id

Nov 20, 2009 at 07:03 AM Santosh

You'd be better off posting stuff here, that way more people get to see it...

Nov 20, 2009 at 07:47 AM Matt Whitfield ♦♦

One more thing the problem is coming when executing from the remote Server. . From sqlserver2000 query analyzer trying to execute the query connecting to the SQLserver2008 server giving for ex : SQLSRVR2008.DBNAME

Nov 20, 2009 at 07:57 AM Santosh
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 20, 2009 at 03:27 AM

Seen: 4397 times

Last Updated: Dec 08, 2009 at 11:20 AM

Copyright 2018 Redgate Software. Privacy Policy