Whilst I have many other methods to inform me if backups fail, sometimes someone walks up to the desk ignores the headphones in my ears (They are bright yellow FFS), eyes fixed on the screen, face deep in concentration and asks me a question. It was for times like these that the functions from this series were written.
“When was this database last backed up?”
Auditors, managers, bosses, developers all can come and ask this question. I just turn to my PowerShell prompt type
and show him the screen. Job done (Sometimes)
As I mentioned previously there are 154 properties on the database object. Three of them are
Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!
Yesterday we looked at Reading Todays SQL Error Log Today we are going to search all* of the SQL Error Logs. This is usually used by DBAs to troubleshoot issues
The SQL Server Error Logs (by default) are located in the folder Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and are named as ERRORLOG.n files. The most recent has no extension the rest 1 to 6.
Using PowerShell you can easily find the location of the SQL Error Log using the ErrorLogPath Property
You can also read it with PowerShell using the ReadErrorLog Method. This has the following properties LogDate, Processinfo and Text. You can easily filter by any of those with a bit of PowerShell 🙂
I have created a function which takes two parameters $SearchTerm and $SQLServer adds *’s to the Search Term to allow wildcards and searches each of the SQL Error Logs
Simply call it like this and use the results as needed
Of course, as the results are an object you can then carry on and do other things with them
DBAs need to read the error log for many reasons and there are different ways to do it. sp_readerrorlog, xp_readerrorlog, using SSMS opening the file in notepad. I’m sure every DBA has their own favourite. This one is mine.Of course, it uses PowerShell
It is very simple as there is a method on the server property called ReadErrorLog.
In this function I read the latest Error Log and filter it for the last 24 hours using the Get-Date cmdlet and the AddDays Method
Here is the output
You can also save the output to a text file and open it by piping the function to Out-File
Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!
Todays question which I often get asked is What databases are on that server?
This is often a follow up to a question that requires the Find-Database script. It is often asked by support teams investigating issues. It can also be asked by developers checking the impact of other services on their DEV/UAT environments, by change managers investigating impact of changes, by service managers investigating the impact of downtime, when capacity planning for a new service and numerous other situations.
A simple quick and easy question made simpler with this function which can also be called when creating documentation
Simply call it with Show-DatabasesOnServer SERVERNAME and use the results as you need
This only shows you the name but if you need more information about your databases then have a look and see what you require.
Use Get-Member to see what is there. I ran the following code to count the number of Properties available for Databases (Using PowerShell V3 on SQL Server 2012 SP1 11.0.3350.0 )
154 Properties that you can examine and that is just for databases:-)
Picking out a few properties you could do something like this
If you want aliases for your column headings you will need to add a bit of code to the select.
For Example, maybe you want to Database Name as a heading and the Size in Gb (Its in Mb in the example above) You would need to create a hash table with a Label element and an Expression element. The Label is the column heading and the Expression can just be the data or a calculation on data.
<#PSScriptInfo
.VERSION 1.0
.GUID 48bf0316-66c3-4253-9154-6fc5b28e482a
.AUTHOR Rob Sewell
.DESCRIPTION Returns Database Name and Size in MB for databases on a SQL server
.COMPANYNAME
.COPYRIGHT
.TAGS SQL, Database, Databases, Size
.LICENSEURI
.PROJECTURI
.ICONURI
.EXTERNALMODULEDEPENDENCIES
.REQUIREDSCRIPTS
.EXTERNALSCRIPTDEPENDENCIES
.RELEASENOTES
#>
<#
.Synopsis
Returns the databases on a SQL Server and their size
.DESCRIPTION
Returns Database Name and Size in MB for databases on a SQL server
.EXAMPLE
Show-DatabasesOnServer
This will return the user database names and sizes on the local machine default instance
.EXAMPLE
Show-DatabasesOnServer -Servers SERVER1
This will return the database names and sizes on SERVER1
.EXAMPLE
Show-DatabasesOnServer -Servers SERVER1 -IncludeSystemDatabases
This will return all of the database names and sizes on SERVER1 including system databases
.EXAMPLE
Show-DatabasesOnServer -Servers 'SERVER1','SERVER2\INSTANCE'
This will return the user database names and sizes on SERVER1 and SERVER2\INSTANCE
.EXAMPLE
$Servers = 'SERVER1','SERVER2','SERVER3'
Show-DatabasesOnServer -Servers $servers|out-file c:\temp\dbsize.txt
This will get the user database names and sizes on SERVER1, SERVER2 and SERVER3 and export to a text file c:\temp\dbsize.txt
.NOTES
AUTHOR : Rob Sewell http://sqldbawithabeard.com
Initial Release 22/07/2013
Updated with switch for system databases added assembly loading and error handling 20/12/2015
Some tidying up and ping check 01/06/2016
#>
Function Show-DatabasesOnServer
{
[CmdletBinding()]
param (
# Server Name or array of Server Names - Defaults to $ENV:COMPUTERNAME
[Parameter(Mandatory = $false,
ValueFromPipeline = $true,
ValueFromPipelineByPropertyName = $true,
Position = 0)]
$Servers = $Env:COMPUTERNAME,
# Switch to include System Databases
[Parameter(Mandatory = $false)]
[switch]$IncludeSystemDatabases
)
[void][reflection.assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo" );
foreach ($Server in $Servers) {
if ($Server.Contains('\')) {
$ServerName = $Server.Split('\')[0]
$Instance = $Server.Split('\')[1]
}
else {
$Servername = $Server
}
## Check for connectivity
if ((Test-Connection $ServerName -count 1 -Quiet) -eq $false) {
Write-Error "Could not connect to $ServerName - Server did not respond to ping"
$_.Exception
continue
}
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server
if ($IncludeSystemDatabases) {
try {
$Return = $srv.databases| Select Name, Size
}
catch {
Write-Error "Failed to get database information from $Server"
$_.Exception
continue
}
}
else {
try {
$Return = $srv.databases.Where{$_.IsSystemObject -eq $false}| Select Name, Size
}
catch {
Write-Error "Failed to get database information from $Server"
$_.Exception
continue
}
}
Write-Output "`n The Databases on $Server and their Size in MB `n"
$Return
}
}
I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever. I call it my PowerShell Box of Tricks
I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.
Todays post is not about a question but about a routine task DBAs do. Dropping Logins
Whilst best practice says add users to active directory groups, add the group to roles and give the roles the correct permissions there are many situations where this is not done and DBAs are required to manually remove logins. This can be a time consuming task but one that is essential. There was a time at MyWork when this was achieved via a script that identified which servers had a users login and the task was to connect to each server in SSMS and remove the user from each database and then drop the server login. As you can imagine it was not done diligently. Prior to an audit I was tasked with ensuring that users that had left MyWork did not have logins to any databases. It was this that lead to the Checking for SQL Logins script and to this one
It starts exactly the same as the Checking for SQL Logins script by grabbing the list of SQL Servers from the text file and creating an array of user names including all the domains as I work in a multi-domain environment
Then iterate through each database ignoring those that may need special actions due to the application and call the drop method
Repeat the process for the servers and send or save the report as required. Simple and easy and has undoubtedly saved me many hours compared to the previous way of doing things 🙂
IMPORTANT NOTE
This script will not delete logins if they have granted permissions to other users. I always recommend running the Checking for SQL Logins script after running this script to ensure all logins have been dropped
This script can be found
#############################################################################################
#
# NAME: Drop-SQLUsers.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:06/08/2013
#
# COMMENTS: Load function to Display a list of server, database and login for SQL servers listed
# in sqlservers.txt file and then drop the users
#
# I always recommend running the Checking for SQL Logins script after running this script to ensure all logins have been dropped
#
# Does NOT drop Users who have granted permissions
#BE CAREFUL
Function Drop-SQLUsers ($Usr) {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Suppress Error messages - They will be displayed at the end
$ErrorActionPreference = "SilentlyContinue"
# cls
# Pull a list of servers from a local text file
$servers = Get-Content 'c:\temp\sqlservers.txt'
# Create an array for the username and each domain slash username
$logins = @("DOMAIN1\$usr", "DOMAIN2\$usr", "DOMAIN3\$usr" , "$usr")
Write-Output "#################################"
Write-Output "Dropping Logins for $Logins"
#loop through each server and each database and
Write-Output "#########################################"
Write-Output "`n Database Logins`n"
foreach ($server in $servers) {
if (Test-Connection $Server -Count 1 -Quiet) {
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
#drop database users
foreach ($database in $srv.Databases) {
if ($database -notlike "*dontwant*") {
foreach ($login in $logins) {
if ($database.Users.Contains($login)) {
$database.Users[$login].Drop();
Write-Output " $server , $database , $login - Database Login has been dropped"
}
}
}
}
}
}
Write-Output "`n#########################################"
Write-Output "`n Servers Logins`n"
foreach ($server in $servers) {
if (Test-Connection $Server -Count 1 -Quiet) {
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
#drop server logins
foreach ($login in $logins) {
if ($srv.Logins.Contains($login)) {
$srv.Logins[$login].Drop();
Write-Output " $server , $login Login has been dropped"
}
}
}
}
Write-Output "`n#########################################"
Write-Output "Dropping Database and Server Logins for $usr - Completed "
Write-Output "If there are no logins displayed above then no logins were found or dropped!"
Write-Output "###########################################"
}
I decided it was time to write a Create-Database function using a number of scripts that I have used to create individual databases.
Errors
Whilst finalising the function I didn’t quite get it right sometimes and was faced with an error.
Not the most useful of errors to troubleshoot. The issue could be anywhere in the script
You can view the last errors PowerShell has shown using $Errors. This gives you the last 500 errors but you can see the last error by using $Error[0] if you pipe it to Format-List you can get a more detailed error message so I added a try catch to the function which gave me an error message I could resolve.
Much better. The problem was
Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\.LDF’ because it already exists.
Mistyping a variable has caused this. Creating an empty file name variable which then threw the error the second(and third,fourth fifth) times I ran the script but this error pointed me to it.
Creating Database
There are a vast number of variables you can set when creating a database. I decided to set File Sizes, File Growth Sizes, Max File Sizes and Recovery Model. I only set Server and Database Name as mandatory parameters and gave the other parameters default values
We take the parameters for file sizes in MB and set them to KB
Then set the default file locations. Create a database object, a Primary file group object and add the file group object to the database object
Add a User File Group for User objects
Create a database file on the primary file group using the variables set earlier
Do the same for the user file and then create a Log File
Set the Recovery Model and create the database and then set the user file group as the default
Finally catch the errors
It can then be called as follows Create-Database SERVERNAME DATABASENAME
or by setting all the parameters Create-Database -Server Fade2black -DBName DatabaseTest -SysFileSize 10 -UserFileSize 15 -LogFileSize 20 -UserFileGrowth 7 -UserFileMaxSize 150 -LogFileGrowth 8 -LogFileMaxSize 250 -DBRecModel FULL
This means that I can easily and quickly set up several databases of different types and sizes
The script can be found here
#############################################################################################
#
# NAME: Create-Database.ps1
# AUTHOR: Rob Sewell http://sqldbawithabeard.com
# DATE:08/09/2013
#
# COMMENTS: Load function for creating a database
# Only Server and DB Name are mandatory the rest will be set to small defaults
#
# USAGE: Create-Database -Server Fade2black -DBName Test35 -SysFileSize 10 -UserFileSize 15 -LogFileSize 20
# -UserFileGrowth 7 -UserFileMaxSize 150 -LogFileGrowth 8 -LogFileMaxSize 250 -DBRecModel FULL
# ————————————————————————
Function Create-Database {
Param(
[Parameter(Mandatory = $true)]
[String]$Server ,
[Parameter(Mandatory = $true)]
[String]$DBName,
[Parameter(Mandatory = $false)]
[int]$SysFileSize = 5,
[Parameter(Mandatory = $false)]
[int]$UserFileSize = 25,
[Parameter(Mandatory = $false)]
[int]$LogFileSize = 25,
[Parameter(Mandatory = $false)]
[int]$UserFileGrowth = 5,
[Parameter(Mandatory = $false)]
[int]$UserFileMaxSize = 100,
[Parameter(Mandatory = $false)]
[int]$LogFileGrowth = 5,
[Parameter(Mandatory = $false)]
$LogFileMaxSize = 100,
[Parameter(Mandatory = $false)]
[String]$DBRecModel = 'FULL'
)
try {
# Set server object
$srv = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $server
$DB = $srv.Databases[$DBName]
# Define the variables
# Set the file sizes (sizes are in KB, so multiply here to MB)
$SysFileSize = [double]($SysFileSize * 1024.0)
$UserFileSize = [double] ($UserFileSize * 1024.0)
$LogFileSize = [double] ($LogFileSize * 1024.0)
$UserFileGrowth = [double] ($UserFileGrowth * 1024.0)
$UserFileMaxSize = [double] ($UserFileMaxSize * 1024.0)
$LogFileGrowth = [double] ($LogFileGrowth * 1024.0)
$LogFileMaxSize = [double] ($LogFileMaxSize * 1024.0)
Write-Output "Creating database: $DBName"
# Set the Default File Locations
$DefaultDataLoc = $srv.Settings.DefaultFile
$DefaultLogLoc = $srv.Settings.DefaultLog
# If these are not set, then use the location of the master db mdf/ldf
if ($DefaultDataLoc.Length -EQ 0) {$DefaultDataLoc = $srv.Information.MasterDBPath}
if ($DefaultLogLoc.Length -EQ 0) {$DefaultLogLoc = $srv.Information.MasterDBLogPath}
# new database object
$DB = New-Object ('Microsoft.SqlServer.Management.SMO.Database') ($srv, $DBName)
# new filegroup object
$PrimaryFG = New-Object ('Microsoft.SqlServer.Management.SMO.FileGroup') ($DB, 'PRIMARY')
# Add the filegroup object to the database object
$DB.FileGroups.Add($PrimaryFG )
# Best practice is to separate the system objects from the user objects.
# So create a seperate User File Group
$UserFG = New-Object ('Microsoft.SqlServer.Management.SMO.FileGroup') ($DB, 'UserFG')
$DB.FileGroups.Add($UserFG)
# Create the database files
# First, create a data file on the primary filegroup.
$SystemFileName = $DBName + "_System"
$SysFile = New-Object ('Microsoft.SqlServer.Management.SMO.DataFile') ($PrimaryFG , $SystemFileName)
$PrimaryFG.Files.Add($SysFile)
$SysFile.FileName = $DefaultDataLoc + $SystemFileName + ".MDF"
$SysFile.Size = $SysFileSize
$SysFile.GrowthType = "None"
$SysFile.IsPrimaryFile = 'True'
# Now create the data file for the user objects
$UserFileName = $DBName + "_User"
$UserFile = New-Object ('Microsoft.SqlServer.Management.SMO.Datafile') ($UserFG, $UserFileName)
$UserFG.Files.Add($UserFile)
$UserFile.FileName = $DefaultDataLoc + $UserFileName + ".NDF"
$UserFile.Size = $UserFileSize
$UserFile.GrowthType = "KB"
$UserFile.Growth = $UserFileGrowth
$UserFile.MaxSize = $UserFileMaxSize
# Create a log file for this database
$LogFileName = $DBName + "_Log"
$LogFile = New-Object ('Microsoft.SqlServer.Management.SMO.LogFile') ($DB, $LogFileName)
$DB.LogFiles.Add($LogFile)
$LogFile.FileName = $DefaultLogLoc + $LogFileName + ".LDF"
$LogFile.Size = $LogFileSize
$LogFile.GrowthType = "KB"
$LogFile.Growth = $LogFileGrowth
$LogFile.MaxSize = $LogFileMaxSize
#Set the Recovery Model
$DB.RecoveryModel = $DBRecModel
#Create the database
$DB.Create()
#Make the user filegroup the default
$UserFG = $DB.FileGroups['UserFG']
$UserFG.IsDefault = $true
$UserFG.Alter()
$DB.Alter()
Write-Output " $DBName Created"
Write-Output "System File"
$SysFile| Select Name, FileName, Size, MaxSize, GrowthType| Format-List
Write-Output "User File"
$UserFile| Select Name, FileName, Size, MaxSize, GrowthType, Growth| Format-List
Write-Output "LogFile"
$LogFile| Select Name, FileName, Size, MaxSize, GrowthType, Growth| Format-List
Write-Output "Recovery Model"
$DB.RecoveryModel
}
Catch {
$error[0] | fl * -force
}
}
Whilst using my Drop-SQLLogins function, which is one of my PowerShell Box Of Tricks series, it failed to delete logins on some servers with the error
Login domain\user’ has granted one or more permissions. Revoke the permission before dropping the login (Microsoft SQL Server, Error: 15173)
I used the Show-SQLPermissions function and added the .grantor property to try and locate the permission the account had granted but it came back blank. A bit of googling and a AHA moment and I remembered mirroring
I checked the mirroring endpoints
and found the endpoints with the user as the owner so I needed to change them
This can be done in T-SQL as follows
but to do it on many endpoints it is easier to do it with Powershell
I could then drop the user successfully
$svrs = ## list of servers Get-Content from text fiel etc
foreach ($svr in $svrs) {
$server = New-Object Microsoft.SQLServer.Management.Smo.Server $svrs
foreach ($endpoint in $server.Endpoints['Mirroring']) {
if ($endpoint.Owner = 'Domain\User') {
$endpoint.Owner = 'Domain\NEWUser'
$endpoint.Alter()
}
}
}
I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks
I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.
Todays question which I often get asked is Which server is that database on?
It isn’t always asked like that. Sometimes it is a developer asking where the database for their UAT/SAT/FAT/Dev environment is. Sometimes it is a manager who requires the information for some documentation or report. I wrote it because whilst I am good, I am not good enough to remember the server location for many hundreds of databases and also because I wanted to enable a new starter to be as self sufficient as possible
When I first wrote this I thought it would be a case of simply using $databaseName.Contains($DatabaseNameSearch) but this did not work for MiXed case searches or DatAbaSe names so I had to convert both the search and the database name to lower case first
I create an empty hash table and then populate it with the results
Set a results variable to the names from the hash table and count the number of records
and call it like this
Note that the search uses the contains method so no need for wildcards
Results come out like this
You can find the code here
#############################################################################################
#
# NAME: Find-Database.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:22/07/2013
#
# COMMENTS: Load function for finding a database
# USAGE: Find-Database DBName
# ������������������������
Function Find-Database ([string]$Search) {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Pull a list of servers from a local text file
$servers = Get-Content 'sqlservers.txt'
#Create an empty Hash Table
$ht = @{}
$b = 0
#Convert Search to Lower Case
$DatabaseNameSearch = $search.ToLower()
Write-Output "#################################"
Write-Output "Searching for $DatabaseNameSearch "
Write-Output "#################################"
#loop through each server and check database name against input
foreach ($server in $servers) {
if (Test-Connection $Server -Count 1 -Quiet) {
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server
foreach ($database in $srv.Databases) {
$databaseName = $database.Name.ToLower()
if ($databaseName.Contains($DatabaseNameSearch)) {
$DatabaseNameResult = $database.name
$Key = "$Server -- $DatabaseNameResult"
$ht.add($Key , $b)
$b = $b + 1
}
}
}
}
$Results = $ht.GetEnumerator() | Sort-Object Name|Select Name
$Resultscount = $ht.Count
if ($Resultscount -gt 0) {
Write-Output "############### I Found It!! #################"
foreach ($R in $Results) {
Write-Output $R.Name
}
}
Else {
Write-Output "############ I am really sorry. I cannot find" $DatabaseNameSearch "Anywhere ##################### "
}
}
I have developed a series of PowerShell functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I call it my PowerShell Box of Tricks and this is another post in the series.
Todays question which I often get asked is How much space is free on the drive?
A question often asked by developers during development and by DBAs when looking at provisioning new databases so I use this simple function to return the drive sizes using a WMI call with PowerShell
I first write the date out to the console with the Server name as I found that useful to show how much space had been freed when archiving data. Then a WMI query and a bit of maths and output to the console. The /1GB converts the drive size to something meaningful and you can see how the PercentFree is calculated from the two values using “{0:P2}”
Alerting of issues across the SQL Server estate is important and recently I needed to audit the operators and the notifications that they were receiving.
I created a SQL Server Object
One of the important things to remember when investigating SMO is the Get-Member cmdlet. This will show all methods and properties of the object
$server | Get-Member
gave me the JobServer Property
$Server.JobServer|gm
includes the Operator Property
$Server.JobServer.Operators | gm
has the EnumJobNotifications and EnumNotifications methods
So it was easy to loop through each server in the servers.txt file and enumerate the notifications for each Operator
and create a simple report
However this does not work as it does not perform the second enumerate. Try it yourself, switch round the EnumJobNotifications and EnumNotifications methods in that script and see what happens.
So I ended up with two functions
and I thought I could do this
But that doesnt work
So I tried this
and that doesnt work either
Now the reports are coming out showing the correct number of lines but not displaying them. I spent a period of time on my Azure boxes trying to work a way around this. I set the outputs to both enums to a variable and noted that they are different type of objects.
Job Notifications are System.Object and Alert Notifications are System.Array
I tried to enumerate through each member of the array and display them but got too tired to finish but I had contacted my friend Stuart Moore Twitter | Blog who had a look and resolved it by simply piping the Enumerates to Format-Table. Thank you Stuart.