Rationalisation of Database with Powershell and T-SQL part one

Posted on Updated on

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

# Set SQL Query
$query = “WITH agg AS
(
SELECT
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
FROM
sys.dm_db_index_usage_stats, master..sysdatabases sd
WHERE
sd.name not in(‘master’,’tempdb’,’model’,’msdb’)
AND
database_id = sd.dbid group by sd.name
)
SELECT
dbname,
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT dbname, last_user_seek, NULL FROM agg
UNION ALL
SELECT dbname, last_user_scan, NULL FROM agg
UNION ALL
SELECT dbname, last_user_lookup, NULL FROM agg
UNION ALL
SELECT dbname, NULL, last_user_update FROM agg
) AS x (dbname, last_read, last_write)
GROUP BY
dbname
ORDER BY 1;

#Open Excel
$xl = new-object -comobject excel.application
$wb = $xl.Workbooks.Add()

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

# Get List of sql servers to check
$sqlservers = Get-Content ‘D:\SkyDrive\Documents\Scripts\Powershell Scripts\sqlservers.txt’

# 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
$col++
$cells.item($row,$col)=”Database”
$cells.item($row,$col).font.size=16
$Cells.item($row,$col).Columnwidth = 40
$col++
$cells.item($row,$col)=”Last Read”
$cells.item($row,$col).font.size=16
$Cells.item($row,$col).Columnwidth = 20
$col++
$cells.item($row,$col)=”Last Write”
$cells.item($row,$col).font.size=16
$Cells.item($row,$col).Columnwidth = 20
$col++

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

$row++
$col=2
$cells.item($Row,$col)=$sqlserver
$col++
$cells.item($Row,$col)=$Result.dbname
$col++
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
}
$col++
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

$wb.Saveas(“D:\DatabaseLastAccessTimeFeb2014.xlsx”)
$xl.quit()
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

8 thoughts on “Rationalisation of Database with Powershell and T-SQL part one

    […] Rationalisation of Database with Powershell and T-SQL part one – Rob Sewell (Blog|Twitter) […]

    […] the previous post I showed the script to create an Excel Workbook, colour coded showing the last used date for all of […]

    (SFTW) SQL Server Links 28/02/14 • John Sansom said:
    March 4, 2014 at 10:53 am

    […] Rationalisation of Database with Powershell and T-SQL part one – Rob Sewell (Blog|Twitter) […]

    El said:
    April 24, 2015 at 9:59 am

    Rob , great post thanks. Could you show me how to output to only 1 excel sheet for all databases ? Been trying but had no luck.

      SQLDBAwithTheBeard said:
      April 24, 2015 at 10:23 am

      Hi

      Thank you

      At line 47

      #Open Excel
      $xl = new-object -comobject excel.application
      $wb = $xl.Workbooks.Add()

      add this line

      # Add a new sheet
      $ws = $wb.Worksheets.Add()
      $name = “Database to Go”
      # Name the Sheet
      $ws.name = $Name

      and remove this code from lines 66 – 70

      # Add a new sheet
      $ws = $wb.Worksheets.Add()
      $name = “$sqlserver”
      # Name the Sheet
      $ws.name = $Name

      That will put everything in one sheet but you will need to also move row number out of the loop

      So move lines 74 and 75

      $row = 2
      $col = 2

      to before line 55

      and delete lines 89 and 90

      $row=3
      $col=2

      and that should do the trick

      If not please let me know

      Regards

      Rob

    El said:
    April 24, 2015 at 12:08 pm

    Hi Rob thanks for quick reply, apprecaite your help. This is now the amended script , but still don’t seem to be listed each DB on each server. Where have I gone wrong ?
    All on one sheet but no db information listed.

    ##############################################################################################
    # 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

    # Set SQL Query
    $query = “WITH agg AS
    (
    SELECT
    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
    FROM
    sys.dm_db_index_usage_stats, master..sysdatabases sd
    WHERE
    sd.name not in(‘master’,’tempdb’,’model’,’msdb’)
    AND
    database_id = sd.dbid group by sd.name
    )
    SELECT
    dbname,
    last_read = MAX(last_read),
    last_write = MAX(last_write)
    FROM
    (
    SELECT dbname, last_user_seek, NULL FROM agg
    UNION ALL
    SELECT dbname, last_user_scan, NULL FROM agg
    UNION ALL
    SELECT dbname, last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT dbname, NULL, last_user_update FROM agg
    ) AS x (dbname, last_read, last_write)
    GROUP BY
    dbname
    ORDER BY 1;

    #Open Excel
    $xl = new-object -comobject excel.application
    $wb = $xl.Workbooks.Add()
    # Add a new sheet
    $ws = $wb.Worksheets.Add()
    $name = “Database to Go”
    # Name the Sheet
    $ws.name = $Name
    $row = 2
    $col = 2

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

    # Get List of sql servers to check
    $sqlservers = Get-Content ‘D:\SQLSkills\PowerShell\Dev_SQL_Servers.txt’

    # 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

    $cells=$ws.Cells
    $xl.Visible = $true
    #define some variables to control navigation

    $cells.item($row,$col)=$SQLServer + ‘ Was Rebooted at ‘ + $CreateDate
    $cells.item($row,$col).font.size=16
    $Cells.item($row,$col).Columnwidth = 10

    # Set some titles
    $cells.item($row,$col)=”Server”
    $cells.item($row,$col).font.size=16
    $Cells.item($row,$col).Columnwidth = 10
    $col++
    $cells.item($row,$col)=”Database”
    $cells.item($row,$col).font.size=16
    $Cells.item($row,$col).Columnwidth = 40
    $col++
    $cells.item($row,$col)=”Last Read”
    $cells.item($row,$col).font.size=16
    $Cells.item($row,$col).Columnwidth = 20
    $col++
    $cells.item($row,$col)=”Last Write”
    $cells.item($row,$col).font.size=16
    $Cells.item($row,$col).Columnwidth = 20
    $col++

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

    $row++
    $col=2
    $cells.item($Row,$col)=$sqlserver
    $col++
    $cells.item($Row,$col)=$Result.dbname
    $col++
    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
    }
    $col++
    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

    $wb.Saveas(“D:\SQLSkills\PowerShell\Reports\DatabaseLastAccessTimeApr2105.xlsx”)
    $xl.quit()
    Stop-Process -Name *excel*

      El said:
      April 24, 2015 at 12:57 pm

      ok , Rob , I am getting there. Must have been something I did wrong before. Your script works really well. All details on one sheet now. The only thing I have left to resolve is the titles format in the xls part. After the frist server the Servername , Database , last read and last write appear right at the end of the first server details , and not on a new line for the next server.
      Any pointers ?
      cheers

        SQLDBAwithTheBeard said:
        April 24, 2015 at 3:51 pm

        Hi Ellis,

        No worries, you just caught me at the right time to respond :-)

        I think you need to add $row++ at the beginning of the foreach loop

        That should do the trick

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s