kill all user connections in SQL Azure

0 votes
asked Sep 27, 2010 by rabbi

I need to alter a table and the Sql Azure just spins its wheels.

I think its because there is a connection somewhere that is accessing that table.

How can I see who is accessing my tables and how can I terminate their connections.

I am looking for something similar to

ALTER DATABASE xxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

that will work with sql azure

4 Answers

0 votes
answered Sep 3, 2010 by jp-alioto

You can use sys.dm_exec_connections to view information about connections to a SQL Azure DB. Example here under Monitoring Connections. You could block the errant connections at the SQL Firewall level or deny them access to the table you care about with the security stored procedures.

0 votes
answered Jan 27, 2012 by benjguin

Any valid alter database should drop connection. For instance, you can change the size ALTER database demo MODIFY (MAXSIZE=5 GB)

0 votes
answered Jan 7, 2016 by sirisha-chamarthi

SQL DB V12 supports setting database into restricted user mode (http://www.sqlindepth.com/alter-database-in-sql-v12/). You can also kill the connections using kill command. Alternatively update the firewall rules just to allow your IP.

0 votes
answered Sep 15, 2017 by johnstaveley

Deletes all user sessions on a database except your own (which you aren't allowed to do)

DECLARE @mySessionId INT
DECLARE @dbName VARCHAR(50)
SET @dbName = '<Your database name>'
DECLARE @spid int
CREATE TABLE #UserSessions (spid SMALLINT, ecid SMALLINT, [Status] NCHAR(30), loginname NCHAR(128), hostname NCHAR(128), blk CHAR(5), dbname NCHAR(128), cmd NCHAR(16), request_id INT)
INSERT INTO #UserSessions ([spid], ecid, [Status], [loginname], [hostname], blk, [dbname], cmd, request_id) EXEC dbo.sp_who

SELECT @spid = min(spid) FROM #UserSessions WHERE NOT hostname = HOST_NAME() AND dbName = @dbName AND [status] = 'sleeping'
WHILE @spid IS NOT NULL
    BEGIN
    EXECUTE ('KILL ' + @spid)
    SELECT @spid = min(spid) FROM #UserSessions WHERE NOT hostname = HOST_NAME() AND dbName = @dbName AND spid > @spid AND [status] = 'sleeping'
END

DROP TABLE #UserSessions
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...