Skip to content

Instantly share code, notes, and snippets.

View FlogDonkey's full-sized avatar

Michael Petri FlogDonkey

9 followers · 1 following
  • CopperPoint Insurance
View GitHub Profile
@FlogDonkey
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…
/*
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)
@FlogDonkey
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.
DECLARE @RowID INT;
DECLARE @t TABLE
(
RowID INT IDENTITY(1, 1)
,command VARCHAR(MAX)
);
INSERT INTO @t
(
@FlogDonkey
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.
BEGIN
DECLARE @RowID INT;
DECLARE @Table TABLE
(
RowNum INT IDENTITY(1, 1)
,DBName VARCHAR(100)
,Command1 NVARCHAR(MAX)
,Command2 NVARCHAR(MAX)
);
@FlogDonkey
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.
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
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.
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