search

dataplat / dbatools

🚀 SQL Server automation and instance migrations have never been safer, faster or freer
https://dbatools.io
MIT License
2.48k stars 805 forks source link

[Bug] Set-DbaTempDbConfig resizing occurs before change of path resulting in filled disk #7325

Closed SQLCanuck closed 3 years ago

SQLCanuck commented 3 years ago

Steps to Reproduce

$hashTableArguments = @{ SqlInstance = "localhost" DataFileSize = 81920 LogFileSize = 20480 DataFileGrowth = 512 LogFileGrowth = 512 DataPath = "D:\MSSQLSERVER\TempDbData" LogPath = "D:\MSSQLSERVER\TempDbLog" }

Set-DbaTempdbConfig @hashTableArguments

Expected Behavior

Expected TempDB file locations to be moved to the specified path and then the files to be expanded to the desired size.

Actual Behavior

TempDB files were expanded on the C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA default location filling the disk and then moved to the new path. The files on the C:\ default location were also not cleaned up after the move.

Environmental information

PowerShell Version : 5.1.17763.1852 dbatools latest installed : 1.0.148 Culture of OS : en-US

SQL Server:

Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

us_english

andreasjordan commented 3 years ago

The command only generates and executes a set of "ALTER DATABASE tempdb MODIFY FILE" or "ALTER DATABASE tempdb ADD FILE" commands. Changing the size will be done immediatly (and does not need a restart), but changing the location is only done with a restart of the instance. The next start of the instance will create the files in the new location. As the restart is not part of the command, it is currently not possible to first do the relocation, restart the instance and then do the resize. So your workaround would be to split the task in two. Cleanup of the old files can only be done after the restart, so can not be done by the command.

andreasjordan commented 3 years ago

So I would like to close this as "no bug" / "works as designed". If you would like to have the command handle the restart and the cleanup, you could open a feature request and optionally provide a pull request with the necessary code changes.

SQLCanuck commented 3 years ago

@andreasjordan thank you for the clarification on the behavior. I have confirmed splitting the actions into two separate calls of Set-DbaTempDbConfig with a restart in-between gives the desired effect.

andreasjordan commented 3 years ago

Great. Please come back here for further issues or visit us on slack: https://sqlcommunity.slack.com/ssb/redirect