x

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

Santosh gravatar image

Santosh
1 1 1 1

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

2 answers: sort oldest

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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.9k 19 21 74

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

Thanks.

more ▼

answered Nov 20, 2009 at 06:13 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1834
x60

asked: Nov 20, 2009 at 03:27 AM

Seen: 3401 times

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