May 24, 2012 at 9:09 am
Hi All,
Is it possible to set the transaction isolation level for a specific login? I.E. If a support team connects to a DB all queries they write use READ UNCOMMITTED?
Cheers,
May 24, 2012 at 9:31 am
You could set up a login trigger to fire a proc on login that contained appropriate logic. However, any other procs that were executed by a login may have code in them that resets the isolation level...
_________________________________
seth delconte
http://sqlkeys.com
May 24, 2012 at 9:46 am
If this is to make it so that support staff don't block application usage I would recommend you start with education. Even without blocking "select * from dbo.reallybigtable" can cause performance issues. If you're in a situation where there are some queries that support needs to run that can't be run without significant blocking add statements to templates for that query that will handle it (I would use nolock).
If modifying the isolation level upon login is what you need to do you may also want to consider source application as a method of identifying which sessions should have which isolation level. And you'll want to keep in mind whether or not the application does passthrough authentication so if support is using the app they're still using the proper isolation level.
May 24, 2012 at 2:37 pm
I'll second, third, and fourth that...education, education, education!
If that is a lost cause, I know it was in some shops where I have been, you might look into enabling READ_COMMITTED_SNAPSHOT isolation. Do your homework though and make sure your tempdb is setup correctly to handle it, and that you monitor the version store.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 25, 2012 at 3:00 am
Thanks for the responses guys, i agree with the education route - unfortunately our offshore support teams sometimes "forget" to stick to process :S
May 26, 2012 at 9:14 am
Another option is to not allow them to write their own queries. Give them access to only execute stored procedures that you have vetted.
If they need something else, then they would have to request a new procedure be built which would have the necessary protections built in.
Or, you could provide a copy of the production system they can run their queries against. In most cases, they wouldn't need access to real-time data. If they need access to real-time data you could setup replication and grant them access to the replicated database.
If they don't need real-time access you could use database snapshots (Enterprise Edition), mirroring with database snapshots, backup/restore to another server or SAN replication/mirroring/snapshots.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply