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
GO
Go ahead and do what you need to do then set it back:
ALTER DATABASE myDatabaseName SET MULTI_USER
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!