Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
One of the things I find myself doing often with demos is dropping databases. I may create and drop databases often to try some technique out or practice a skill. I’ve done this for years, going back to SQL Server 4.2, using small databases as quick lab spaces.
A common problem for me is that often a database is in use, often because of another connection from ADS or SSMS. This is actually a common problem for many people in teams as well, when someone else might be holding an open connection. If you’ve tried to run a simple DROP DATABASE, you know this doesn’t work. In the SSMS GUI, we have a “Close existing connections” option, but nothing with the DROP DATABASE command.
The best solution I’ve found is on StackOverflow. Set the db to single user, which allows you to use the WITH ROLLBACK IMMEDIATE option. This terminates any existing connections and rolls back their transactions.
Here’s the syntax:
ALTER DATABASE ClientDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
If I do that, I can then run this, without any issues.
DROP DATABASE ClientDemo
SQLNewBlogger
This is a fairly simple task, and one I’ve done often. I’ve never written about it, mostly because I never took the time. Solving this issue was something that took about 5 minutes of Internet research. I had to check that the DROP command hadn’t added the option. I know this has been discussed, but apparently, never added.
In any case, since I had to do this for work, I made a new notes, snapped a screenshot, and saved a few links. I do this with OpenLiveWriter. Later I took 10 minutes and typed this up, including this section on how and why.
This is a quick way to show some knowledge, problem solving, cement learning, and give the next interviewer something to ask you about. Give it a try.