Wednesday, July 6, 2011

How cluttered is MSDB?


# My quick and dirty PowerShell script to check for the oldest entry in MSDB.
# I'm using a SQLPSX 1.6 function Get-SqlData to execute queries. 
# Runs against multiple servers

   1:  $start = get-date
   2:  write-host "Start: "  $start
   3:   
   4:  $serverName = "SERVER\INSTANCE"
   5:  $databaseName = "dbINVENTORY"
   6:   
   7:  $FilePath = "C:\Output"
   8:  $OutFile = Join-Path -path $FilePath -childPath ("MSDB_MIN_Date_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")
   9:   
  10:  # here string contains SQL query
  11:  $qry = @"
  12:  SELECT DISTINCT
  13:          ISNULL(A.MACH_LPAR_NM, '') + CASE INST_NM
  14:                                         WHEN 'DEFAULT' THEN ''
  15:                                         ELSE '\'
  16:                                       END + CASE INST_NM
  17:                                               WHEN 'DEFAULT' THEN ''
  18:                                               ELSE ISNULL(INST_NM, '')
  19:                                             END AS InstanceName
  20:  FROM    dbInventory A
  24:  ORDER BY InstanceName
  25:  "@
  26:   
  27:  $Servers = Get-SqlData $serverName $databaseName $qry
  28:   
  29:  $qry = @"
  30:  SELECT  @@SERVERNAME AS ServerName, MIN(backup_finish_date) OldestMsdbBackupDate FROM  msdb.dbo.backupset 
  31:  "@
  32:  $databaseName = "msdb"
  33:   
  34:  # Version inventory
  35:  @(
  36:  foreach ($svr in $Servers)
  37:  {
  38:      $serverName = $svr.InstanceName
  39:      trap {"Oops! Query failed. $_"; continue } Get-SqlData $serverName $databaseName $qry
  40:      
  41:  }
  42:  )  | export-csv -noType $OutFile
  43:   
  44:  $end = get-date    
  45:  write-host "End: "  $end