Enabling CLR Integration

CLR (Common Language Runtime) Integration

Microsoft SQL Server now features the integration of the common language runtime (CLR) component of the .NET Framework for Microsoft Windows. The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. For SQL Server users and application developers, CLR integration means that you can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#. Note that Visual Studio .NET 2003 cannot be used for CLR integration programming. SQL Server includes the .NET Framework version 2.0 SP1 pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.

The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

You can disable CLR integration by setting the clr enabled option to 0. When you disable CLR integration, SQL Server stops executing all CLR routines and unloads all application domains.

Note
To enable CLR integration, you must have ALTER SETTINGS server level permission, which is implicitly held by members of the sysadmin and serveradmin fixed server roles.

Note
Computers configured with large amounts of memory and a large number of processors may fail to load the CLR integration feature of SQL Server when starting the server. To address this issue, start the server by using the -g memory_to_reserve SQL Server service startup option, and specify a memory value large enough. For more information, see Using the SQL Server Service Startup Options.

Note
Common language runtime (CLR) execution is not supported under lightweight pooling. Before enabling CLR integration, you must disable lightweight pooling. For more information, see lightweight pooling Option.

SQL Server – Alter database in Single User mode to Multi User mode

Refereed from

https://myadventuresincoding.wordpress.com/2014/03/06/sql-server-alter-database-in-single-user-mode-to-multi-user-mode/

We have some test environment databases that get rebuilt nightly. The job that does the rebuild always switches the database into single user mode when it is being rebuilt. When the rebuild is finished it will switch it back to multi user mode. However, if there was a problem with a script and the job failed, it will leave the database in single user mode.

First, open a SQL Server Management Studio query window connected to database “master”.

The command to change the database back to multi user mode is:
ALTER DATABASE {InsertDatabaseNameHere} SET MULTI_USER WITH ROLLBACK IMMEDIATE GO

However, if there is an existing process blocking this alter, you may get the error message:

“Transaction (Process ID 864) was deadlocked on lock resources with another process and has been chosen as the deadlock victim”

Since both the existing running process and the current request have the same deadlock priority, preference is given to the longer running process which is why your alter database command is chosen as the deadlock victim. So to deal with this when you run the command again, set the deadlock priority to HIGH. However, if that does not work, you will need to kill the existing connection.

To find the “spid” of the existing connection, you can use the following query:

SELECT sd.[name], sp.spid, sp.login_time, sp.loginame

FROM sysprocesses sp

INNER JOIN sysdatabases sd on sp.dbid = sd.dbid 

WHERE sd.[name] = 'DatabaseNameGoesHere'

As an alternative, you can also use the command “sp_who” to get the “spid” of the open connection:

exec sp_who

So, here is our revised command:

KILL SpidToKillGoesHere

GO

SET DEADLOCK_PRIORITY HIGH

GO

ALTER DATABASE DatabaseNameGoesHere SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

That should do it!