There are a few ways to do this but here’s one that I like. I believe it works in SQL Server 7, 2000 and 2005.
ALTER DATABASE myDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE myDatabaseName
GOGo ahead and do what you need to do then set it back:
ALTER DATABASE myDatabaseName SET MULTI_USER
Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.
Ah thanks! Worked well enough on sqlsrv2k.
Saw that noone left any comment so I’ll just leave this here :)
/K
Thanks, this is exactly what I was looking for!
This is a great and easy way ! Thanks a lot
There's a slight danger to doing this if you have a persistent connection. It is unlikely, although possible that another connection might grab the single user connection, effectively locking you out of the database. If that happens, there's a simple way to steal it back.
Run:
use MyDatabaseName
go 1000
and then go kill the offending session or sessions. Sooner or later, your query will grab the connection.
Cheers.
haha, nice trick, Ken!