TRAN issue...

[BradF]

I have a SP that will not work when I have the queries in it surrounded by...

BEGIN TRAN

blah - blah - blah

IF @ERROR ROLLBACK TRAN

COMMIT TRAN

The proc seems to run indefinitely and there are tons of locks on the database it's using. If I remove all the TRAN lines (comment them out) the PROC runs in less than 3:00 minutes.

I'm thinking it's some sort of corruption but I need some evidence to present to my superiors.

Anyone have any ideas?

 

[SQLDenis]

>>IF @ERROR

IF @ERROR what? =0

BTW you need to store that in a variable then check it after every DML statement

read

http://www.sommarskog.se/error-handling-II.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[BradF]

Dennis -- I did not put in the full code because I assumed those responding would assume I knew what I was doing...

IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN 1
END

Is the exact block of code for that section.

Thanks for your concern over my methods however that's not my problem. I outlined what the problem was.

 

[SQLDenis]

like I said, this is incorrect if you have multiple DML statements

IF @@error <> 0

please show the whole proc

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Crowley16]

sounds like you're holding lots of locks and your sp is most likely deadlocking...

has this sp previously worked fine and suddenly stopped or is this a new sp?

I've seen this happen when indexes haven't been setup properly (conflicting and duplicate indexes...) and as soon as the tables get over a certain size, locks suddenly starts to appear.

--------------------
Procrastinate Now!

 

[BradF]

If I do the following, the SPROC runs fine:

--BEGIN TRAN --commented out

blah blah blah

IF @@ERROR <> 0
BEGIN
--ROLLBACK TRAIN --commented out
RETURN 1
END

--COMMIT TRAN --commented out

 

[BradF]

Crowley, thanks for the helpful response. The SPROC had been running fine for the past year and just recently started having problems. I've looked over the indexes and they seem to be okay. It does appear to be a deadlock as you said.

I had noticed some of the indexes on one of the tables had gotten deleted by someone. I recreated them and the SPROC was working again. However a week later, it was back to no good again (DEADLOCKING). The indexes are still there.

Furthermore, the SPROC has always ran well on our development server. However as soon as I refreshed from Production down to Development, the SPROC stopped working (doing what it was doing on Production)...

 

[BradF]

Could it be related to the LOG files?

 

[SQLDenis]

>>The SPROC had been running fine for the past year and just recently started having problems

it would helped if you said that right away at the beginning


>>It does appear to be a deadlock as you said.

enable Trace Flag 1204 so that you can log this

also update the stats (do you have auto stats on?), they might be out of date, how is the fragmentation level, run dbcc showcontig


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[BradF]

Nothing has helped so far. Everything seems to be okay with the defragmentation and statistics.

This proc is very simple. It simply truncates two tables and reloads summary data into them.

As I said, everything works fine and fast except when I surround the query inside the SPROC with a TRAN block. Running the query by itself in QA finishes in 2-4 minutes, as does running the SPROC without the TRAN block.

Other SPROCS in this application with TRAN blocks run fine. I've tried sp_recompile on the SPROC as well with no luck. I also rebuilt the indexes.