July 14, 2015 at 6:49 am
In SQL Server Management Studio, it is possible to do the following:
a) In Object Explorer, connect to a particular SQL Server Database Engine, let's call it X.
b) Use that connection to generate many SQL queries, connected to X, in the right-hand pane.
c) In Object Explorer, connect to a particular SQL Server Database Engine, let's call it Y.
d) Use that connection to generate many SQL queries, connected to Y, in the right-hand pane.
e) Close the connection to X, which was created in step (a), from within Object Explorer.
In the right-hand pane, I am now left with a bunch of queries connected to X, and a similar bunch of queries connected to Y.
Does anyone know of a quick way to shut all the queries connected to X, but none of the queries connected to Y?
This question can assume real practical importance if server X is a Live Production Server, and server Y is a Test Server, and
my next job of the day is to run a change script against server Y....
I have looked for an option in Management Studio to "Close all queries connected to Server X", but haven't found one.
July 14, 2015 at 6:58 am
Quick suggestion, run an instance of ssms for each server.
😎
July 14, 2015 at 7:01 am
Eirikur Eiriksson (7/14/2015)
Quick suggestion, run an instance of ssms for each server.😎
This.
Enforce it by using different windows logins for the two servers. Then one instance of SSMS is run as one login, the other run as a second login. Include the coloured footers for different servers, red for production is one I quite like
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2015 at 7:03 am
Dear Eirikur,
Thanks for the suggestion. However, I make extensive use of Redgate's SQL Server Source Control, which will only work on one instance of Management Studio at a time.
Best wishes
Mark Thornton
July 14, 2015 at 7:26 am
MarkThornton (7/14/2015)
Dear Eirikur,Thanks for the suggestion. However, I make extensive use of Redgate's SQL Server Source Control, which will only work on one instance of Management Studio at a time.
Best wishes
Mark Thornton
I've had the same issue with RG SSSC, tried few workarounds, ended up with using an additional instance of SSMS for the SSSC and a lot of copy/paste
😎
July 14, 2015 at 7:36 am
There is a way to kill all the users attached to a specific database, but it wouldn't close the query windows and it would kill other users unless you told it to look for a specific login. EDIT: Let me know if you want me to post that code.
You'd still end up having to close the individual query windows after the fact.
July 14, 2015 at 8:20 am
MarkThornton (7/14/2015)
Dear Eirikur,Thanks for the suggestion. However, I make extensive use of Redgate's SQL Server Source Control, which will only work on one instance of Management Studio at a time.
Best wishes
Mark Thornton
OK that makes sense. But why do you need to have two instances open? If you are deploying to production you should have already done your testing so having that connection open is just making it easier to make mistakes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply