• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

  • Services
  • Pocket DBA®
  • Blog
  • Testimonials
  • Contact
  • About

EntryLevel

Getting to know Powershell, from an old DBA

December 12, 2016 by Kevin3NF 1 Comment

Getting to know Powershell, from an old DBA

My team was recently tasked with finding and resolving all Errors and Warnings for every server in our environment, from Prod all the way down to Dev.  Developers are on their own for their laptops. This is a bunch of servers and a small admin team that does mostly SQL server, so I knew I needed automation.

I tried just exporting directly from each server’s Event Viewer, but that was far too cumbersome, and not really repeatable for the future.

Finally, I have an actual use for PowerShell, since SQL Server is not involved here. Best tool for the job, yes?

What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.

Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉

Start here to see the specifics of the Get-EventLog cmdlet:
MSDN Get-EventLog

Step 1: Get a minimal command to actually execute:

#This will bring every entry from your local machines’s System log

Get-EventLog -LogName System

(Feel free to run these in your non-prod/test environment,

Step 2: Cut that list down a bit:

#Lets filter out everything but Errors and Warnings...
Get-EventLog -LogName System -EntryType "Error","Warning"

Step 3: Add a date parameter and line wrap the code (note the ` at the end of line 1):

#Still too much, how about just the last three days?
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3)

Step 4: Connect to remote computer:

#Switch from local pc to remote machine (local is default)
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3) -ComputerName SERVER08

Step 5: Connect to multiple remote computers:

#Add a second server to the list, note the “” around each name
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3) -ComputerName "SERVER08","SERVER07"

Results:

PS C:\Users\Kevin3NF> Get-EventLog -LogName System -EntryType “Error”,”Warning” `
-After (Get-Date).AddDays(-3) -ComputerName “SERVER08″,”SERVER07”

Index Time EntryType Source InstanceID Message
—– —- ——— —— ———- ——-
162296 Dec 06 03:40 Error Schannel 36888 A fatal alert was generated and sent to the remote endpoint. This may r…
162295 Dec 06 03:40 Error Schannel 36874 An TLS 1.2 connection request was received from a remote client applica…

Step 6: Pick and choose the columns we care about:

# Pipe results to Select-Object to choose the properties (columns) we want
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3) -ComputerName "SERVER08","SERVER07" `
| Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message

Results:

MachineName : SERVER08.DallasDBAs.com
EntryType : Error
EventID : 36888
Source : Schannel
TimeGenerated : 12/6/2016 3:40:24 AM
Message : A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS
protocol defined fatal error code is 40. The Windows SChannel error state is 1205.

MachineName : SERVER08.DallasDBAs.com
EntryType : Error
EventID : 36874
Source : Schannel
TimeGenerated : 12/6/2016 3:40:24 AM
Message : An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the
client application are supported by the server. The SSL connection request has failed.

Step 7: let’s get something that looks more table-ish…

# Ugh. I want a table I can work with:
Get-EventLog -LogName System -EntryType "Error","Warning" `
-After (Get-Date).AddDays(-3) -ComputerName "SERVER08","SERVER07" `
| Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
| Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message

Results:

SERVER08.DallasDBAs.com 12/6/2016 3:39:05 AM Error Schannel 36888 A fatal alert was ge…
SERVER08.DallasDBAs.com 12/6/2016 3:38:38 AM Warning LsaSrv 40968 The Security System …
SERVER07.DallasDBAs.com 12/6/2016 4:06:30 AM Error Schannel 36888 A fatal alert was ge…
SERVER07.DallasDBAs.com 12/6/2016 4:06:30 AM Error Schannel 36874 An TLS 1.2 connectio…

(this looks like a table when run in PowerShell)

Step 8: 2 servers? Yeah, right…more like 20+!

Make a ServerList_Sample.txt file (Name it whatever you want):

get-errorlog_blog_1

Save it somewhere.

Step 9: Reference that file, refer to it in the PS script in a variable

# Store each server in ServerList_Sample.txt in a variable
$computers = Get-Content "B:\DBA\ServerList_Sample.txt";

# Loop through each server and get the logs
foreach($computer in $computers)

    {
    Get-EventLog -LogName System -EntryType "Error","Warning" `
    -After (Get-Date).AddDays(-3) -ComputerName $computer `
    | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
    | Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message
    }

——————————————————————————————-
New items:
• foreach,
• $computers
• -ComputerName $computer (variable call)
• Statement braces {}

In English: For every computer in the file I give, Get the event log info.

Result:

SERVERACA18.DallasDBAs.com 12/6/2016 2:45:28 PM Warning ebdrv 34 NTPNP_PCI0184 Unqual…
SERVERACA18.DallasDBAs.com 12/6/2016 2:45:28 PM Error hpipmibt 1001 The description for …
SERVERACA18.DallasDBAs.com 12/6/2016 2:34:41 PM Warning WinRM 10149 The description for …

MachineName TimeGenerated EntryType Source EventID Message


SERVERACA21.DallasDBAs.com 12/9/2016 10:34:48 AM Error DCOM 10016 The description for …
SERVERACA21.DallasDBAs.com 12/9/2016 10:13:03 AM Error Service Control Manager 7034 The Acme-Calc s…

Step 10: Oh crud…a server in my server list is offline for patching!

# Store each server in ServerList_Sample.txt in a variable
$computers = Get-Content "B:\DBA\ServerList_Sample.txt";

# loop through each server
foreach($computer in $computers)

{
  # Try to make a connection to each server

  if(Test-Connection $computer -Quiet -Count 1)

  # on successful connection, go get the logs
    {
    Try
        {
        Get-EventLog -LogName System -EntryType "Error","Warning" `
        -After (Get-Date).AddDays(-3) -ComputerName $computer `
        | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
        | Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message
        }
    # if an error in retrieving the logs, write it out
    Catch
        {
        Write-Verbose "Error $($error[0]) encountered when attempting to get events from $computer"
        }
    }
# if the Connection attempt failed, write this error…
    else
        {
        Write-Verbose "Failed to connect to $computer"
        }
}

New Items:
• if(Test-Connection…)/else…(basically a ping)
• Try / Catch blocks
• Write-Verbose for error reporting
• Nested statement Braces {}

Step 11: Output this to a .txt file for using offline in Excel

 
# Store each server in ServerList_Sample.txt in a variable
$computers = Get-Content "B:\DBA\ServerList_Sample.txt";

# loop through each server
foreach($computer in $computers)

{
  # Try to make a connection to each server

  if(Test-Connection $computer -Quiet -Count 1)

  # on successful connection, go get the logs
    {
    Try
        {
        Get-EventLog -LogName System -EntryType "Error","Warning" `
        -After (Get-Date).AddDays(-3) -ComputerName $computer `
        | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
        | Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message `
        | Out-File -append B:\DBA\System.txt ;

        Get-EventLog -LogName Application -EntryType "Error","Warning" `
        -After (Get-Date).AddDays(-3) -ComputerName $computer `
        | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message `
        | Format-Table -Property MachineName, TimeGenerated, EntryType, Source, EventID, Message `
        | Out-File -append B:\DBA\Application.txt ;
        } 
    # if an error in retrieving the logs, write it out 
    Catch 
        { 
        Write-Verbose "Error $($error[0]) encountered when attempting to get events from $computer" 
        } 
    } 
# if the Connection attempt failed, write this error… 
  else 
    { 
    Write-Verbose "Failed to connect to $computer" 
    } 
} 

New Items:
• Out-File –append #Simply sends the output to the specified file without overwriting
• Added a second statement to do all the same for the Application log, inside the foreach loop, writing to Application.txt

Results from system.txt:

SERVERACA18.DallasDBAs.com 12/8/2016 2:18:21 PM Error 10016 The description for…
SERVERACA18.DallasDBAs.com 12/8/2016 1:36:37 PM Warning 1014 Name resolution for…
SERVERACA18.DallasDBAs.com 12/8/2016 11:54:19 AM Warning 1014 Name resolution for…
SERVERACA18.DallasDBAs.com 12/8/2016 11:44:20 AM Error 10016 The description for…

MachineName TimeGenerated EntryType Source EventID Message


SERVERACA22.DallasDBAs.com 12/9/2016 10:11:00 AM Warning 1101 The SNMP Service is…
SERVERACA22.DallasDBAs.com 12/9/2016 10:11:00 AM Warning 1101 The SNMP Service is…
SERVERACA22.DallasDBAs.com 12/9/2016 10:10:55 AM Error 5719 This computer was n…

Not great…Message got chopped off, and I don’t really want the headers for each server…but still getting there.

Step 12/Final: Add variables, append to it, and write the contents out to a .csv

#Drop the existing files
Remove-Item B:\DBA\System_Test.csv
Remove-Item B:\DBA\Application_test.csv

# SERVER LIST PROPERTIES
# Get computer list to check disk space. This is just a plain text file with the servers listed out.
$computers = Get-Content "B:\DBA\ServerList.txt";
$sys = @()
$app = @()

# QUERY COMPUTER SYSTEM EVENT LOG
foreach($computer in $computers)


{ 
    # Try to make a connection to each server
    if(Test-Connection $computer -Quiet -Count 1)
      { 
      Try 
          {
          $sys += Get-EventLog -LogName System -EntryType "Error","Warning" ` 
          -After (Get-Date).AddDays(-3) -ComputerName $computer ` 
          | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message ;

          $app += Get-EventLog -LogName Application -EntryType "Error","Warning" ` 
          -After (Get-Date).AddDays(-3) -ComputerName $computer ` 
          | Select-Object -Property machineName, EntryType, EventID, Source, TimeGenerated, Message ; 
          } 
       # if an error in retrieving the logs, write it out 
       Catch 
          { 
          Write-Verbose "Error $($error[0]) encountered when attempting to get events from $computer" 
          } 
     } 
   # If the connection attempt failed, write this error...
   else 
     { 
     Write-Verbose "Failed to connect to $computer" 
     } 
} 
$sys | Export-Csv B:\DBA\System_Test.csv -NoTypeInformation -Append;
$app | Export-Csv B:\DBA\Application_Test.csv -NoTypeInformation -Append; 

New Items:
• Remove-Item – delete the existing files
• $sys = @() – declare a variable (one for system, one for application)
• $sys += – append the results to the variable
• Format-Table – removed
• Out-File – removed
• $sys | Export – Send the contents of the variable to a .csv file

Results:

A screen grab of my System.txt file:

121116_0205_2.png

There is only one set of headers, and the full message is there in column ‘F’, just not showing.   Some details may be missing, as not everything is exposed to Get-EventLog

This allows me to use Excel to sort, filter, group, count, and prioritize the effort needed to cleanup dozens or hundreds of servers. I expect to run this weekly until we get to minimal results, then set up alerts.

I hope you got something useful out of this. It all works on my machine, but is heavily edited for public consumption. Work on your local or in a test lab before you even think about moving to Prod. PowerShell 3.0. May or may not work in lower versions…I have no desire to check.

Shout out to Chris for helping with the very early parts of this process, as well as those who answered my Question1 and Question2 at Stack Overflow.

Update: 1/14/2017:

I decided to enhance this by dumping all the data into one file, and changing the core CmdLet from Get-EventLog to Get-WinEvent, so I can pick up “Critical” events as well as “Error” and “Warning”:

    #Drop the existing files
    Remove-Item C:\Get_WinEvent.csv
    

    # SERVER LIST PROPERTIES
    # Get computer list to check disk space. This is just a plain text file with the servers listed out.
     $computers = Get-Content "C:\ServerList.txt"; 
     $sys = @()
     $st= (Get-Date).AddDays(-7)

    # QUERY COMPUTER SYSTEM EVENT LOG
    foreach($computer in $computers)

{        
 if(Test-Connection $computer -Quiet -Count 1)
        {
   Try {$sys += Get-WinEvent -Computername $computer -FilterHashtable @{logname="System","Application";level=1,2,3;starttime=$st} `
            | Select-Object -Property machinename, LevelDisplayName, LogName, ID, ProviderName, TimeCreated, Message

       } 
   Catch 
       {
       Write-Verbose "Error $($error[0]) encountered when attempting to get events from  $computer"
       }
       } 
   else {
         Write-Verbose "Failed to connect to $computer"
        }
    
}
$sys | Export-Csv C:\Get_WinEvent.csv -NoTypeInformation -Append;

The part that defines the Critical/Error/Warning:  level=1,2,3

Thanks for reading!

Kevin3NF

Filed Under: Beginner, EntryLevel, PowerShell

Dear Junior DBA…

September 2, 2016 by Kevin3NF 2 Comments

Image by Pexels from Pixabay

Congratulations on getting your first SQL Server DBA job!

Presumably you have a tech background or education, and have been through some basic training in SQL Server administration.  I also assume you intended to be a DBA and want to be really good at it so you can advance your career and get mad raises/bonuses.

With any luck at all, you are in an environment where there is at least one other DBA there that knows more than you do.  Ideally a Senior that is really into mentoring that can guide your path.

If not, here are some of the basic things that you may already know how to do in SQL Server Management Studio, but don’t really know the inner workings or the T-SQL to make them happen.

Also, almost everything you can do in current SSMS versions can be scripted.  Look for the script button and click it after you make all of your selections so you can start learning the code behind the GUI.  In time you’ll prefer going straight to the Query Window for some functions of your job.

The list I want my juniors to get intimately familiar with:

  • Backup and Restore…beyond the Maintenance Plans
  • Creating/Deleting databases
  • Creating Logins and Users (and knowing the difference)
  • Creating and maintaining indexes
  • Other database maintenance items
  • Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)
  • Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions

All of these can be setup/monitored in the GUI…so make sure you know all of the options there, and then start working on knowing them deeper. Start with Books Online/MSDN and go from there.

More on each list item:

Backup and Restore – I want you to be able to regurgitate exactly what the difference is between Full, Differential and Transaction Log backups.  I want you to know when you would use each.  You need to know how to restore to a point in time, to another server or as a new database name. Backwards and forwards…this is DBA 101 and the first question I ask if I interview you.  You need to be able to throw down the basic Backup Database syntax on the fly.  Also, recovery models…memorize and understand them (including Bulk-Logged)

 

Creating/Deleting databases – There are many ways to create a database…SSMS, T-SQL, Restore from a backup, deploy from a .dacpac/.bacpac, etc.  Know how to do each, when you would use each, what options are available and how they affect behavior.  Know about filegroups and best practices for laying out your .mdf, .ldf and .ndf files…for your environment.   Know what to do before you delete a database.  Does the requester mean Delete, Detach or just take offline?  What’s the difference?  Know your RPO and RTO by heart.

 

Creating Logins and Users (and knowing the difference) – I’m amazed at the number of experienced people and non-sql people that use the terms interchangeably.  Know the difference. Also learn what server and database roles are. These days, the more you know about the various pieces of the security model in SQL Server the stronger a DBA you are.

 

Creating and maintaining indexes – If your databases are only a few GB, you can almost ignore indexes…but don’t.  Know and be able to explain the difference between clustered and non-clustered indexes.  Understand how to determine what indexes are needed and which existing ones are not. What is an included column?  What is a covering index?  Memorize the ins and outs of Reorganize vs Rebuild and Online vs. Offline maintenance…this matters.  A surprising number of application developers will rely heavily on you for help in this area.

 

Other database maintenance items – DBCC CheckDB and its impact on tempdb.  How to respond to CheckDB errors. Statistics…what are they, do they matter and how do you handle them?  When?

Basics of whatever HA/DR may be in place (Clustering, Log Shipping, Availability Groups)
Know the difference between the various options available and what they are intended to do.  High Availability, Disaster Recovery and Distributed Computing are NOT the same things.  Understand that managers use Replication and Log Shipping interchangeably. Teach them gently.  Your environment may or may not being doing any of this.   Know your RPO and RTO by heart.
Basic performance monitoring via SQL Trace or Extended Events in modern SQL versions – any time you have a chance to get into a performance tuning issue jump on it.  Don’t assume the issue is with SQL server just because someone said so.  Prove it is or isn’t.  Don’t automatically blame the storage or network teams unless you are never going to need them again (you will need them again…).  Start at the server level, then drill down into the DB, then queries…jumping straight to queries may just be a waste of time if a rogue application is stealing all the CPU or leaking memory.
Yes, this is a lot of stuff.  No, you won’t learn all of this today or next week.  If you went from GUI to familiarity with all of this in 6 months I’d be impressed.  Ask your mentors and teammates for help and guidance AFTER you do your research.

 

Also, there is a ton more you will pick up along the way.  Ask questions.  Go to SQL events if possible.  Read blogs from Paul Randal, Brent Ozar and Grant Fritchey (and a bunch of others)

You can make a long career out of just database administration.   But don’t deprive yourself of opportunities to learn about storage, virtualization, cloud computing, development, business intelligence, etc.  The more you know, the more successful you can be.   One of these days, you will be the mentor, not the mentee.  Be kind to the new guy 😉

Kevin3NF

Filed Under: Beginner, Career, EntryLevel Tagged With: career, syndicated

SQL 101 – Recovery models in simple terms

January 10, 2008 by Kevin3NF Leave a Comment

A recovery model is simply a choice made on each database that determines how much data you can recover if your db goes “toes up”.

If you do not backup your database, you have chosen recovery model “terminated” or “update resume”

The 3 that are offered by Microsoft are:

  • Bulk-logged (rarely seen, and generally more advanced, so I’m skipping it)
  • Simple
  • Full

Simple vs. Full is very simply a matter of how much data can you afford to lose if the database is lost and you are forced to restore from your backups.

Simple recovery model: does not allow backing up the transaction logs, therefore you cannot restore any of the data in them. You can only restore to the point of the Full backup and any one Differential you may have.

Full recovery model: You can restore from t-log backups (assuming you have them), right up to a specific point in time. Reduced data loss, assuming the backup files are valid.

When to use:

Simple: When you do not care about the data, such as in a Development or Test environment where you can regenerate from another source. Also useful in a static, read-only database (assuming you have a full backup).

Full: Pretty much any live production database that has Inserts, Updates and Deletes happening.

Switching from one to the other:

Simple to Full: Immediately take a Full or Differential backup

Full to Simple: No specific action required, other than verifying regular data backups are in place and working.

Maintenance plan considerations:

If you have both Simple and Full recovery model databases in your SQL instance, and you create a Maintenance Plan to back up data and logs, you may run into an issue (at least in SQL 2000) where the automated deletion of old t-log backups is failing. Make two plans: one for Full and one for Simple. I have no idea if this issue still presents in SQL 2005.

I hope this is clear…please feel free to comment.

Kevin3NF

Filed Under: Accidental DBA, backup, Beginner, EntryLevel, SQL

  • « Go to Previous Page
  • Go to page 1
  • Interim pages omitted …
  • Go to page 7
  • Go to page 8
  • Go to page 9

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!

Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2025 · WordPress · Log in

 

Loading Comments...