Rationalisation of Database with Powershell and T-SQL part one

I have recently been involved in a project to rationalise databases. It is easy in a large organisation for database numbers to rapidly increase and sometimes the DBA may not be aware of or be able to control the rise if they don’t have knowledge of all of the database servers on the estate.

There are lots of benefits of rationalisation to the business. Reduced cpu usage = reduced heat released = lower air-con bill for the server room and less storage used = quicker backups and less tapes used or better still less requirement for that expensive new SAN. You may be able to consolidate data and provide one version of the truth for the business as well. Removing servers can release licensing costs which could then be diverted elsewhere or pay for other improvements.

William Durkin b | t presented to the SQL South West User Group about this and will be doing the session at SQL Saturday in Exeter in March 2014 Please check out his session for a more detailed view

I needed to be able to identify databases that could possibly be deleted and realised that an easy way to achieve this would be to use a script to check for usage of the database.

No need to recreate the wheel so I went to Aaron Bertrands blog http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx and used his script. Instead of using an audit file I decided to use Powershell so that I could output the results to Excel and colour code them. This made it easier to check the results and also easier to show to Managers and Service Owners



What it does is place the query in a variable. Get the contents of the SQL Server text file holding all my known SQL Servers and runs the query against each of them storing the results in a variable. It then creates an Excel Workbook and a new sheet for each server and populates the sheet including a bit of colour formatting before saving it. The results look like this

usage excel

The tricky bit was understanding how to match the NULL result from the query. This was done by assigning a variable to [System.DBNull]::Value and using that.

Of course these stats are reset when SQL Server restarts so I also included the SQL server restart time using the create date property  of the TempDB. I gathered these stats for a few months before starting any rationalisation.

My next post will be about the next step in the process. You can get the script here