Tag Archives: PowerShell

Find all the unused SQL database files on your SQL server

If your like me you have been frustrated by finding stray mdf, ldf, or ndf files on your SQL server from databases deleted long ago that should have been cleaned up but someone forgot.

To permanently solve this problem I have written a couple of PowerShell scripts and configured a scheduled task to send an email to our DBA group if we ever have SQL database files (mdf, ldf, ndf) sitting on any local drive attached to an SQL server that are not currently being used by a database on that SQL server.

First up we a simple helper function that allows us to run SQL statements from anywhere .net is installed without SQL specific utilities installed:

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
    
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
    
    $connection.Close()
    $dataSet.Tables
}

Next up we have have Get-SQLDatabaseFilesNotUsed:

function Get-SQLDatabaseFilesNotUsed {
    param ( 
        [parameter(Mandatory = $true)][string]$SQLServerName,
        [switch]$IncludeEqual
    )

        $SQLCommand = @"
set nocount on
SELECT DB_NAME([database_id])AS [Database Name], 
        [file_id], name, physical_name, type_desc, state_desc, 
        CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
"@

    $Results = Invoke-Sql -datasource $SQLServerName -database master -SQLCommand $SQLCommand 
    $SQLDatabaseFilesInUse = $Results | select @{Name="fullname";Expression={$_.physical_Name -replace "\\\\","\" <#SQL server allows \\ in paths and just uses it like \#> }}

    $SQLDatabaseAndLogFilesOnDisk = invoke-command -ComputerName $SQLServerName -ScriptBlock { 
        $Filesystems = get-psdrive -PSProvider FileSystem
        Foreach ($FileSystem in $Filesystems) {
            Get-ChildItem $FileSystem.Root -Recurse -include *.mdf,*.ldf,*.ndf -File -ErrorAction SilentlyContinue | select fullname 
        }
    }

    $SQLDatabaseAndLogFilesOnDisk = $SQLDatabaseAndLogFilesOnDisk | 
    Where fullname -notlike "C:\windows\winsxs*" | 
    Where fullname -notlike "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\*" |
    select fullname
    
    
    Compare-Object -ReferenceObject $SQLDatabaseFilesInUse -DifferenceObject $SQLDatabaseAndLogFilesOnDisk -Property FullName -IncludeEqual:$IncludeEqual | FT -AutoSize

}

One thing to note, if you have additional database files that you want to keep on disk and exclude from this check just add another “Where fullname –notlike “<Path where you have files you don’t want checked>” |” to the others you see that filter out some files that share the same extensions as SQL database files even though they are not databases.

Lastly we have a script file Test-SQLDatabaseFilesNotUsed.ps1 that will want to call from your scheduled task:

. c:\scripts\Invoke-SQL.ps1
. c:\scripts\Get-SQLDatabaseFilesNotUsed.ps1

$OutputMessage = ""

$FromAddress = "scheduledtasks@tervis.com"
$ToAddress = "WindowsServerApplicationsAdministrator@tervis.com"
$Subject = "SQL Server has data or log files that are not being used"
$SMTPServer = "cudaspam.tervis.com"

$OutputMessage = Get-SQLDatabaseFilesNotUsed -SQLServerName sql

if ($OutputMessage)
{
    Send-MailMessage -From $FromAddress -to $ToAddress -subject $Subject -SmtpServer $SMTPServer -Body ($OutputMessage | FT -autosize | out-string -Width 200) 
}

Now save all of these in a C:\scripts directory and create a scheduled task with whatever schedule you like (I have ours run once per week) that executes:

Powershell.exe -noprofile -file c:\scripts\Test-SQLDatabaseFilesNotUsed.ps1

This has saved me a +100GB already and from this point forward you won’t have to worry if you have space being used up but the database files that time forgot.

Tagged

Find all files with NTFS Alternate Data Streams using PowerShell

  1. Open Powershell
  2. cd to the directory or the root of a drive where you want to recursively look for alternate data streams
  3. Run the following command (requires PowerShell V3)

gci -recurse | % { gi $_.FullName -stream * } | where stream -ne ':$Data'

To test that this works properly navigate to a random directory on your computer and run the following commands:

new-item test.txt -Type file
Add-Content test.txt &quot;test content&quot;
Add-Content test.txt -str HiddenStream &quot;Hidden test content stored in the stream&quot;
get-content .\test.txt
get-content test.txt -str HiddenStream

Now navigate to some folder above the directory where you ran these commands and run the command to find all files with alternate data streams and you should see our test.txt file come up.

For a more complete description of what you can do with NTFS Alternate Data Streams in PowerShell V3 please check out this blog post.

Tagged

Clean up Active Directory Computers and Users with stock PowerShell

Here two commands that will quickly help you delete computers and users from active directory that havn’t logged on for over a month.

For computers use:

Get-ADComputer -properties LastLogonDate -filter * | where LastLogonDate -LT (get-date).AddDays(-30) | Remove-ADObject -Recursive

For users use:

Get-ADUser -properties LastLogonDate -filter * | where LastLogonDate -LT (get-date).AddDays(-30) | Remove-ADObject -Recursive

Both commands will prompt you before deleting the object so that you can sanity check each deletion before it occurs and if your unsure simply choose not to delete that object.

It’s quick, easy, and if you simply delete the things your sure you can delete you will likely be leaps ahead of where you started.

Tagged ,

Get-Member lies

Get-Member lies.

Let me show you.

Here are some arrays:

$Array = @()
$Array2 = 1,2
$Array3 = "Thing1","Thing2"

Here is what GM says about these arrays:

PS H:\> $Array | GM
GM : No object has been specified to the get-member cmdlet.
At line:1 char:10
+ $Array | GM
+          ~~
+ CategoryInfo          : CloseError: (:) [Get-Member], InvalidOperationException
+ FullyQualifiedErrorId : NoObjectInGetMember,Microsoft.PowerShell.Commands.GetMemberCommand

PS H:\> $Array2 | GM


TypeName: System.Int32

Name        MemberType Definition
----        ---------- ----------
CompareTo   Method     int CompareTo(System.Object value), int CompareTo(int value), int

…


PS H:\> $Array3 | GM


TypeName: System.String

Name             MemberType            Definition
----             ----------            ----------
Clone            Method                System.Object Clone(), System.Object ICloneable.Clone()
CompareTo        Method                int CompareTo(System.Object value), int CompareTo(string strB), int IComparab...

…

LIES, ALL LIES!

Here is how you force GM to tell you the truth, we use , . That is right ,, this small period that grew a tail is the truth serum to your filthy lying GM problems.

Let me demonstrate:

PS H:\> ,$Array|GM


TypeName: System.Object[]

…

PS H:\> ,$Array2|GM


TypeName: System.Object[]

…

PS H:\> ,$Array3|GM


TypeName: System.Object[]

…

Now we can clearly see the true type of the object and the methods that are available to us.

Thanks goes out to Marco Shaw for providing this solution here,

Update 2013-02-12:

If you open powershell and run get-help about_array, then navigate down to the heading GET THE MEMBERS OF AN ARRAY you will find the following:

GET THE MEMBERS OF AN ARRAY

To get the properties and methods of an array, such as the Length
property and the SetValue method, use the InputObject parameter of the
Get-Member cmdlet.

When you pipe an array to Get-Member, Windows PowerShell sends the items
one at a time and Get-Member returns the type of each item
in the array (ignoring duplicates).

When you use the InputObject parameter, Get-Member returns the
members of the array.

For example, the following command gets the members of the array in the
$a variable.

Get-Member -InputObject $a

You can also get the members of an array by typing a comma (,) before
the value that is piped to the Get-Member cmdlet. The comma makes the
array the second item in an array of arrays. Windows PowerShell pipes
the arrays one at a time and Get-Member returns the members of the array.

,$a | Get-Member

,(1,2,3) | Get-Member

There you have it, the comma forces PowerShell to dynamically create an array of array’s and then get-member gets the type of the arrays within the array thereby giving us the actual properties of the array we are interested in.

Tagged