How can I tell if my database has Implicit Transactions On?

Question

How can I tell if my database has Implicit Transactions On?

nicholasr 68909

SSC Veteran

Points: 246
More actions
November 8, 2011 at 1:28 pm

#259650

Hello,
I am new at my company and I am trying to determine if implicit transactions is turned on? How can I do this via a command or the ui?
Thanks,
Nick

Viewing 8 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Implicit transactions isn't a database-level setting. It's a client setting, so client apps would enable it (or not) when they connect.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nicholasr 68909 SSC Veteran Points: 246 More actions · Answer 2

Isnt ANSI SETTINGS ON database wide? It is my understanding that setting would turn on implicit transactions.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

No such option as Ansi Settings. The various Ansi set options and implicit transactions are client settings. They're set (or not) when a client connects.

There's an sp_configure setting (user options) that sets defaults for connecting clients, but they can still override that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nicholasr 68909 SSC Veteran Points: 246 More actions · Answer 4

I might be misunderstanding something but when setting ANSI_DEFAULTS to on I would be also setting implicit transactions on which from what I am reading here is a server setting: http://msdn.microsoft.com/en-us/library/ms188340.aspx

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 5

nicholasr 68909 (11/9/2011)
I might be misunderstanding something but when setting ANSI_DEFAULTS to on I would be also setting implicit transactions on which from what I am reading here is a server setting: http://msdn.microsoft.com/en-us/library/ms188340.aspx

You should look into this article.

SET IMPLICIT_TRANSACTIONS (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms187807.aspx

Ken McKelvey SSCoach Points: 18944 More actions · Answer 6

This article may be of interest:

http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

nicholasr 68909 (11/9/2011)
I might be misunderstanding something but when setting ANSI_DEFAULTS to on I would be also setting implicit transactions on which from what I am reading here is a server setting: http://msdn.microsoft.com/en-us/library/ms188340.aspx

That page is bloody confusing.

It's a SET option, so it's a client setting. The server wide setting would be the sp_configure 'user options' which just sets the defaults for connections that clients can overrule.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass