Skip to content

Instantly share code, notes, and snippets.

View ghotz's full-sized avatar

Gianluca Hotz ghotz

30 followers · 41 following
View GitHub Profile
@ghotz
ghotz / en_profile_logins.sql
Created September 22, 2014 23:03
Profile connections using Event Notification and AUDIT_LOGIN events
--
-- Profile connections using Event Notification and AUDIT_LOGIN events
-- to create a summary of number of logins by database, application, login and hostname
-- additionally tracking domain name and user
--
USE DBPerfmon;
GO
-- Prerequisites
ALTER DATABASE DBPerfmon SET ENABLE_BROKER;
@ghotz
ghotz / group-ola-indexoptimize.sql
Last active January 30, 2024 16:10
Group index optimize total size from logged data by OLA Maintenance Procedures https://ola.hallengren.com
--
-- Group index optimize total size from logged data by https://ola.hallengren.com
--
WITH cte AS
(
SELECT
DatabaseName
, StartTime
, ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'bigint') AS pages
FROM CommandLog
@ghotz
ghotz / xe_audit_getdate_usage.sql
Created October 9, 2014 15:24
Audit GETDATE() usage in SQL Batches with Extended Events (useful to check when migrating to Amazon RDS)
CREATE EVENT SESSION [audit_getdate_usage] ON SERVER
ADD EVENT sqlserver.sql_batch_starting(SET collect_batch_text=(1)
ACTION(sqlserver.database_name)
WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%getdate%')))
ADD TARGET package0.event_file(SET filename=N'c:\temp\audit_getdate_usage')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
@ghotz
ghotz / replace-files.ps1
Created October 10, 2014 17:47
Replace all files with a given name in subdirectories with another file
$ReplacementFile = "C:\Temp\newfile.txt"
$SearchDir = "c:\Temp\root"
dir $SearchDir -File (Split-Path $ReplacementFile -Leaf) -Recurse |
% { Copy-Item $ReplacementFile $_.FullName -Force}
@ghotz
ghotz / netstat-wait-for-port.ps1
Created October 25, 2014 12:29
Waits for a port to be opened using netstat
$PortToFind = "6012";
$SleepIntervalMs = 200;
while ($true)
{
$nstat = (netstat -nab);
if ($nstat -like "*$PortToFind*")
{
$nstat -like "*$PortToFind*";
break;
@ghotz
ghotz / trace-date-function-usage-fro-amazon-rds-compliancy.sql
Created November 2, 2014 16:31
--
-- Trace date function usage (batch completed) for Amazon RDS compliancy
--
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
@ghotz
ghotz / get-errorlog-17053.ps1
Created November 21, 2014 07:33
SQL Server error logs offline parsing
# Operating system error %.*ls on file "%.*ls" during %ls.
cls;
$DirPattern = "D:\SQLServer\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG*";
$OutputDir = "D:\Temp";
$CSVDelimiter = ";";
$SearchLogFiles = [regex] "(?<Date>\d{4}-\d{2}-\d{2}) (?<Time>\d{2}:\d{2}:\d{2}\.\d{2}) (?<Source>.*?)\s{4}(?<FullFileName>.*?): Operating system error (?<OSErrorNumber>\d*) \((?<OSErrorText>.*?)\) encountered.";
@ghotz
ghotz / query-perfmon-relog-data.sql
Created December 16, 2014 17:29
Query data gathered with Performance Monitor and loaded with Relog to a SQL Database
WITH cte AS
(
SELECT
YEAR(CONVERT(DATETIME, CAST(C1.CounterDateTime AS VARCHAR(19))) ) AS sample_year
, MONTH(CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19)))) AS sample_month
, DAY(CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19)))) AS sample_day
, DATEPART(HH,CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19)))) AS sample_hour
, DATEPART(mi,CONVERT(DATETIME, CAST(CounterDateTime AS VARCHAR(19)))) AS sample_minute
, REPLACE(C2.MachineName, '\\', '') AS machine_name
, CASE
@ghotz
ghotz / generate-mirroring-backup-ops.sql
Last active August 29, 2015 14:13
Generate SQL Server Mirroring quick hacks
--
-- generates T-SQL commands for full and log backups
--
DECLARE @BackupPath sysname = N'\\192.168.1.2\SyncMirror\';
WITH cte AS
(
-- use databases already configured for mirroring
SELECT DB_NAME(database_id) AS database_name
FROM sys.database_mirroring
@ghotz
ghotz / perf-db-cpu-workload.sql
Created March 4, 2015 12:48
Get a rough idea of the workload distribution per database
WITH cte AS
(
SELECT DB_NAME(t.[dbid]) AS [Database Name],
qs.total_worker_time AS [Total Worker Time],
qs.min_worker_time AS [Min Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.execution_count AS [Execution Count]--,
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
)