Michael Petri FlogDonkey
FlogDonkey
/ Script Linked Servers.sql
Last active
July 26, 2023 16:04
Script All Linked Servers. Run on source server, the copy and paste message window into connection for target server. Passwords are hashed.
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
| SET NOCOUNT ON; | |
| /* Local Vars */ | |
| DECLARE @RowID_Server INT | |
| ,@RowID_Login INT | |
| ,@RowID_Option INT | |
| ,@NBSP CHAR(1) = CHAR(10); | |
| /* Results for linked servers and currently configured options */ | |
| DECLARE @LinkedServers TABLE |
FlogDonkey
/ Move Database Files.sql
Last active
April 9, 2024 12:21
For moving Database Files to new location. Does a copy, so some cleanup is necessary. Accepts a single Database, or runs for *all* databases. In testing, it took about one minute for each 10GB of DB size, so plan accordingly accordingly. There is a list of excluded databases, allowing you to precisely target databases.
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
| DECLARE @WorkingSQL VARCHAR(8000) | |
| ,@NewPath VARCHAR(8000) = 'G:\SQL Data\' /* Root location to move files */ | |
| ,@TargetDatabase sysname = '%'; /* Specify a singular database, or % for All Databases */ | |
| SET NOCOUNT ON; | |
| /* Enable xp_cmdshell */ | |
| EXEC sys.sp_configure 'Show Advanced Options', 1; | |
| RECONFIGURE; |
FlogDonkey
/ EnableQueryStore.sql
Last active
October 27, 2022 16:32
Enables query store for all user databases on a supported edition of SQL Server (2016 or higher). Much faster than enabling via the GUI one DB at a time.
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
| BEGIN | |
| DECLARE @RowID INT; | |
| DECLARE @Table TABLE | |
| ( | |
| RowNum INT IDENTITY(1, 1) | |
| ,DBName VARCHAR(100) | |
| ,Command1 NVARCHAR(MAX) | |
| ,Command2 NVARCHAR(MAX) | |
| ); |
FlogDonkey
/ SetFillFactor100.sql
Created
June 4, 2019 22:57
This snip generates and executes the T-SQL to set Fill Factor to 100 for any indexes; both CLUSTERED and NON-CLUSTERED.
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
| DECLARE @RowID INT; | |
| DECLARE @t TABLE | |
| ( | |
| RowID INT IDENTITY(1, 1) | |
| ,command VARCHAR(MAX) | |
| ); | |
| INSERT INTO @t | |
| ( |
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) |