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

# NAME: lastdbusage.ps1
# AUTHOR: Rob Sewell
# http://sqldbawithabeard.com
# DATE:19/10/2013
# COMMENTS: Fill Excel WorkBook with details fo last access times for each database
# NOTES : Does NOT work with SQL 2000 boxes
$FileName = '' # Set a filename for the output
# Get List of sql servers to check
$sqlservers = Get-Content '' # serverlist, database query whatever

# Set SQL Query
$query = "WITH agg AS
max(last_user_seek) last_user_seek,
max(last_user_scan) last_user_scan,
max(last_user_lookup) last_user_lookup,
max(last_user_update) last_user_update,
sd.name dbname
sys.dm_db_index_usage_stats, master..sysdatabases sd
sd.name not in('master','tempdb','model','msdb')
database_id = sd.dbid group by sd.name
last_read = MAX(last_read),
last_write = MAX(last_write)
SELECT dbname, last_user_seek, NULL FROM agg
SELECT dbname, last_user_scan, NULL FROM agg
SELECT dbname, last_user_lookup, NULL FROM agg
SELECT dbname, NULL, last_user_update FROM agg
) AS x (dbname, last_read, last_write)
#Open Excel
$xl = new-object -comobject excel.application
$wb = $xl.Workbooks.Add()

# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

# Loop through each sql server from sqlservers.txt
foreach ($sqlserver in $sqlservers) {
    # Get the time SQL was restarted
    $svr = New-Object 'Microsoft.SQLServer.Management.Smo.Server' $SQLServer
    $db = $svr.Databases['TempDB']
    $CreateDate = $db.CreateDate

    #Run Query against SQL Server
    $Results = Invoke-Sqlcmd -ServerInstance $sqlServer -Query $query -Database master
    # Add a new sheet
    $ws = $wb.Worksheets.Add()
    $name = "$sqlserver"
    # Name the Sheet
    $ws.name = $Name
    $cells = $ws.Cells
    $xl.Visible = $true
    #define some variables to control navigation
    $row = 2
    $col = 2
    $cells.item($row, $col) = $SQLServer + ' Was Rebooted at ' + $CreateDate
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $row = 3
    $col = 2
    # Set some titles
    $cells.item($row, $col) = "Server"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 10
    $cells.item($row, $col) = "Database"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 40
    $cells.item($row, $col) = "Last Read"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 20
    $cells.item($row, $col) = "Last Write"
    $cells.item($row, $col).font.size = 16
    $Cells.item($row, $col).Columnwidth = 20

    foreach ($result in $results) {
        # Check if value is NULL
        $DBNull = [System.DBNull]::Value
        $LastRead = $Result.last_read
        $LastWrite = $Result.last_write

        $col = 2
        $cells.item($Row, $col) = $sqlserver
        $cells.item($Row, $col) = $Result.dbname
        if ($LastRead -eq $DBNull) {
            $LastRead = "Not Since Last Reboot"
            $colour = "46"
            $cells.item($Row, $col).Interior.ColorIndex = $colour
            $cells.item($Row, $col) = $LastRead
        else {
            $cells.item($Row, $col) = $LastRead
        if ($LastWrite -eq $DBNull) {
            $LastWrite = "Not Since Last Reboot"
            $colour = "46"
            $cells.item($Row, $col).Interior.ColorIndex = $colour
            $cells.item($Row, $col) = $LastWrite
        else {
            $cells.item($Row, $col) = $LastWrite

$xl.DisplayAlerts = $false
Stop-Process -Name *excel*



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

Installing Windows Server 2003 on Hyper-v

A quick post, as much for next time (if there is one) in case I forget.

I created a new Virtual Machine in Hyper-v and installed Windows Server 2003 Standard but after the reboot I had no network connection despite setting it up to connect to my internal network.

I tried re-adding the network adapter in Hyper-v and rebooting which  didnt work so I checked Device Manager which displayed No Device

To fix this, I downloaded and installed Windows Server 2003 SP2 and then installed Integration Services from the Action Menu of the Hyper-V Virtual Machine Connection.

Then after a reboot I had a network adapter

Why You Should Visit the Sponsors at #SQLSatExeter and Other Community Events



SQL Saturdays and other community events rely on sponsors and you know that you will often get entered into a raffle for a prize in exchange for your contact details and there will be freebies of various types from many vendors but there is more that you can get from visiting the sponsors.


At SQL Saturday Exeter on the 22nd March 2014  we are putting free coffee amongst the sponsors. Yes, it’s a ruse in some ways to put you in the same room as the sponsors whilst your mind is buzzing with all the new SQL learning you have been doing and you are feeling confident and inspired about SQL and what you can achieve.

We need the sponsors to put on the events and the sponsors need us to help put them in contact with purchasers of their wares. It is good for all community events if the sponsors can put SQL Saturday Exeter (or another community event} into their CRM as the point of first contact or the place a decision was made for a purchase as it will mean that when they analyse their data in readiness for next years budget community events will still be important to them and they will spend their money and we will continue to be able to benefit from superb free or very cheap training and learning, networking and down right good fun at next years events


For example, I use Red Gate’s SQL Monitor and make use of the graphs to baseline, to see when there are variations to that baseline and to get alerted about long running queries, deadlocks and many other useful DBA information.

Whilst at the Red Gate stand at SQL Saturday in Cambridge I got talking to Daniel Rothig who is one of the developers for SQL Monitor and I was able to ask him about using SQL Monitor.

I wanted to know how best to use the base lining feature and how best to describe some of the detail I was seeing to none-technical people. He and Jonathan Allen was able to give me some examples and knowledge to improve my capabilities in this area. It was fantastic to be able to discuss the product with him and see where they are wanting to take it.


Obviously the sponsors need customers and that is why they put a large amount of marketing activity into SQL Community Events. They want to put their products in front of the people who will be using them and make sales.

But there is a further benefit too Daniel asked me to show him how I used the tool and what I would improve if I could.  I explained that I was having trouble getting the Regex correct for writing exceptions for the alerts for long running queries and I said that I wished there was button I could press to automatically ignore that query that sometimes. He said he would take that back to the team. Excellent, I was able to get a way to improve a good tool to make me work smarter and my experience better

I don’t know if my idea will make it to Production but I hope so. Daniel also said it was useful to see the way users of their software navigated the application and used the features and that that knowledge would help future development

Daniel said

“I’m sure we can make a sale or two on a SQL Saturday – but then, why am I there, and not a sales team? We’ve found it’s more valuable to meet people in the community, learn about their jobs and problems, and search for a gleam in their eyes when we show them our solutions. We take home those first impressions, and the feedback from long-time users, to make our software more focused, relevant, and useful.

And the conversations are always great fun – so come and say hi!”

It’s a win all ways round


Find out more about SQL Saturday Exeter at http://sqlsouthwest.co.uk/ 

Viewing SQL Endpoint Permissions with PowerShell

A quick and simple post today as I have been very busy. I needed to list the users with permissions on mirroring endpoints today so I wrote this script and figured it was worth sharing.

It’s a simple script which takes a server name from a Read-Host prompt. Displays the available endpoints and asks which one you want and shows you the permissions

$Server = Read-Host "Please Enter the Server"
$Endpoints = $srv.Endpoints |select Name -ExpandProperty Name
$EndpointName = Read-Host "Please Enter the Endpoint Name `n Available Names are `n $Endpoints"
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
$Endpoint = $srv.Endpoints[$EndpointName]

and heres a screenshot of the results

Ps`1If you want to do it with T-SQL

select s.name as grantee,
e.name as endpoint,
p.permission_name as permission,
p.state_desc as state_desc
from sys.server_permissions p
join sys.server_principals s on s.principal_id = p.grantee_principal_id
join sys.endpoints e on p.major_id = e.endpoint_id
where p.type='CO'