Andy CW AndyDBA
FlogDonkey
/ Move TempDB.sql
Last active
April 9, 2024 12:20
New and improved Move TempDB script. Captures xp_cmdshell value before execution and resets it post-execution. Allows for dynamic drive allocation in case of company policy of leaving some set percent of drive available for future growth. Also includes improved error handling and validation, as well as a @debug mode, allowing us to run the scrip…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| Snippet is nuts and bolts for creating/moving to an isolated tempdb drive. | |
| After you run this, SQL Server must be restarted for it to take effect | |
| */ | |
| DECLARE @DriveSizeGB INT = 40 | |
| ,@FileCount INT = 9 | |
| ,@InstanceCount TINYINT = 1 | |
| ,@VolumeBuffer DECIMAL(8, 2) = .8 /* Set to amount of volume TempDB can fill. */ | |
| ,@RowID INT | |
| ,@FileSize VARCHAR(10) |