Search code examples
c#sql-serversmo

Can I force SQL SMO to use READ UNCOMMITTED isolation?

I have an application which is using SQL2008 SMO to grab the SpaceAvailable property of all of the databases, but it often becomes blocked by other processes for long a duration. I have noted that I can run the query it generates in SSMS with no issues in READ UNCOMMITTED isolation.

So, do a way exist to force SQL SMO to use read uncommitted isolation?

Solution

  • Sorry for the late reply to this. Figured I'd post this in in case someone needs help in the future.

    You can set specify the isolation level when you create your Server instance.

    Make sure you have the following references:

    using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.ConnectionInfo;

    And then set the isolation level when creating the connection

    Server theSQLServer = new Server(@"cb-z420vmsasq1\sql12");
        theSQLServer.ConnectionContext.SqlConnectionObject.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);

    You may have to change this for different transactions.