0

With the help of a user I've created a SQL Server Agent Job that stalks a few servers on my network every 10 minutes to collect all connections:

USE [msdb];
GO

DECLARE @ServerName varchar(50), @DynamicSQL NVARCHAR(MAX)
DECLARE @myTableVariable TABLE (id INT, ServerName varchar(50))
insert into @myTableVariable values(1,'TESLABSQL01T'),(2,'TESLABSQL02T')
-- select * from @myTableVariable           -- use thisfor test


Declare VarCursor cursor for
Select ServerName from @myTableVariable
Open VarCursor
FETCH NEXT FROM VarCursor INTO @ServerName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @DynamicSQL='
    INSERT INTO [msdb].[dbo].[ConnectionCounts_Remote]
    SELECT ''' + @ServerName + ''' AS [ServerName]
                 ,NAME AS DatabaseName 
                 ,COUNT(STATUS) AS [NumberOfConnections]
                 ,GETDATE() AS [TimeStamp]
                 ,hostname
                 ,program_name
                 ,loginame
    FROM '+@ServerName+'.master.sys.databases sd
    LEFT JOIN '+@ServerName+'.master.sys.sysprocesses sp ON sd.database_id = sp.dbid
    WHERE database_id NOT BETWEEN 1 AND 4
    GROUP BY NAME,hostname ,program_name ,loginame'
    

    EXEC (@DynamicSQL)

    FETCH NEXT FROM VarCursor INTO @ServerName
END
CLOSE VarCursor
DEALLOCATE VarCursor

So easy and so beautiful, the query connects every 10 minutes to TESLABSQL01T and TESLABSQL02T.

The problem is that on these remote servers if I execute sp_who2 I can still the users connected as DORMANT. So this means that my server is stalking those servers every 10 minutes but the connection is maintained.

How can I kill that connection not form the remote server but from the local server where the SQL Agent Job is executed?

Basically I'm trying to create a query that kill itself after is executed.

Any suggestion is welcome.

Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
  • A query cannot kill itself but you could easily implement something that would kill a dormant process in a similar fashion to what you are already doing. – Stu Jun 29 '21 at 21:09
  • @Stu, no, because I'm sending the connection from Server1 but the connection is dormant on server2. How to do that? – Francesco Mantovani Jun 29 '21 at 21:17
  • A few ideas spring to mind. you're already using sysprocesses, if you use a specific loginame you can look for its dormant spid, or use sql_handle to identify the specific query; you could have a job on the remote server to poll regularly for it and kill it, or you could have a procedure on the remote server that you call remotely to do it. – Stu Jun 29 '21 at 21:23
  • @Stu, that is better. I taught about it but that means that I have to create stuff on remote servers which maybe are on production... and you know how is not good to create stored procedures here and there because then you forget them. That's why I have a remote query and I want to kill it remotely – Francesco Mantovani Jun 29 '21 at 21:39
  • 1
    You want to kill a process on the server, you do that using `kill` which, to the best of my knowlege, cannot be run remotely. You can *query* from `master.sys.sysprocesses` over a linked server so the best I think you can do is remotely call a procedure and pass it the `spid` to kill. That's probably the best you're going to be able to do. No such thng as a free lunch etc. – Stu Jun 29 '21 at 21:43
  • By the way, you should use `QUOTENAME(@ServerName)` – Charlieface Jun 29 '21 at 23:39
  • @Stu, you mean for `@myTableVariable`? how cna I do that? I have multiple servers to add, not only one – Francesco Mantovani Jun 30 '21 at 08:59
  • 1
    I mean, each server you connect to will have a simple procedure (either in `master` or a database of utility functions if you use such a concept), this will take the `spid` to kill, then you can dynamically construct a FQN and call it in your loop where you get your counts. If you are doing that I would just pass the *login* name instead and have the proc kill any spid for that login which is *dormant*. – Stu Jun 30 '21 at 09:07

0 Answers0