Experimenting with SQL Server Memory

In my “career” as a muskie fisherman, I’ve caught or seen fish in some very unlikely places. I’ve caught fish over very deep water with seemingly no nearby structure as well as in water as shallow as just a few feet. I’ve had muskies strike or chase northern pike and walleye as I brought them into the boat. I’ve had bites when I was expecting nothing. The key to catching more fish is being will to test new locations, techniques, and lures.

shallow-muskies
I recently wondered what would happen if I was forced to drastically reduce memory available to SQL Server. Most DBAs know that if SQL Server is low on available memory and there is RAM to spare on the server, you can increase the memory SQL Server can use in the server properties. But what happens if you reduce the memory SQL Server can use below what it is currently using? I decided to try it out in our Test environment.

The initial maximum memory usage was 7 GB:

20161130-lower-memory-limit-below-current-usage-initial-setting

In looking at the SQL Server process in Windows Task Manager, I see it is using all plus a bit:

20161130-lower-memory-limit-below-current-usage-current-usage

I turned the max down to 4.5 GB:

20161130-lower-memory-limit-below-current-usage-change-setting

Looking at the Windows Task Manager shortly after the change, I can see the amount be using by SQL Server has been reduced as expected.

20161130-lower-memory-limit-below-current-usage-new-usage

If I look at my Memory Usage tracking at that time, I can see buffer pool was the area that was directly affected.  The buffer pool is where any data pages in SQL Server are cached for quick reading/writing.

20161130-lower-memory-limit-below-current-where-memory-changed

So next time you need to adjust the max memory downward, be aware this will immediately reduce the number of pages in the buffer pool.

Advertisements

Using Powershell to create a nightly SQL health check

I had a great opportunity through work to go on a hunting weekend last week.  I usually prefer to fish, especially at this time of year, but I found the hunting to be a lot of fun.  I saw a total of 16 deer, 3 of them being bucks.  The only deer in shooting range were two small doe, and I let them go.  It was very cool to see the deer all around me but not aware of my presence.  I’d love to get back out, but this coming weekend starts gun hunting here in Wisconsin, and I’d prefer not to be in the woods with all the bullets that will be flying around.

hunt-guard

My last two posts showed how to query SQL Server and Oracle using Powershell.  In this post I’m going to share my Powershell script that I use to create a nightly monitoring workbook in Excel.  Each morning when I get in I open that workbook to be sure everything is well in my databases.  The workbook is divided into four tabs:

  1. Scheduled Jobs
  2. Backups
  3. Database file space
  4. Server disk space

I use the information in these worksheets to investigate failed jobs, make sure my backups are up to date, grow any database files that need to be grown, and make sure my servers aren’t running out of space.  Please note I found a script similar to this online and adapted it for my purposes.  The idea behind this monitoring is not original, but my implementation is.

#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $false

$WB = $Excel.Workbooks.Add()
$WB.Worksheets.Add()
$WB.Worksheets.Add()
$WB.Worksheets.Add()
$now = [datetime]::now.ToString('yyyyMMdd')
$a = "\\Servername\IT\Gugg\Powershell\ServerMonitoring\ServerMonitoring$now.xlsx"
#write-host $a
$WB.SaveAs($a)

#################################################################################
#####                             SQL Agent Jobs                                #####

$Sheet = $WB.Worksheets.Item(1)
$Sheet.Name = "SQL Agent Jobs"

#Counter variable for rows
$intRow = 1

#Header for SQL Server
$Sheet.Cells.Item($intRow,1) = "SQL Server"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 10
$intRow++

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\SQL_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "JOB NAME"
      $Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"
      $Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"
      $Sheet.Cells.Item($intRow,4) = "ENABLED"

     #Format the column headers
     for ($col = 1; $col -le 4; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server Agent job status information using PowerShell

     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $jobs=$srv.JobServer.Jobs

     #Formatting using Excel


ForEach ($job in $jobs)  
{ 

       # Formatting for the failed jobs 
       if ($job.LastRunOutcome -eq 0) 
       { 
           $fgColor = 3 
       } 
       else 
       { 
           $fgColor = 0 
       } 
   

       $Sheet.Cells.Item($intRow, 1) =  $job.Name 
       $Sheet.Cells.Item($intRow, 2) =  $job.LastRunOutcome.ToString() 
       $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor 
       $Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate
       $Sheet.Cells.Item($intRow, 4) =  $job.IsEnabled

    
            
       $intRow ++ 
   
} 
   $intRow ++ 


}

#Header for Oracle
$Sheet.Cells.Item($intRow,1) = "ORACLE"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 30
$intRow++

#Read thru the contents of the Oracle_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt")
{

#Create column headers
     $Sheet.Cells.Item($intRow,1) = "DATABASE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "JOB NAME"
      $Sheet.Cells.Item($intRow,2) = "OWNER"
      $Sheet.Cells.Item($intRow,3) = "LAST RUN OUTCOME"
      $Sheet.Cells.Item($intRow,4) = "LAST RUN DATE"
      $Sheet.Cells.Item($intRow,5) = "ENABLED"

     #Format the column headers
     for ($col = 1; $col -le 5; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
     
     #Create connection to Oracle
     $OracleconnectionString = "Data Source=OracleDataSource;User Id=myUser;Password=myPassword;Integrated Security=No"
     $OracleSchedulerQueryString = "with cte as (SELECT sjrd.job_name ,sjrd.owner ,sjrd.status ,TO_CHAR(sjrd.actual_start_date ,'yyyy-mm-dd HH24:MI:SS') as ACTUAL_START_DATE ,sjrd.error# ,asj.ENABLED ,ROW_NUMBER() OVER (Partition by sjrd.JOB_NAME ORDER BY sjrd.ACTUAL_START_DATE DESC) as rn FROM dba_scheduler_job_run_details sjrd INNER JOIN ALL_SCHEDULER_JOBS asj on sjrd.JOB_NAME = asj.JOB_NAME) select JoB_Name ,owner ,Status ,ACTUAL_START_DATE ,ENABLED from cte where rn = 1"
     [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
     $OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString)
     $OracleCommand = New-Object System.Data.OracleClient.OracleCommand($OracleSchedulerQueryString, $OracleConnection)
     $OracleConnection.Open()
     $OraSchedJobs = $OracleCommand.ExecuteReader()
     $OraCounter = $OraSchedJobs.FieldCount
     #Read the data out onto the Excel worksheet
     While ($OraSchedJobs.Read()) {
        for ($o=0; $o -lt $OraCounter; $o = $o + 5) {
            $Sheet.Cells.Item($intRow, 1) = $OraSchedJobs.GetValue($o)
            $Sheet.Cells.Item($intRow, 2) = $OraSchedJobs.GetValue($o+1)
            $Sheet.Cells.Item($intRow, 3) = $OraSchedJobs.GetValue($o+2)
            if ($OraSchedJobs.GetValue($o+2) -ne "SUCCEEDED") 
                { 
                    $Sheet.Cells.item($intRow, 3).Interior.ColorIndex = 3 
                } 
            $Sheet.Cells.Item($intRow, 4) = $OraSchedJobs.GetValue($o+3)
            $Sheet.Cells.Item($intRow, 5) = $OraSchedJobs.GetValue($o+4)
            $intRow++
        }
    }
    $OracleConnection.Close()
    $intRow++
}

$Sheet.UsedRange.EntireColumn.AutoFit()

##############################################################################
#####                    Database Backups                                #####

#Add a new sheet
$Sheet = $WB.Worksheets.Item(2)
$Sheet.Name = "Database Backups"

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\SQL_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
      $Sheet.Cells.Item($intRow,2) = "LAST FULL BACKUP"
      $Sheet.Cells.Item($intRow,3) = "LAST DIFF BACKUP"
      $Sheet.Cells.Item($intRow,4) = "LAST LOG BACKUP"
      $Sheet.Cells.Item($intRow,5) = "LAST BACKUP AGE(DAYS)"

     #Format the column headers
     for ($col = 1; $col -le 5; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server database information using PowerShell

     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $dbs = $s.Databases

     #Formatting using Excel


ForEach ($db in $dbs)  
{ 
   if ($db.Name -ne "tempdb") #We do not need the backup information for the tempdb database 
   {         
       if($db.LastBackupDate -eq "1/1/0001 12:00 AM") #This is the default dateTime value for databases that have not had any backups 
       { 
           $fullBackupDate="Never been backed up" 
           $fgColor3="red" 
       } 
       elseif(($db.LastBackupDate) -lt ((Get-Date).AddDays(-7)))
       {
              $fullBackupDate="{0:g}" -f  $db.LastBackupDate
           $fgColor3="red"
       }
       else 
       { 
           $fullBackupDate="{0:g}" -f  $db.LastBackupDate
           $fgColor3 = "xlnone"
       } 
       
       $Sheet.Cells.Item($intRow, 1) = $db.Name 
       $Sheet.Cells.Item($intRow, 2) = $fullBackupDate
       $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor3
       
       if($db.LastDifferentialBackupDate -eq "1/1/0001 12:00 AM") #This is the default dateTime value for databases that have not had any backups 
       { 
           $diffBackupDate="Never had differential taken"
           $fgColor4 = 15
       } 
       elseif(($db.LastDifferentialBackupDate) -lt ((Get-Date).AddDays(-2)))
       {
              $diffBackupDate="{0:g}" -f  $db.LastDifferentialBackupDate
           $fgColor4=3
       }
       else 
       { 
           $diffBackupDate="{0:g}" -f  $db.LastDifferentialBackupDate 
           $fgColor4=1
       }
    
         
       $Sheet.Cells.Item($intRow, 3) = $diffBackupDate
       $Sheet.Cells.item($intRow, 3).Font.ColorIndex = $fgColor4
   
       #We use the .ToString() Method to convert the value of the Recovery model to string and ignore Log backups for databases with Simple recovery model 
       if ($db.RecoveryModel.Tostring() -eq "SIMPLE")
       { 
           $logBackupDate="N/A"
           $fgColor5 = 15
           $fgColor6 = "xlNone"
       } 
       else 
       { 
           if($db.LastLogBackupDate -eq "1/1/0001 12:00 AM")  
           { 
               $logBackupDate="Never been backed up" 
               $fgColor5 = 1
                  $fgColor6 = 3
           } 
           elseif(($db.LastLogBackupDate) -lt ((Get-Date).AddHours(-5)))
           {
                  $logBackupDate= "{0:g}" -f $db.LastLogBackupDate
               $fgColor5 = 1
                  $fgColor6 = 3
           }
           else 
           { 
               $logBackupDate= "{0:g}" -f $db.LastLogBackupDate
               $fgColor5 = 1
               $fgColor6 = "xlNone"
           } 
            
       } 
    
       $Sheet.Cells.Item($intRow, 4) = $logBackupDate
       $Sheet.Cells.Item($intRow, 4).Font.ColorIndex = $fgColor5
       $Sheet.Cells.Item($intRow, 4).Interior.ColorIndex = $fgColor6
    
       #Define your service-level agreement in terms of days here 
       if ($db.LastBackupDate -gt $db.LastDifferentialBackupDate)
           {
            if ($db.LastBackupDate -gt $db.LastLogBackupDate)
                {
                    $LatestBackup = $db.LastBackupDate
                }
            else
                {
                    $LatestBackup = $db.LastLogBackupDate
                }
        }
        elseif ($db.LastDifferentialBackupDate -gt $db.LastBackupDate)
        {
            if ($db.LastDifferentialBackupDate -gt $db.LastLogBackupDate)
                {
                    $LatestBackup = $db.LastDifferentialBackupDate
                }
            else
                {
                    $LatestBackup = $db.LastLogBackupDate
                }
        }
        else
        {
            $LatestBackup = $db.LastLogBackupDate
        }
        
        
       $NumDaysSinceLastBackup = ((Get-Date) - $LatestBackup).Days #We use Date Math to extract the number of days since the last full backup  
       if ($NumDaysSinceLastBackup -gt 1) 
       { 
           $fgColor = 3 
       } 
       else 
       { 
           $fgColor = 0 
       } 
    
       $Sheet.Cells.Item($intRow, 5) = $NumDaysSinceLastBackup 
       $Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor 
    
    
            
       $intRow ++ 
    
       } 
   } 
   $intRow ++ 


}

#Read thru the contents of the Oracle_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt")
{
    #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++
     
     $Sheet.Cells.Item($intRow,1) = "DATAFILE BACKUPS"
     $Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 50
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     
     $intRow++

      $Sheet.Cells.Item($intRow,1) = "TABLESPACE"
      $Sheet.Cells.Item($intRow,2) = "FILE TYPE"
      $Sheet.Cells.Item($intRow,3) = "BACKUP TYPE"
      $Sheet.Cells.Item($intRow,4) = "FILE NAME"
      $Sheet.Cells.Item($intRow,5) = "BACKUPSET TIME"


     #Format the column headers
     for ($col = 1; $col -le 5; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
     
     #Create connection to Oracle
     $OracleconnectionString = "Data Source=OracleDataSource;User Id=myUser;Password=myPassword;Integrated Security=No"
     $OracleDFBUQueryString = "with cte as (select DF_TABLESPACE,FILE_TYPE,BACKUP_TYPE,FNAME,BS_COMPLETION_TIME, ROW_NUMBER () OVER (PARTITION BY DF_TABLESPACE,FILE_TYPE,BACKUP_TYPE,FNAME ORDER BY BS_COMPLETION_TIME DESC) rn from V`$BACKUP_FILES WHERE FILE_TYPE = 'DATAFILE') SELECT DF_TABLESPACE, FILE_TYPE, BACKUP_TYPE, FNAME, BS_COMPLETION_TIME FROM cte WHERE rn = 1"
     [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
     $OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString)
     $OracleCommand = New-Object System.Data.OracleClient.OracleCommand($OracleDFBUQueryString, $OracleConnection)
     $OracleConnection.Open()
     $OraDFBU = $OracleCommand.ExecuteReader()
     $OraCounter = $OraDFBU.FieldCount
     #Read the data out onto the Excel worksheet
     While ($OraDFBU.Read()) {
        for ($o=0; $o -lt $OraCounter; $o = $o + 5) {
            $Sheet.Cells.Item($intRow, 1) = $OraDFBU.GetValue($o)
            $Sheet.Cells.Item($intRow, 2) = $OraDFBU.GetValue($o+1)
            $Sheet.Cells.Item($intRow, 3) = $OraDFBU.GetValue($o+2)
            $Sheet.Cells.Item($intRow, 4) = $OraDFBU.GetValue($o+3)
            $Sheet.Cells.Item($intRow, 5) = $OraDFBU.GetValue($o+4)
            $intRow++
        }
    }
    $intRow++
    $Sheet.Cells.Item($intRow,1) = "OTHER FILE BACKUPS"
     $Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 54
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     
     $intRow++

      $Sheet.Cells.Item($intRow,1) = "BACKUP TYPE"
      $Sheet.Cells.Item($intRow,2) = "FILE TYPE"
      $Sheet.Cells.Item($intRow,3) = "BACKUP TIME"
      
      
      #Format the column headers
     for ($col = 1; $col -le 3; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }
    
    $intRow++
    #We're going to use the existing connection, which is already open, but we need a new query string and command object
    $OracleOFBUQueryString = "with cte as ( select BACKUP_TYPE, FILE_TYPE, NVL(COMPLETION_TIME,BS_COMPLETION_TIME) COMPLETION_TIME, ROW_NUMBER () OVER (PARTITION BY BACKUP_TYPE, FILE_TYPE ORDER BY NVL(COMPLETION_TIME,BS_COMPLETION_TIME) DESC) rn from V`$BACKUP_FILES WHERE FILE_TYPE<> 'DATAFILE') SELECT BACKUP_TYPE, FILE_TYPE, COMPLETION_TIME FROM cte WHERE rn = 1"
    $OracleCommandOF = New-Object System.Data.OracleClient.OracleCommand($OracleOFBUQueryString, $OracleConnection)
    $OraOFBU = $OracleCommandOF.ExecuteReader()
     $OraCounterOF = $OraOFBU.FieldCount
     #Read the data out onto the Excel worksheet
     While ($OraOFBU.Read()) {
        for ($o=0; $o -lt $OraCounterOF; $o = $o + 3) {
            $Sheet.Cells.Item($intRow, 1) = $OraOFBU.GetValue($o)
            $Sheet.Cells.Item($intRow, 2) = $OraOFBU.GetValue($o+1)
            $Sheet.Cells.Item($intRow, 3) = $OraOFBU.GetValue($o+2)
            $intRow++
        }
    }
    
    $OracleConnection.Close()
    $intRow++
     
}

$Sheet.UsedRange.EntireColumn.AutoFit()

#############################################################################################################
#####                                    Database Disk Space                                                #####

#Create a new Excel object using COM 
$Sheet = $WB.Worksheets.Item(3)
$Sheet.Name = "Database File Space"

#Counter variable for rows
$intRow = 1



#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\SQL_Servers.txt")
{

     # Create an SMO connection to the instance
     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

     $dbs = $s.Databases

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
      $Sheet.Cells.Item($intRow,2) = "AUTOSHRINK"
      $Sheet.Cells.Item($intRow,3) = "RECOVERY MODEL"
      $Sheet.Cells.Item($intRow,4) = "FILE NAME"
      $Sheet.Cells.Item($intRow,5) = "SIZE (MB)"
      $Sheet.Cells.Item($intRow,6) = "SPACE USED (MB)"
      $Sheet.Cells.Item($intRow,7) = "PERCENT FREE"

     #Format the column headers
     for ($col = 1; $col -le 7; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server database information using PowerShell

#     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

     # Create an SMO connection to the instance
#     $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

#     $dbs = $s.Databases

     #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

     #Formatting using Excel

     ForEach ($db in $dbs) 
     {
     $FileGroups = $db.FileGroups
        ForEach($FileGroup in $FileGroups)
        {
        $Files = $FileGroup.Files
            ForEach($File in $Files)
            {
#              #Divide the value of SpaceAvailable by 1KB 
#              $FileSpaceAvailable = $File.SpaceAvailable/1KB 

              #Format the results to a number with three decimal places 
    #          $dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable

              $Sheet.Cells.Item($intRow, 1) = $db.Name
     
               #Change the background color of the Cell depending on the AutoShrink property value 
               if ($db.AutoShrink -eq "True")
              {
                   $fgColor = 3
              }
              else
              {
                   $fgColor = 0
              }

              $Sheet.Cells.Item($intRow, 2) = $db.AutoShrink 
              $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor

              $Recovery = $db.RecoveryModel
              Switch ($Recovery)
              {
                 Full { $RecoveryText = 'Full' }
                 BulkLogged { $RecoveryText = 'Bulk-Logged?' }
                 Simple { $RecoveryText = 'Simple' }
                 Default { $RecoveryText = 'unable to determine' }
                }

              $Sheet.Cells.Item($intRow, 3) = $RecoveryText
              $Sheet.Cells.Item($intRow, 4) = $File.FileName
              $Sheet.Cells.Item($intRow, 5) = "{0:N0}" -f ($File.Size/1024)

              #Change the background color of the Cell depending on the SpaceAvailable property value 
              if (($File.UsedSpace / $File.Size * 100) -gt 95.00)
              {
                   $fgColor = 3
              }
               else
              {
                   $fgColor = 0
              }

              $Sheet.Cells.Item($intRow, 6) = "{0:N0}" -f ($File.UsedSpace/1024 )
              $Sheet.Cells.Item($intRow, 7) = 1-($File.UsedSpace / $File.Size)
              $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor
              $Sheet.Cells.item($intRow, 7).NumberFormat = "0.00%"

              $intRow ++
                  }
            }
        #Continue with log files
        $LogFiles = $db.LogFiles
        ForEach ($LogFile in $LogFiles)
            {
                $Sheet.Cells.Item($intRow, 1) = $db.Name
     
               #Change the background color of the Cell depending on the AutoShrink property value 
               if ($db.AutoShrink -eq "True")
              {
                   $fgColor = 3
              }
              else
              {
                   $fgColor = 0
              }

              $Sheet.Cells.Item($intRow, 2) = $db.AutoShrink 
              $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor

              $Recovery = $db.RecoveryModel
              Switch ($Recovery)
              {
                 Full { $RecoveryText = 'Full' }
                 BulkLogged { $RecoveryText = 'Bulk-Logged?' }
                 Simple { $RecoveryText = 'Simple' }
                 Default { $RecoveryText = 'unable to determine' }
                }

              $Sheet.Cells.Item($intRow, 3) = $RecoveryText
              $Sheet.Cells.Item($intRow, 4) = $LogFile.FileName
              $Sheet.Cells.Item($intRow, 5) = "{0:N0}" -f ($LogFile.Size/1024)

              #Change the background color of the Cell depending on the SpaceAvailable property value 
              if (($LogFile.UsedSpace / $LogFile.Size * 100) -gt 95.00)
              {
                   $fgColor = 3
              }
               else
              {
                   $fgColor = 0
              }

              $Sheet.Cells.Item($intRow, 6) = "{0:N0}" -f ($LogFile.UsedSpace/1024 )
              $Sheet.Cells.Item($intRow, 7) = 1-($LogFile.UsedSpace / $LogFile.Size)
              $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor
              $Sheet.Cells.item($intRow, 7).NumberFormat = "0.00%"

              $intRow ++
            }

     }

$intRow ++

}

#Read thru the contents of the Oracle_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt")
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
      $Sheet.Cells.Item($intRow,2) = "SPACE MANAGEMENT"
      $Sheet.Cells.Item($intRow,3) = "ARCHIVELOG MODE"
      $Sheet.Cells.Item($intRow,4) = "FILE NAME"
      $Sheet.Cells.Item($intRow,5) = "SIZE (MB)"
      $Sheet.Cells.Item($intRow,6) = "SPACE USED (MB)"
      $Sheet.Cells.Item($intRow,7) = "PERCENT FREE"
      
      #Format the column headers
     for ($col = 1; $col -le 7; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
     
     #Create connection to Oracle
     $OracleconnectionString = "Data Source=OracleDataSource;User Id=myUser;Password=myPassword;Integrated Security=No"
     $OracleSchedulerQueryString = "SELECT  Substr(df.tablespace_name,1,20) , dt.SEGMENT_SPACE_MANAGEMENT, vd.LOG_MODE, Substr(df.file_name,1,80), Round(df.bytes/1024/1024,0), decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)), 1 - decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes),3)) FROM  DBA_DATA_FILES DF INNER JOIN dba_tablespaces dt on DF.TABLESPACE_NAME = dt.tablespace_name LEFT OUTER JOIN (SELECT file_id, sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E ON  e.file_id = df.file_id LEFT OUTER JOIN (SELECT Max(bytes) free_bytes, file_id FROM dba_free_space GROUP BY file_id) f ON df.file_id  = f.file_id CROSS JOIN v`$database vd ORDER BY df.tablespace_name, df.file_name"
     [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
     $OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString)
     $OracleCommand = New-Object System.Data.OracleClient.OracleCommand($OracleSchedulerQueryString, $OracleConnection)
     $OracleConnection.Open()
     $OraSchedJobs = $OracleCommand.ExecuteReader()
     $OraCounter = $OraSchedJobs.FieldCount
     #Read the data out onto the Excel worksheet
     While ($OraSchedJobs.Read()) {
        for ($o=0; $o -lt $OraCounter; $o = $o + 7) {
            $Sheet.Cells.Item($intRow, 1) = $OraSchedJobs.GetValue($o)
            $Sheet.Cells.Item($intRow, 2) = $OraSchedJobs.GetValue($o+1)
            $Sheet.Cells.Item($intRow, 3) = $OraSchedJobs.GetValue($o+2)
            $Sheet.Cells.Item($intRow, 4) = $OraSchedJobs.GetValue($o+3)
            $Sheet.Cells.Item($intRow, 5) = $OraSchedJobs.GetValue($o+4)
            $Sheet.Cells.Item($intRow, 6) = $OraSchedJobs.GetValue($o+5)
            $Sheet.Cells.Item($intRow, 7) = $OraSchedJobs.GetValue($o+6)
            $Sheet.Cells.item($intRow, 7).NumberFormat = "0.00%"
            if ($OraSchedJobs.GetValue($o+6) -lt 0.05) 
                { 
                    $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = 3 
                } 
            $intRow++
        }
    }
    $OracleConnection.Close()
    $intRow++
}

$Sheet.UsedRange.EntireColumn.AutoFit()

############################################################################################################################
######################                    Server disk space                                     ############################

#Create a new Excel object using COM 
$Sheet = $WB.Worksheets.Item(4)
$Sheet.Name = "Server Disk Space"

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\SQL_Servers.txt")
{
    #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DRIVE"
      $Sheet.Cells.Item($intRow,2) = "SIZE GB"
      $Sheet.Cells.Item($intRow,3) = "FREE SPACE GB"
      $Sheet.Cells.Item($intRow,4) = "PERCENT FREE"

     #Format the column headers
     for ($col = 1; $col -le 4; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
    
    
    $disks = Get-WmiObject -ComputerName $instance -Class Win32_LogicalDisk -Filter "DriveType = 3";
    
    foreach($disk in $disks)
    {
         $percentFree = ($disk.FreeSpace / $disk.Size)
         if ($percentFree -lt .15)
         {
             if ($percentFree -lt .05)
            {
                $fgColor7 = 3
            }
            else
            {
                $fgColor7 = 6
            }
         }
         else
         {
             $fgColor7 = "xlNone"
         }
        
         $Sheet.Cells.Item($intRow,1) = $disk.DeviceID
         $Sheet.Cells.Item($intRow,2) = "{0:N1}" -f ($disk.Size / 1073741824)
         $Sheet.Cells.Item($intRow,3) = "{0:N1}" -f ($disk.FreeSpace / 1073741824)
         $Sheet.Cells.Item($intRow,4) = $percentFree
         $Sheet.Cells.item($intRow, 4).NumberFormat = "0.00%"
         $Sheet.Cells.Item($intRow,4).Interior.ColorIndex = $fgColor7
         
         $intRow++
    }
    
    $intRow++
}

#Read thru the contents of the Oracle_Servers.txt file
foreach ($instance in get-content "\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt")
{
    #Create column headers
     $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = "DRIVE"
      $Sheet.Cells.Item($intRow,2) = "SIZE GB"
      $Sheet.Cells.Item($intRow,3) = "FREE SPACE GB"
      $Sheet.Cells.Item($intRow,4) = "PERCENT FREE"

     #Format the column headers
     for ($col = 1; $col -le 4; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
    
    
    $disks = Get-WmiObject -ComputerName $instance -Class Win32_LogicalDisk -Filter "DriveType = 3";
    
    foreach($disk in $disks)
    {
         $percentFree = ($disk.FreeSpace / $disk.Size)
         if ($percentFree -lt .15)
         {
             if ($percentFree -lt .05)
            {
                $fgColor7 = 3
            }
            else
            {
                $fgColor7 = 6
            }
         }
         else
         {
             $fgColor7 = "xlNone"
         }
        
         $Sheet.Cells.Item($intRow,1) = $disk.DeviceID
         $Sheet.Cells.Item($intRow,2) = "{0:N1}" -f ($disk.Size / 1073741824)
         $Sheet.Cells.Item($intRow,3) = "{0:N1}" -f ($disk.FreeSpace / 1073741824)
         $Sheet.Cells.Item($intRow,4) = $percentFree
         $Sheet.Cells.item($intRow, 4).NumberFormat = "0.00%"
         $Sheet.Cells.Item($intRow,4).Interior.ColorIndex = $fgColor7
         
         $intRow++
    }
    
    $intRow++
}

$Sheet.UsedRange.EntireColumn.AutoFit()

$WB.Save()
$WB.Close()
$Excel.Application.Quit()
cls

There are a few things to note:

  • I keep the list of SQL Servers and Oracle Servers in text files on a folder share.  That is the\\Servername\IT\Gugg\Powershell\Oracle_Servers.txt and

    \\Servername\IT\Gugg\Powershell\SQL_Servers.txt that you are seeing over and over.

  • You’ll notice several places where the Excel cells are shaded red or yellow, depending upon conditions.  It helps to see potential problems highlighted when going through the new workbook each morning.
  • I use Windows Task Scheduler to kick this powershell script off each morning at 6 AM.

SQL Server Alerts via Text Message

After a very mild winter thus far, we’re finally getting some cold weather. Today was -1 degree as I left for work this morning. It looks like we could finally get some ice that will be safe for fishing. I’ve never been a huge fan of ice fishing, but I have to admit I’m growing rather restless this winter. Ice fishing is another sport that is expensive to get into. An auger will cost at least $300, then you’ll want some poles for jigging (about $30 each), lures, a shanty so you don’t freeze, a heater for the shanty, and plenty of super warm clothes and outerwear. All told, if you’re looking to start the sport, you’re looking at about $1,000. The best thing to do is find some friends who enjoy it and tag along. Then you’ll at least get the experience and decide if you want to start investing in some of the equipment.
It is important for a DBA to stay connected with his/her system. You have to really know what you have to know if it is performing well, poorly, or at all. One way to stay connected is through alerts. SQL Server is able to send emails through DatabaseMail to warn when things aren’t working properly. This works well, but if you don’t want to have to be constantly checking your email to see problems, you can also send text messages to your phone. This is especially handy for emergency situations, like running out of space. Hopefully you’d know well ahead of time if you are running out of space, but crazy things can happen and you have to be ready for them.
Let’s say you know your tempdb database data files are using the entire LUN that they are on – no file growth is possible. If the applications using your database max out the space inside tempdb, your database is going to crash. You’re a good DBA, so you’re constantly monitoring tempdb used space with this table and stored procedure. A SQL agent job calls the stored procedure every five minutes.

CREATE TABLE [dbo].[tempdbSpaceMonitoring](
   [TID] [INT] IDENTITY(1,1) NOT NULL,
   [Filename] [VARCHAR](50) NOT NULL,
   [FileSize_MB] [FLOAT] NOT NULL,
   [UsedSpace_MB] [FLOAT] NOT NULL,
   [UserObjectUsed_MB] [FLOAT] NULL,
   [InternalObjectUsed_MB] [FLOAT] NULL,
   [VersionStoreUsed_MB] [FLOAT] NULL,
   [IsLog] [BIT] NOT NULL,
   [CaptureDateTime] [DATETIME] NOT NULL,
 CONSTRAINT [PK_tempdbSpaceMonitoring] PRIMARY KEY CLUSTERED 
(
   [TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE PROCEDURE [dbo].[CapturetempdbSpaceMonitoring] 

AS
BEGIN
   
   DECLARE @CaptureDate DATETIME
   SELECT @CaptureDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DATEADD(SECOND, 30 - DATEPART(SECOND, GETDATE() + '00:00:30.000'), GETDATE())), 0)
   
   INSERT INTO dbo.tempdbSpaceMonitoring
       (   Filename,
           FileSize_MB,
           Usedspace_MB,
           UserObjectUsed_MB,
           InternalObjectUsed_MB,
           VersionStoreUsed_MB,
           IsLog,
           CaptureDateTime)
   SELECT
           df.name,
           df.size/128,
           df.size/128 - fsu.unallocated_extent_page_count/128,
           fsu.user_object_reserved_page_count/128,
           fsu.internal_object_reserved_page_count/128,
           fsu.version_store_reserved_page_count/128,
           0,
           @CaptureDate
       FROM tempdb.sys.dm_db_file_space_usage fsu
       INNER JOIN tempdb.sys.database_files df ON fsu.FILE_ID = df.FILE_ID
   UNION
   SELECT  f.name,
           f.size,
           os.cntr_value/8/128 AS freespace,
           NULL,
           NULL,
           NULL,
           1,
           @CaptureDate
       FROM tempdb.sys.dm_os_performance_counters os
       FULL OUTER JOIN (SELECT name,size/128 AS size FROM tempdb.sys.database_files WHERE TYPE = 1) AS f ON 1=1
       WHERE counter_name = 'Log File(s) Used Size (KB)'
       AND instance_name = 'tempdb'
       
END

GO

You’d want an email sent whenever the percent of available space goes under 5%. You can get the percent of space used with the following SQL:

SELECT  SUM(FileSize_MB) AS FileSize_MB ,
        SUM(UsedSpace_MB) AS UsedSpace_MB ,
        CaptureDateTime
FROM    DBMaint.dbo.tempdbSpaceMonitoring
WHERE   IsLog = 0
GROUP BY CaptureDateTime
ORDER BY CaptureDateTime DESC;

So I’d set up a SQL Agent job to run every five minutes and execute this SQL:

DECLARE @percentused DECIMAL(3, 2);

SELECT TOP 1
@percentused = SUM(UsedSpace_MB) / SUM(FileSize_MB) * 100
FROM    DBMaint.dbo.tempdbSpaceMonitoring
WHERE   IsLog = 0
GROUP BY CaptureDateTime
ORDER BY CaptureDateTime DESC;

IF @percentused > 95
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = '5555555555@email.uscc.net',
@subject = N'TempDB Almost Full',
@body = N'TempDB is almost full.  Please login and check for unclosed connections';
END;

The text message is sent just like email, except you have to put the number into the Send To address. There is some additional characters needed depending on which cell carrier you use. The additional characters can be found on this website.

Here are the sample results:

20160113 Text alert

Use these rules to stay connected to your SQL Server and look like a pro!