This page looks best with JavaScript enabled

Shrink Direct Access Database using PowerShell.

 ·  ☕ 3 min read  ·  ✍️ Javy de Koning

We recently ran into an issue where the a system was running out of space on the system-drive. After initial investigation we’ve discovered that Direct Access Database Windows Internal Database was consuming a lot of drive-space on a system-drive. Below I will explain how to shrink Direct Access database

Direct Access folder size

In the output above we used RoboCopy to calculate the folder sizes, if you want to know more about that, read my previous blog post here: Faster Dir Size calculations in PowerShell!

After spending some time searching the Internet, I couldn’t find a guide on how to shrink the Direct Access Windows Internal Database. Hence, I’ve created a PowerShell module. The first cmdlet will show you the exact size of the DA DataBase.

1Get-DaDatabaseSize | ft -auto
2
3Name                  Length
4----                  ------
5RaAcctDb.mdf     14996799488
6RaAcctDb_log.ldf 12727346176

The Second cmdlet shrinks the DB

 1Shrink-DaDatabase
 2Get-DaDatabaseSize | ft -auto
 3
 4Name                  Length
 5----                  ------
 6RaAcctDb.mdf     14988476416
 7RaAcctDb_log.ldf     1048576
 8
 9(12727346176-1048576)/1GB
1011.8522882461548

11.8GB recovered!

Shrink Direct Access Database – PowerShell module

You can find the PowerShell module on my GitHub page here:

javydekoning/ShrinkDaDataBase - GitHub

Also a thank you to Jaap Brasser who created FixDaDatabase which was a great starting point for this module.

jaapbrasser/FixDaDatabase - GitHub

Full code:

  1function Get-DaDatabaseSize {
  2  <#
  3      .Synopsis
  4      Checks if the Direct Access Database size
  5
  6      .DESCRIPTION
  7      This function connects to the Windows Internal Database (WID) in order to check the DirectAccess DB file sizes.
  8
  9      .NOTES   
 10      Name:        Get-DaDatabaseSize
 11      Author:      Javy de Koning
 12      Version:     1.0.0
 13      DateCreated: 2016-10-04
 14      DateUpdated: 2016-10-04
 15      Blog:        http://www.javydekoning.com
 16
 17      .EXAMPLE
 18      Get-DaDatabaseSize
 19
 20      Description:
 21      Will get filesize for the DA DB files.
 22  #>
 23  [cmdletbinding(SupportsShouldProcess=$true)]
 24  param()
 25
 26  process {
 27      $ConnectionString = 'Server=np:\\.\pipe\MICROSOFT##WID\tsql\query;Integrated Security=True;Initial Catalog=RaAcctDb;'
 28      Write-Verbose "Connecting using: '$ConnectionString'"
 29
 30      if ($PSCmdlet.ShouldProcess('.','Creating index')) {
 31        try {
 32          #Setup Connection to WID
 33          $Connection       = New-Object System.Data.SqlClient.SqlConnection
 34          $Connection.ConnectionString = $ConnectionString
 35          $Connection.Open()
 36
 37          #Prep Query
 38          $Query             = $Connection.CreateCommand()
 39          $Query.CommandText = "SELECT name, physical_name AS current_file_location FROM sys.master_files`r`n"
 40          $SQLOutput         = $Query.ExecuteReader()
 41          
 42          $Table = New-Object -TypeName 'System.Data.DataTable'
 43          $Table.Load($SQLOutput)
 44          
 45          #Get FileSize
 46          $Files = $Table | Where-Object {$_.name -match 'RaAcctDb|RaAcctDb_log'}
 47          $Size  = $Files | ForEach-Object {Get-Item $_.current_file_location} | Select-Object Name,Length
 48
 49          #Close connection and return object
 50          $Connection.Close()
 51          Return $Size
 52        } catch {
 53          throw $_
 54        }
 55    }
 56  }
 57}
 58
 59function Shrink-DaDatabase {
 60  <#
 61      .Synopsis
 62      Shirnks the Direct Access Database
 63
 64      .DESCRIPTION
 65      This function connects to the Windows Internal Database (WID) in order to Shrink the DirectAccess DB files.
 66
 67      .NOTES
 68      Name:        Shrink-DaDatabase
 69      Author:      Javy de Koning
 70      Version:     1.0.0
 71      DateCreated: 2016-10-04
 72      DateUpdated: 2016-10-04
 73      Blog:        http://www.javydekoning.com
 74
 75      .EXAMPLE
 76      Shrink-DaDatabase
 77
 78      Description:
 79      Will shrink the DA DB files (Default C:\Windows\DirectAccess\db).
 80  #>
 81  [cmdletbinding(SupportsShouldProcess=$true)]
 82  param()
 83
 84  process {
 85      $ConnectionString = 'Server=np:\\.\pipe\MICROSOFT##WID\tsql\query;Integrated Security=True;Initial Catalog=RaAcctDb;MultipleActiveResultSets=True'
 86      Write-Verbose "Connecting using: '$ConnectionString'"
 87
 88      if ($PSCmdlet.ShouldProcess('.','Creating index')) {
 89        try {
 90          #Setup Connection to WID
 91          $Connection                  = New-Object System.Data.SqlClient.SqlConnection
 92          $Connection.ConnectionString = $ConnectionString
 93          $Connection.Open()
 94
 95          #ShrinkDB_Log
 96          $Query                = $Connection.CreateCommand()
 97          $query.CommandTimeout = 3600
 98          $Query.CommandText    = "DBCC SHRINKFILE ('RaAcctDb_log')`r`n"
 99          $Null = $Query.ExecuteReader()
100
101          #ShrinkDB_Log
102          $Query                = $Connection.CreateCommand()
103          $query.CommandTimeout = 3600
104          $Query.CommandText    = "DBCC SHRINKFILE ('RaAcctDb')`r`n"
105          $Null = $Query.ExecuteReader()
106
107          #Close connection and return object
108          $Connection.Close()
109
110        } catch {
111          throw $_
112        }
113    }
114  }
115}

Javy de Koning
WRITTEN BY
Javy de Koning
Geek 🤓, Love sports 🏃‍♂️🏋️‍♂️, Food 🍛, Tech 💻, @Amsterdam ❌❌❌.