Category Archives: Microsoft

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

How to copy a function in Dynamics NAV C/Side

From the Functions list right click on the bar to the right and click copy, select a new blank line, and click paste.

I know, dead simple right? Some times it helps to have these things out there as it’s easy to miss that this is possible in C/Side.

Tagged , ,

Dealing with multiple parameters passed to exe in powerhsell

If you have ever needed to call an exe application from PowerShell and needed to pass multiple parameters to the exe then the technique I outline here will really help you out.

Declare a variable that holds the executable name as a string and any other variables you might want to use

$pfm = “pfm”

$MountPath = “T:”

As a side note the application I am calling is Pismo File Mount which is by far the best free (even for commercial use) software I have ever used for mounting ISOs

Declare all your parameters as an object array

$Mount = “mount”,”-m”,”$MountPath”

Now in your code you can do the following

& $pfm $Mount “$MSDNIsoPath\en_visual_studio_2010_premium_rc_x86_dvd_489527.iso”

If I wanted to write out all my parameters every time I could always write in PowerShell:

Pfm mount -m “$MountPath” “$MSDNIsoPath\en_visual_studio_2010_premium_rc_x86_dvd_489527.iso”

I can’t say as I have figured out why this works this way and if anyone has an explanation I would love to hear it in the comments.

How to rebase differencing disk VHDs (or how to change the parent of a differencing VHD when the parent has moved)

I am a big fan of differencing VHDs as the provide tremendous power when working with large database but they also come with their own set of complications.

One of those complications that can arise is when due to files being moved or folder structures being reorganized the parent VHD of your differencing VHD is no longer accessible. After you try and attach your differencing VHD you may see an error message like this one:

Virtual Disk Manager The network path was not found.

We can use the following power shell from a Windows 7 PC with the latest version of Microsoft Virtual PC that comes with Windows XP Mode installed to take a look at the parent property of our differencing disk to see if something is wrong

$vpc=new-object -com VirtualPC.Application
$VHDChild = $vpc.GetHardDisk("C:\NAV2010.06.10_Diff")
$VHDChild
$VHDChild.Parent

image

In my case I recently changed the IP address of a NAS that was storing the parent VHD and assigned a DNS record to the NAS so that I wouldn’t have to keep track of it’s IP address but now the path to the parent VHD that is stored in my differencing VHD is no longer valid.

Thankfully this is fairly easy to fix. All we need to do is add the following code so that we get a new $VHDParent object using the corrected path and then assign that object to the Parent property of our differencing disk

$VHDParent = $vpc.GetHardDisk("\\devnas\Data_Backups SQLCluster1\NAV2010.06.10.vhd")
$VHDChild.Parent = $VHDParent
$VHDChild.Parent

image

Now we can attach our differencing VHD without error and get back to work =).

How to create a differencing VHD that refers to a parent VHD that is on a network share from Windows 7

I love the fact that Windows 7 can attach VHD disks as if they were local hard disks. This allows me to pull off some very interesting stuff that I will blog about later but one pain point has been that through the UI with only Windows 7 I cannot create a differencing VHD from a parent VHD that is stored at a UNC path on the network.

The solution to this is to install the latest version of Microsoft Virtual PC which can be downloaded form the Windows XP Mode website. After installing this you are now able to create differencing disks based on parent VHDs that are stored on your network at a UNC path as shown below:

$vpc=new-object -com VirtualPC.Application
$vpc.CreateDifferencingVirtualHardDisk("\\devnas\Data_Backups\SQLCluster1\NAV 2010.08.20 Differencing.vhd","\\devnas\Data_Backups\SQLCluster1\NAV 2010.08.20 Full.vhd")

Note: If you still have the parent VHD attached to a machine this command will silently fail. Make sure that the parent is not being used and is not attached to any machine before running this.

In a future post I will talk about some of the use cases for this technique that make it invaluable.

Hint: Multiple 300GB+ database development from a laptop with only an 120 GB hard drive as if all DBs were local

Syntax you may not remember in C/AL

Using IN with values from an option field to test whether a field is set to one of the values in a subset of the total options.

IF (Status IN [Status::Open, Status::Authorized, Status::Closed] = TRUE) THEN BEGIN

Keyboard shortcuts that every Dynamics NAV developer should know

Legend:
A ‘,’ separates items that come in a sequence for instance Alt, F, S would mean press Alt, let go, press F, let go, and finally press S.
A ‘+’ separates keys that are suppose to be pressed at the same time. Alt + D would mean press and hold Alt while pressing D

From the C/AL Editor window

ALT, V, B – Shows the C/AL globals
ALT, V, A – Shows the C/AL locals

From the Object Designer

ALT + D – Design the current object
ALT + R – Run the current object

From either

ALT > F > S to save
CTRL + F to find

Control Windows Virtual PC from PowerShell

I have used the command line interface (CLI) for Sun’s Virtual Box and for Hyper V but I didn’t realize that Virtual PC also can be accessed through PowerShell. This might be the key to creating network based differencing disks natively on Windows 7 which currently isn’t something that can be done in a straightforward way through the GUI and even the way that it can be done has limitations (like not being able to create a differencing disk with the parent disk on the network).

</pre>
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.
PS H:\> $vpc=new-object -com VirtualPC.Application
PS H:\> $vpc
HostInfo                    : System.__ComObject
VirtualMachines             : System.__ComObject
VirtualNetworks             : System.__ComObject
UnconnectedNetworkAdapters  : System.__ComObject
SupportDrivers              : System.__ComObject
Tasks                       : System.__ComObject
MinimumMemoryPerVM          : 4
MaximumMemoryPerVM          : 3575
SuggestedMaximumMemoryPerVM : 3217
MaximumFloppyDrivesPerVM    : 1
MaximumSerialPortsPerVM     : 2
MaximumParallelPortsPerVM   : 1
MaximumNetworkAdaptersPerVM : 4
MaximumNumberOfIDEBuses     : 2
DefaultVMConfigurationPath  : C:\Users\cmagnuson\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\
SearchPaths                 : {}
USBDeviceCollection         : System.__ComObject
Name                        : Windows Virtual PC
Version                     : 6.1.7600.16393
UpTime                      : 6
PS H:\> $vm = $vpc.FindVirtualMachine(
>>
>>
>>
PS H:\> $vm = $vpc.FindVirtualMachine("IE 6")
PS H:\> $vm
Name                  : IE 6
ConfigID              : {DB33D85B-1FAC-4A67-910A-33EE4A4F06FC}
File                  : C:\Users\cmagnuson\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\IE 6.vmc
Accountant            : System.__ComObject
GuestOS               : System.__ComObject
Display               : System.__ComObject
Keyboard              : System.__ComObject
Mouse                 :
State                 : 2
ShutdownActionOnQuit  : 0
SavedStateFilePath    : C:\Users\cmagnuson\AppData\Local\Microsoft\Windows Virtual PC\Virtual Machines\IE 6.vsv
BIOSGUID              : {D5356F8F-4383-4B68-ACDA-03D1A06E5C46}
BIOSSerialNumber      : 8425-1374-7407-2656-1007-9980-44
BaseBoardSerialNumber : 8425-1374-7407-2656-1007-9980-44
ChassisSerialNumber   : 8425-1374-7407-2656-1007-9980-44
ChassisAssetTag       : 1746-8710-8930-6641-7796-6780-67
HardDiskConnections   : System.__ComObject
Undoable              : False
UndoAction            : 1
DVDROMDrives          : System.__ComObject
FloppyDrives          : System.__ComObject
NetworkAdapters       : System.__ComObject
SerialPorts           : System.__ComObject
ParallelPorts         : System.__ComObject
ProcessorSpeed        : 2926
HasMMX                : True
HasSSE                : True
HasSSE2               : False
Has3DNow              : False
RdpPipeName           :
Notes                 :
Memory                : 1024
AttachedDriveTypes    : {1, 0, 2, 0}
PS H:\>

Original Source: http://blogs.msdn.com/b/virtual_pc_guy/archive/2009/07/14/windows-virtual-pc-and-powershell.aspx

Foreach in CAL

So when I go to do this again I will have something to refer back to:

WITH r_RecordVariableName DO BEGIN
  // SetFilters and SetRanges
  IF FIND('-') THEN BEGIN
    REPEAT
      ForEachRecordDoThis();
    UNTIL NEXT = 0;
  END;
END;

Tagged ,