Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

By accident, I issued in the region of 500,000 individual queries against my database. It's been running for 6 hours now. When I restart the server, they continue to connect and run. I need them to stop - how can I do this?

share

migration rejected from stackoverflow.com Jul 22 '13 at 1:55

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as unclear what you're asking by Mark Storey-Smith, ypercube, StanleyJohns, Paul White, Jon Seigel Jul 22 '13 at 1:55

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Just to be clear, are you saying that you aren't sending these queries anymore, you've restarted SQL Server, and the queries are still showing up as active in some tool? Are they in a ROLLBACK state? –  James Jul 21 '13 at 13:30
2  
@smithy Further to the comment above restarting the server will have aborted any batches executing at that time. It won't continue with the inserts as you seem to be assuming. –  Martin Smith Jul 21 '13 at 13:54
    
@smithy Yes it sounds like you either a) have a client app that is still sending these queries, b) the queries are in ROLLBACK or c) I'm completely misunderstanding what you wrote. It's generally best to flesh these questions out more with evidence and examples so that people know they've understood you. –  James Jul 21 '13 at 17:50
    
Maybe I misunderstood the issue. If some application keeps these queries queued up and sends them as soon as the server is back online sounds like you'll need to provide details about the application. –  Martin Smith Jul 21 '13 at 18:32

1 Answer 1

This will kill every connection to your instance that has a running request and is connected to master. Obviously change @MyDBName to your database name. You will probably have to wait a bit for the killed processes to roll back.

DECLARE @sql nvarchar(max)
DECLARE @MyDBName nvarchar(max)
SET @MyDBName = 'master'

SELECT @sql = (SELECT 'KILL ' + cast(session_id AS varchar(20)) + '; '
FROM sys.dm_exec_requests
WHERE db_name(database_id) = @MyDBName
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')

--PRINT @sql
EXEC sp_executesql @sql

Now this won't stop new queries from coming in. If you have some process that is adding new queries you will have to give us some details on that for us to help you.

EDIT: Here is also a version to pull for a specific user.

DECLARE @sql nvarchar(max)
DECLARE @MyDBName nvarchar(max)
SET @MyDBName = 'master'

SELECT @sql = (SELECT 'KILL ' + cast(sys.dm_exec_requests.session_id AS varchar(20)) + '; '
FROM sys.dm_exec_requests
JOIN sys.dm_exec_sessions
    ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
WHERE login_name = 'sa'
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')

--PRINT @sql
EXEC sp_executesql @sql

Also based on Aaron's comment you could easily disconnect all connections to a given database like this:

ALTER DATABASE [DBA] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [DBA] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
share
2  
Why loop through and kill sessions individually? Set the database to single_user with rollback immediate. That does all the work you're doing the hard way and prevents new sessions from connecting - never mind that sys.dm_exec_requests might not always accurately reflect sessions that are doing some of their work in that database. –  Aaron Bertrand Jul 20 '13 at 23:51
1  
@AaronBertrand Honestly I didn't think of it. However database was just one possibility. Another option, and to be honest was my original plan, and I'm not quite sure I didn't go with it, was to join sys.dm_exec_sessions and pull for a specific user. –  Kenneth Fisher Jul 21 '13 at 1:27