Shrink Direct Access Database using PowerShell.

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.

Get-DaDatabaseSize | ft -auto

Name                  Length
----                  ------
RaAcctDb.mdf     14996799488
RaAcctDb_log.ldf 12727346176

The Second cmdlet shrinks the DB

Shrink-DaDatabase
Get-DaDatabaseSize | ft -auto

Name                  Length
----                  ------
RaAcctDb.mdf     14988476416
RaAcctDb_log.ldf     1048576

(12727346176-1048576)/1GB
11.8522882461548

11.8GB recovered!

Shrink Direct Access Database – PowerShell module

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

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

Full code:

function Get-DaDatabaseSize {
  <#
      .Synopsis
      Checks if the Direct Access Database size

      .DESCRIPTION
      This function connects to the Windows Internal Database (WID) in order to check the DirectAccess DB file sizes.

      .NOTES   
      Name:        Get-DaDatabaseSize
      Author:      Javy de Koning
      Version:     1.0.0
      DateCreated: 2016-10-04
      DateUpdated: 2016-10-04
      Blog:        http://www.javydekoning.com

      .EXAMPLE
      Get-DaDatabaseSize

      Description:
      Will get filesize for the DA DB files.
  #>
  [cmdletbinding(SupportsShouldProcess=$true)]
  param()

  process {
      $ConnectionString = 'Server=np:\\.\pipe\MICROSOFT##WID\tsql\query;Integrated Security=True;Initial Catalog=RaAcctDb;'
      Write-Verbose "Connecting using: '$ConnectionString'"

      if ($PSCmdlet.ShouldProcess('.','Creating index')) {
        try {
          #Setup Connection to WID
          $Connection       = New-Object System.Data.SqlClient.SqlConnection
          $Connection.ConnectionString = $ConnectionString
          $Connection.Open()

          #Prep Query
          $Query             = $Connection.CreateCommand()
          $Query.CommandText = "SELECT name, physical_name AS current_file_location FROM sys.master_files`r`n"
          $SQLOutput         = $Query.ExecuteReader()
          
          $Table = New-Object -TypeName 'System.Data.DataTable'
          $Table.Load($SQLOutput)
          
          #Get FileSize
          $Files = $Table | Where-Object {$_.name -match 'RaAcctDb|RaAcctDb_log'}
          $Size  = $Files | ForEach-Object {Get-Item $_.current_file_location} | Select-Object Name,Length

          #Close connection and return object
          $Connection.Close()
          Return $Size
        } catch {
          throw $_
        }
    }
  }
}

function Shrink-DaDatabase {
  <#
      .Synopsis
      Shirnks the Direct Access Database

      .DESCRIPTION
      This function connects to the Windows Internal Database (WID) in order to Shrink the DirectAccess DB files.

      .NOTES
      Name:        Shrink-DaDatabase
      Author:      Javy de Koning
      Version:     1.0.0
      DateCreated: 2016-10-04
      DateUpdated: 2016-10-04
      Blog:        http://www.javydekoning.com

      .EXAMPLE
      Shrink-DaDatabase

      Description:
      Will shrink the DA DB files (Default C:\Windows\DirectAccess\db).
  #>
  [cmdletbinding(SupportsShouldProcess=$true)]
  param()

  process {
      $ConnectionString = 'Server=np:\\.\pipe\MICROSOFT##WID\tsql\query;Integrated Security=True;Initial Catalog=RaAcctDb;MultipleActiveResultSets=True'
      Write-Verbose "Connecting using: '$ConnectionString'"

      if ($PSCmdlet.ShouldProcess('.','Creating index')) {
        try {
          #Setup Connection to WID
          $Connection                  = New-Object System.Data.SqlClient.SqlConnection
          $Connection.ConnectionString = $ConnectionString
          $Connection.Open()

          #ShrinkDB_Log
          $Query                = $Connection.CreateCommand()
          $query.CommandTimeout = 3600
          $Query.CommandText    = "DBCC SHRINKFILE ('RaAcctDb_log')`r`n"
          $Null = $Query.ExecuteReader()

          #ShrinkDB_Log
          $Query                = $Connection.CreateCommand()
          $query.CommandTimeout = 3600
          $Query.CommandText    = "DBCC SHRINKFILE ('RaAcctDb')`r`n"
          $Null = $Query.ExecuteReader()

          #Close connection and return object
          $Connection.Close()

        } catch {
          throw $_
        }
    }
  }
}

Shrink Direct Access Database using PowerShell.

Shrink Direct Access Database – PowerShell module

See Also