SharePoint Use Cases

  • Home
  • About
  • Our Tools
  • Contact me
Products

How I dropped all my developers’ SQL Server databases

Toni Frankola - August 25, 2016

This post was originally published on SQLDocKit Blog.

My company Acceleratio Ltd. is an ISV (independent software vendor), which means we make our own software and then sell it to our many customers all over the globe.

We are in the sysadmin business, building tools that help our customers to be more efficient when managing their Windows, Citrix, SharePoint or SQL Server environments.

The key to building great software is to test it thoroughly in every imaginable situation. That means we have to have hundreds of different virtual environments ready for use by our dev and QA teams to test and further develop our software.

We have so many combinations that it is hard to list them all, but we cover everything from Windows Server 2003 to Windows Server 2016, from SharePoint 2007 to SharePoint 2016. On top of that, we also test our tools on various localized products such as French Windows or a German SQL Server.

Successfully manage SQL Server databases with SQLDocKit

To recreate the various challenges faced by our customers, we also have hundreds of databases that are prefilled or used to simulate a very specific problem a customer is facing. As we grow, we have had to find a way to manage all these databases, and that’s how the idea for SQLDocKit was born.

In today’s busy corporate landscape, a developer might “forget” to delete a 100GB test database that no longer needs, and as the years go by, might have many TBs of unused databases lying around, cluttering precious SQL Server and storage resources.

With the latest release of SQLDocKit, we finally have the tool in our arsenal that can free us of all these databases that are no longer needed.

Here is how SQLDocKit can help you do that:

1. Connect to the SQLDocKit Console and jump into Live Explorer.

Go to the Backstage Actions Screen to find the Live Explorer feature.

2. If the tool is already configured, it will show you all of your SQL Servers you on the left-hand side.

The Live Explorer will list all of your databases and their details.

3. Expand the SQL Server or instance you want to explore and choose the Databases node on the right-hand side; the tool will show you the list of all databases.

A visual indicator will be shown, which will allow you to easily distinguish databases that are in the read-only state, in recovery, suspects, etc. Once I got a hold of this list, I took the following steps in order to get rid of some of the databases:

  • Sort databases by size and look at the largest. If these haven’t been used for a while (e.g. two weeks), use the Change State button to make these Read-Only; then alert the owner to check whether she needs this database anymore.
    In the Live Explorer, you can explore SQL Server databases in live mode without having to create a snapshot.
  • Now check databases that have no connections. This could be an indicator that a database is no longer in use. Even if a database has an open connection, that does not necessarily mean it is being used proactively. A connection could be opened by a system job, backup or other action that is no longer needed. Use Show Connections and Kill Connections to view and manage database connections.
    The Change State option lets you change the state of the database from hange the DB state from 'Read/Write' to 'Read Only'.
  • Check for databases whose owner has left the company; try to find the person responsible and use the Change Owner button to transfer ownership if needed.

When it comes to removing databases, we currently have two options available: Detach and Drop. I probably didn’t mention that all of our commands come with integrated support for multiple databases. Want to drop 10 databases with a single click? Yeah, not a problem, we’ve got you covered. And you know what else? On top of that, we also support multiple SQL servers. Want to drop or manage databases from a number of different SQL Servers? You can do that as well.

SQLDocKit bonus options

There is one hidden bonus, you know how in SQL Management Studio sometimes you cannot drop a and get the following error: “Cannot drop database “DB_Name” because it is currently in use. (Microsoft SQL Server, Error: 3702)”, well that’s not going to be a problem if you are going to be using SQLDocKit from now on, as SQLDocKit is going to close all your connections automagically.

Send us your feedback

You can take SQLDocKit for a spin. Just download our 30-day free trial, or contact us for a personalized demo.

Tags | sql-server
 0
Share Now

Toni Frankola

Entrepreneur, IT consultant, speaker, blogger, and geek. Co-founder of syskit.com.

Previous Post Generate sample data for SharePoint with Acceleratio.SPDG
Next Post My slides from from the European SharePoint Conference 2016

Archives

  • December 2021 (1)
  • June 2021 (1)
  • January 2021 (1)
  • July 2019 (1)
  • June 2019 (2)
  • March 2019 (1)
  • February 2019 (3)
  • October 2018 (2)
  • August 2018 (2)
  • June 2018 (1)
  • April 2018 (1)
  • February 2018 (1)
  • January 2018 (1)
  • June 2017 (1)
  • April 2017 (1)
  • February 2017 (1)
  • November 2016 (2)
  • August 2016 (1)
  • July 2016 (2)
  • March 2016 (1)
  • February 2016 (2)
  • January 2016 (1)
  • November 2015 (2)
  • December 2014 (1)
  • November 2014 (2)
  • July 2014 (2)
  • June 2014 (2)
  • May 2014 (1)
  • April 2014 (2)
  • March 2014 (1)
  • February 2014 (4)
  • January 2014 (2)
  • December 2013 (1)
  • September 2013 (1)
  • July 2013 (1)
  • June 2013 (1)
  • May 2013 (1)
  • April 2013 (3)
  • February 2013 (1)
  • December 2012 (1)
  • November 2012 (1)
  • September 2012 (1)
  • August 2012 (1)
  • July 2012 (2)
  • June 2012 (4)
  • April 2012 (1)
  • February 2012 (1)
  • January 2012 (1)
  • November 2011 (2)
  • October 2011 (2)
  • September 2011 (2)
  • July 2011 (3)
  • May 2011 (2)
  • April 2011 (1)
  • March 2011 (4)
  • February 2011 (3)
  • January 2011 (4)
  • December 2010 (4)
  • November 2010 (3)
  • October 2010 (3)
  • September 2010 (4)
  • August 2010 (1)
  • July 2010 (4)
  • May 2010 (2)
  • April 2010 (2)
  • March 2010 (3)
  • January 2010 (2)
  • December 2009 (3)
  • November 2009 (5)
  • October 2009 (2)
  • August 2009 (7)
  • July 2009 (3)
  • June 2009 (4)
  • May 2009 (3)
  • April 2009 (5)
  • March 2009 (8)
  • February 2009 (5)
  • January 2009 (8)
  • December 2008 (9)
  • November 2008 (8)
  • October 2008 (7)
  • September 2008 (4)
  • August 2008 (6)
  • July 2008 (5)
  • June 2008 (3)
  • May 2008 (4)
  • April 2008 (6)
  • March 2008 (4)
  • February 2008 (3)
  • January 2008 (6)
  • December 2007 (1)
  • November 2007 (3)
  • October 2007 (3)
  • September 2007 (2)
  • August 2007 (1)
  • July 2007 (12)
  • June 2007 (2)
  • May 2007 (3)

Connect Us

All postings on this blog are provided “AS IS” with no warranties, and confer no rights. All entries in this blog are my opinion and don’t necessarily reflect the opinion of my employer.

  • Home
  • About
  • Our Tools
  • Contact me

Copyright (c) Toni Frankola 2008. - 2019.