NTFS Compression and SQL Server Do Not Play Well Together

Wanted to be proactive and move a database that was in the default path on C:\ to a secondary drive as it was growing pretty heavily.

What I didn’t realize was the adventure that would ensure.

Lesson 1

Don’t move a SQL Server database to a volume that someone has set NTFS Compression on at the drive level.

Lesson 2

Copy the database next time, instead of moving. Would have eased my anxious dba mind since I didn’t have a backup. before you judge me.. it was a dev oriented enviroment, not production… disclaimer finished

The Nasty Errors and Warnings Ensue

First, you’ll get an error message if you try to mount the database and it has been compressed. Since I’d never done this before I didn’t realize the mess I was getting into. It will tell you that you can’t mount the database without marking as read-only as it’s a compressed file.

Ok… so just go to file explorer > properties > advanced > uncheck compress … right?

Nope…

Changing File Attributes 'E:\DATA\FancyTacos.mdf' The requested operation could not be completed due to a file system limitation`

I found that message about as helpful as the favorite .NET error message object reference not found that is of course so easy to immediately fix.

The Fix

Pull up volume properties. Uncheck compress drive OR
If you really want this compression, then make sure to uncompress the folders containing SQL Server files and apply.

Since I wasn’t able to fix this large of a file by toggling the file (it was 100gb+), I figured to keep it simple and try copying the database back to the original drive, unmark the archive attribute, then copy back to the drive I had removed compression on and see if this worked. While it sounded like a typical “IT Crowd” fix (have you tried turning it on and off again) I figured I’d give it a shot.

… It worked. Amazingly enough it just worked.

Here’s a helpful script to get you on your way in case it takes a while. Use at your own risk, and please… always have backups! #DontBlameMeIfYouDidntBackThingsUp #CowsayChangedMyLife

Move Database To C Drive to eliminate compression then move back folder after disabling compression on the destination folder

GitHub

<#
.Description
Migrate database from drive where it got compressed, back to C, uncompress, then back again to destination.
Not automatic. More for manual execution.

Notice the Robocopy switch being used: /A-:C
This is removing an attribute during copy, in this case the compression attribute. This ensures the newly copied file is uncompressed instead of trying to toggle the compression on the file off after copy which likely would result in the same failures.


.NOTES
Setup your dependencies quickly with this helpful snippet

if (@(Get-module PSDepend -ListAvailable).count -eq 0)
{
    install-module PSDepend -scope currentuser -confirm:$false -force -AllowClobber
}
invoke-psdepend -inputobject @{
    psdepend                       = 'latest'
    PsDependoptions                = @{
        Version = 'Latest'
        Target  = 'currentuser'
    }
    'PSGalleryModule::PSFramework' = 'latest'; # worlds best powershell framework module... logging, config, etc. Use it!
    'PSGalleryModule::dbatools'    = 'latest'; # worlds best module
    'PSGalleryModule::CowsaySharp' = 'latest'; # provies penguin output that is critical to mental health of developer
    'PSGalleryModule::Carbon'      = 'latest'; # has command for Disable-NtfsCompression
} -quiet -confirm:$false

#>
[cmdletbinding()]
param(
[Parameter(mandatory)]$SqlInstance
,[Parameter(mandatory)]$DatabaseName
,[Parameter(mandatory)]$FileToFix
,[parameter()]$TempFolder = 'C:\temp'
)

New-Item -Path $TempFolder -ItemType Directory -force
Disable-NtfsCompression -path $TempFolder # no need to recurse, as copying file after this.
Write-PSFMessage -Level Verbose -Message ( "{0:hh\:mm\:ss\.fff} {1}" -f $StopWatch.Elapsed, "Disabled NTFS compression on $TempFolder")
$FileDirectory = (Get-Item $FileToFix).Directory.FullName
$FileName = (Get-Item $FileToFix).FullName


$StopWatch = [diagnostics.stopwatch]::StartNew()
Write-PSFMessage -Level Verbose -Message ( "{0:hh\:mm\:ss\.fff} {1}" -f $StopWatch.Elapsed, 'Starting Copy')
$CopyJob = start-process robocopy.exe -argumentlist ('"{0}" "{1}" "{2}" /A-:C /ETA' -f $FileDirectory, $TempFolder, $FileName) -Wait -PassThru -NoNewWindow
$CopyJob | Wait-Process
Write-PSFMessage -Level Verbose -Message ( "{0:hh\:mm\:ss\.fff} {1}: finished" -f $StopWatch.Elapsed, 'Copy To C:\temp')
Get-Cowsay -cowfile tux -message "I finished copying $FileName over that file to c temp" -mode tired
[void]$StopWatch.Restart()


$CopyJob = start-process robocopy.exe -argumentlist ('"{0}" "{1}" "{2}" /IS /ETA' -f $TempFolder, $FileDirectory, $FileName) -Wait -PassThru -NoNewWindow
$CopyJob | Wait-Process
Get-Cowsay -cowfile tux -message "I finished copying $FileName from $TempFolder back to $FileDirectory" -mode tired
Get-Cowsay -cowfile tux -message 'Chow'

and finally to remount the database after copying it back to your drive …

Remount the newly fixed database

GitHub


$fileStructure = New-Object System.Collections.Specialized.StringCollection
$fileStructure.Add("E:\DATA\TacosAreTheBest.mdf")
$fileStructure.Add("E:\DATA\SomePreferBurritos.ldf")
Mount-DbaDatabase -sqlinstance $SqlInstance -database $Database -FileStructure $fileStructure

Get-Cowsay -cowfile tux -message 'Hopefully your database is mounted back correctly' -mode paranoid
Write-PSFMessage -Level Verbose -Message ( "{0:hh\:mm\:ss\.fff} {1}" -f $StopWatch.Elapsed, 'Mounting Database Finished...bazinga')