March 13, 2008 at 12:48 pm
Hi there.
I am investigating a new SQL Server, and one thing I see in Performance Monitor is there are 19 active transactions! This seems to me to be abnormally high...
But when I restart the machine, even restart the MSSQLSERVER service... "Transactions" goes right back up to 19!
Any reason for this?
I want for there to be no persistent transactions. But I don't see how to open up a Query as sa and run a "ROLLBACK ALL TRANSACTIONS" statement.
Any advice? Or am I barking up a dead tree?
Thanks a bunch.
Jason
March 13, 2008 at 1:02 pm
I also want to mention...
there are NO active processes in the Activity Monitor. So there's nothing to kill. Just my two connections for little old sa - one for the management studio, one for the activity montior.
March 13, 2008 at 1:24 pm
If you're sure you want to roll stuff back...just type ROLLBACK in a query, and keep hitting execute until it whines at you....
Of course - you could also try committing them first. Same thing as above - just use COMMIT.
"whining" sounds a little like
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 1:32 pm
keep in mind sqlserver needs some background processes to manage the instance/sqlagent/.....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 13, 2008 at 1:43 pm
Matt Miller (3/13/2008)
"whining" sounds a little like
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Yes, that is what I see the first time I execute "ROLLBACK TRANSACTION".
So... there are no transactions that can be rolled back.. but then why does Perf Mon show 19 persistent transactions??
As for the SQL processes.. I've stopped SQL Agent & as much as possible. I didn't kill spid 1 or 2, just everything over 50 except for Management Studio and Activity Monitor.
Thanks guys. Any other ideas?
Jason
March 13, 2008 at 1:50 pm
Then they're not user transactions. Check out what ALZDBA pointed out. It's background stuff.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 2:28 pm
Matt Miller (3/13/2008)
Then they're not user transactions. Check out what ALZDBA pointed out. It's background stuff.
I can accept that.. as long as they are not interfering with other SQL Server operations, then I'm cool with it.
They don't seem to be causing entries on sp_lock, or in sys.dm_tran_locks. In fact, the only system process holding any locks is the CHECKPOINT spid.
Thanks guys, I think I can move on to the next issue now.
Jason
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply