Skip to content

Instantly share code, notes, and snippets.

Avatar
🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / Find SHRINK sessions with lock info.sql
Last active Jun 22, 2020
SQL queries to troubleshoot a long-running DBCC SHRINK operation
View Find SHRINK sessions with lock info.sql
/*
Author: Eitan Blumin | https://www.eitanblumin.com
Create Date: 2020-03-18
Last Update: 2020-06-22
Description:
This script will detect currently running sessions in your database which are running DBCC SHRINK commands.
It will also output the name of any tables and indexes the session is currently locking.
Use this query to find out what causes a SHRINK to run for too long.
You may need to run it multiple times to "catch" the relevant info.
@EitanBlumin
EitanBlumin / CaptureTSQLEvents_XE_Buffer.sql
Last active Jul 15, 2020
Collect T-SQL Events using an Extended Events Buffer
View CaptureTSQLEvents_XE_Buffer.sql
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Date: 2020-02-26
-- Last Update: 2020-04-07
-- Description: Collect T-SQL Events using an Extended Events Buffer
SET NOCOUNT ON;
DECLARE
@SourceLinkedServer SYSNAME
, @MinimumDurationMilliSeconds BIGINT
@EitanBlumin
EitanBlumin / generate_rename_system_named_default_constraints.sql
Created Feb 26, 2020
Generate rename commands for all system-named default constraints
View generate_rename_system_named_default_constraints.sql
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- Date: 2020-02-26
-- Description: This is a query to generate rename commands for all system-named default constraints within the current database.
-- The constraints are renamed based on convention of "DF_{TableName}_{ColumnName}"
-- Simply run this query and then copy & paste the entire remediationCommand column to get the script(s).
SELECT
schemaName = sch.[name],
tableName = tab.[name],
columnName = col.[name],
@EitanBlumin
EitanBlumin / SQL_Server_Error_Log_Based_on_Severity_with_Full_Message.sql
Last active May 31, 2020
T-SQL script to output a single row per each high severity error from the SQL Server Error Log
View SQL_Server_Error_Log_Based_on_Severity_with_Full_Message.sql
-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Description: Get a single row per each high severity error from the SQL Server Error Log
DECLARE
@SampleTime DATETIME = DATEADD(MINUTE,-30,SYSDATETIME())
, @MinimumSeverity INT = 17
, @MaximumSeverity INT = 25;
IF OBJECT_ID(N'tempdb..#errors') IS NOT NULL
DROP TABLE #errors;
View extended_events_track_failed_logins.sql
CREATE EVENT SESSION [TrackFailedLogins] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.database_id,sqlserver.session_id)
WHERE (([severity]=(20) OR [severity]=(14) OR [severity]=(16))
AND ([error_number]=(18056)
OR [error_number]=(17892)
OR [error_number]=(18061)
OR [error_number]=(18452)
OR [error_number]=(11248)
OR [error_number]=(17806)
@EitanBlumin
EitanBlumin / Basic SQL Server table migration using Powershell.ps1
Last active Mar 24, 2020
Basic SQL Server table migration using Powershell
View Basic SQL Server table migration using Powershell.ps1
# Author: Nathan Lifshes
# Date: 2020-01-13
Param (
[string] $SrcServer ,
[string] $SrcDatabase ,
[string] $DestServer ,
[string] $DestDatabase ,
[string] $SrcUsername = "" ,
[string] $SrcPassword = "" ,
[string] $DestUsername = "" ,
@EitanBlumin
EitanBlumin / Create_Named_SQL_Server_Linked_Server.sql
Last active May 31, 2020
Create a Linked Server to a remote SQL Server, but give it a different name than its actual address
View Create_Named_SQL_Server_Linked_Server.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Description: Create a Linked Server to a remote SQL Server, but give it a different name than its actual address
*/
DECLARE
@ServerAddress [nvarchar](255) = 'MyRemoteServerAddress\SomeNamedInstanceIfYouWant,1433',
@NewServerName [nvarchar](255) = 'MyRemoteServerName',
@RemoteUser [nvarchar](128) = 'remote_user',
@RemotePassword [nvarchar](128) = 'remote_user_password',
@MapLocalLogin [nvarchar](255) = NULL -- name a local login to map to the remote login. If NULL, will map current login
@EitanBlumin
EitanBlumin / Deploy SSRS Reports.ps1
Created Feb 9, 2020
Powershell script to deploy SSRS reports from a folder (all rds and rdl files)
View Deploy SSRS Reports.ps1
param (
[string] $SourceFolder = "SSRS",
[string] $TargetReportServerUri = "http://localhost/ReportServer",
[string] $TargetFolder = "MyReports"
)
$ErrorActionPreference = "Stop"
if ($SourceFolder -eq "") {
$SourceFolder = $(Get-Location).Path + "\"
@EitanBlumin
EitanBlumin / Shrink_Database_File_in_Specified_Increments.sql
Last active Jul 28, 2020
Shrink a database file in specified increments down to a specific size or percentage of used space
View Shrink_Database_File_in_Specified_Increments.sql
/*
----------------------------------------------------------------------------
Shrink a Database File in Specified Increments
----------------------------------------------------------------------------
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Creation Date: 2020-01-05
Last Update: 2020-07-28
----------------------------------------------------------------------------
Description:
This script uses small intervals to shrink a file (in the current database)
@EitanBlumin
EitanBlumin / Standardize_Database_File_Sizes_and_AutoGrowth.sql
Last active May 31, 2020
Minimal standardization of all Database Files in a SQL Server instance
View Standardize_Database_File_Sizes_and_AutoGrowth.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: 2020-01-28
Description:
This script generates commands to implement a minimal standardization of all database files in the instance.
List of implemented standards:
1. Files must not be allowed to have percentage growth (defaults to 1GB growth instead as a replacement)
2. Files must all have UNLIMITED max size
3. Log files must be at least 64MB in size
4. Log file auto-growth must be in power multiples of 2 between 64MB and 2048MB (i.e. 64,128,256,512,1024,2048) (defaults to 1GB growth instead as a replacement)
You can’t perform that action at this time.