Fix DirectAccess Windows Internal Database – PowerShell Module

I ran into an odd problem with one of the Windows systems at a customer that was running with a very high CPU load. The system is running Windows Server 2012R2 with the DirectAccess role enabled. Upon further investigation it seemed that the SQL Server process that is running the Windows Internal Database for DirectAccess was to blame here:

DADBBefore

I did some digging around on the web and found this blog post by Richard Hicks that described the exact problem I was experiencing on the DirectAccess servers:
DirectAccess SQL Server High CPU Usage – Richard Hicks/

In his blog post Richard used the GUI to manually create an additional index to reduce the CPU load on the DirectAccess server. Because I was having this issue on multiple servers I decided to create a PowerShell module based on his instructions. I have finalized the PowerShell module and made it publicly available: FixDaDatabase

The module comes with three cmdlets:

  • Get-DaDatabaseIndexStatus
  • Add-DaDatabaseIndex
  • Remove-DaDatabaseIndex

To get started with the module either download the module from GitHub, the TechNet Script Gallery or the PowerShell Gallery. In order to install the module directly from the PowerShell gallery run the following command:

1
Install-Module -Name FixDaDatabase -Verbose

InstallModule

First we run Get-DaDatabaseIndexStatus to establish if an Index is already present on the system:

1
Get-DaDatabaseIndexStatus

Get-DaDatabaseIndexStatus

Since the additional Index is not available the Add-DaDatabaseIndex cmdlet can be used to create the Index:

1
Add-DaDatabaseIndex

Add-DaDatabaseIndex

To verify the results of this cmdlet the Get-DaDatabaseIndexStatus cmdlet can be used:

1
Get-DaDatabaseIndexStatus -Verbose

Get-DaDatabaseIndexStatusVerbose

After creating the additional Index the CPU load of the system dropped down significantly within a minute:

DADBAfter

The module is available at the following locations:

If you have any questions or suggestions for this module feel free to leave a comment below or submit a pull request on GitHub.

All the links mentioned in this article are available below:

Links in this Article
PowerShell Gallery – FixDaDatabase
TechNet Script Gallery – FixDaDatabase
GitHub – JaapBrasser – FixDaDatabase
DirectAccess SQL Server High CPU Usage – Richard Hicks
Direct Access: Windows Internal Database (SQL) High CPU Usage – Thomas Vuylsteke


Share

2 thoughts on “Fix DirectAccess Windows Internal Database – PowerShell Module

  1. Chris

    Hi I tried both running the manual commands and this script but in both cases the scripts time out with this error
    ===========================

    PS C:\Support> add-dadatabaseindex
    Exception calling “ExecuteReader” with “0” argument(s): “Timeout expired. The timeout period elapsed prior to
    completion of the operation or the server is not responding.”
    At C:\support\fixdadatabase.psm1:106 char:17
    + $Query.ExecuteReader()
    + ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    PS C:\Support>

    =========================================================

    Is there any way to increase the timeout so that ythese scripts do not timeout, or alternatively clear down some fo the Inbox accounting records to reduce the size of the database.

    Reply
    1. Jaap Brasser Post author

      Hello Chris,

      I have taken a look at it and implemented a ConnectionTimeOut parameter for all functions in this module, I have uploaded 1.1.0.0 to the PowerShell Gallery so you can give it a try. Let me know if it works for you now.

      Regards,

      Jaap Brasser

      Reply

Leave a Reply

Your email address will not be published.