Skip to content

Instantly share code, notes, and snippets.

Nate Johnson NJohnson9402

View GitHub Profile
NJohnson9402 / FetchAndReadSqlErrorLogs.sql
Last active Jul 20, 2018
Fetch and read SQL Error Logs on AWS RDS (or any SQL Server, but it's meant for RDS where permissions are limited!). See .
View FetchAndReadSqlErrorLogs.sql
USE master;
DECLARE @CONST_P2 int = 1; --Error log, not Agent log.
--^ (there's no way to get it programatically outside of registry-reads/other-xp's, none of which is doable in RDS, AFAIK.
--Example: show events from 7/19/2018 between 2am and 2:30am PDT
DECLARE @StartDate datetime = '2018-07-19T02:00:00'
, @EndDate datetime = '2018-07-19T02:30:00';
CREATE TABLE #ErrorLogs ([LogDate] datetime, [ProcessInfo] nvarchar(50), [Text] nvarchar(max));
NJohnson9402 / GetSsrsSubscriptionInfoFromReportServer.sql
Created Jul 11, 2018
SQL Server Reporting Services (SSRS)- find Report Subscriptions by Email or Comments or Name/Description, and view last Status (i.e. was it sent?). See . Subject to updates and open to feedback!
View GetSsrsSubscriptionInfoFromReportServer.sql
USE ReportServer;
--Fetch the subscription settings XML
WITH subscriptionXmL AS (
SELECT SubscriptionID, OwnerID, Report_OID, Locale, InactiveFlags
, ExtensionSettings, ExtensionSettingsXML = CONVERT(xml, ExtensionSettings)
, ModifiedByID, ModifiedDate, [Description], LastStatus
, EventType, MatchData, LastRunTime, [Parameters]
, DeliveryExtension, [Version]
FROM ReportServer.dbo.Subscriptions
NJohnson9402 / GetSqlReplicationArticles.sql
Created Jul 11, 2018
SQL Server- get replication articles from a given published database. See Replace [dbName] at top with your published database name. Subject to update and open to feedback!
View GetSqlReplicationArticles.sql
USE [dbName];
SELECT pdb.publisher_db [DB], objs.[type] AS [ObjType], art.destination_object AS [Name], pub.publication [Pub], objs.[PubType]
FROM [distribution].dbo.MSpublisher_databases pdb with (nolock)
JOIN [distribution].dbo.MSarticles art with (nolock)
ON art.[publisher_db] = pdb.[publisher_db]
JOIN [distribution].dbo.MSpublications pub with (nolock)
on pub.[publication_id] = art.[publication_id]
and pub.[publisher_db] = pdb.[publisher_db]
SELECT 'table' AS [type], name
NJohnson9402 / GetDatabaseFilesAndSpaceUsed.sql
Created Jul 11, 2018
SQL Server- get all database files and space used on an instance. See . Subject to updates and open to feedback!
View GetDatabaseFilesAndSpaceUsed.sql
DECLARE @SummaryView BIT = 0
DECLARE @GenFileRenames BIT = 0
DECLARE @IgnoreSpecials BIT = 0
--Only w/ @SummaryView = 0 && @GenFileRenames = 0
DECLARE @LimitBySize bit = 0
DECLARE @MinSizeGB decimal(10,2) = 5.0
DECLARE @MinPctFree decimal(10,2) = 10.0
--Customized view using GB answer w/ SO temp-table
View Trigger_AfterDelete_LogEvidence.sql
ON dbo.Victims
INSERT INTO aud.Evidence (SchemaName, TableName, ActionType, RecordID
, DatabaseUser, ServerLogin, ActionTime)
SELECT SchemName = 'dbo', TableName = 'Victims', ActionType = 'delete', RecordID = Deleted.Id
, DbUser = USER_NAME(), SrvLogin = SUSER_SNAME(), ActionTime = SYSDATETIME()
FROM Deleted;
View OrphanedUsersFixByName.sql
/* NJohnson9402 /
An example of how to check for orphaned database users (users that do not map to a server login).
Basically the idea here is to take the output of `sys.sp_helpuser` and utilize it to find & fix.
There are much cooler ways out there, such as, but
I have occasionally seen these not work in odd situations. Plus, if your login names and user names
don't exactly match, this method could be extended to enter custom mappings-- you'd simply add a new
column to #HelpUser, say [NewLoginName], and use that in the 'fix-it' statement-generator query.
USE MyDatabase;
NJohnson9402 / RenameDatabaseByBackupDetachAttach.sql
Created Dec 2, 2017
See corresponding blog post at http:/ .
View RenameDatabaseByBackupDetachAttach.sql
/* NJohnson9402 /
An example of how to rename a database by work-around involving backup, restore, detach, attach.
Basically the idea here is that 'OriginalDB' is in heavy use, and can't tolerate much "downtime",
but "readonly time" is OK for a bit.
Drive letters for clarification in this case: 'D' for slow data, 'L' for slow tlog,
'E' for fast data, 'M' for fast tlog. The 'fast' storage system is our destination.
USE master;
EXEC OriginalDB.sys.sp_helpfile;
View FindTriggerDependencies_AuditTrailExample.sql
/* NJohnson9402 /
Uses system catalog views and DMVs to find trigger table and column dependencies;
tailored as "audit trail" example, but applicable to most trigger situations.
Triggers always depend on & are a child object of the table on which they exist.
In this example, the predicate "OBJECTPROPERTY(trig.object_id, 'ExecIsUpdateTrigger') = 1"
restricts the view only to "ON/AFTER UPDATE" triggers; but you could easily extrapolate
to include the other types (INSERT, DELETE) as well, using the same model;
just replace 'ExecIsUpdateTrigger' with 'ExecIsInsertTrigger' etc.
SELECT [Trigger] =
NJohnson9402 / BitsTransferLoopSample.ps1
Last active Sep 13, 2017
BITS Transfer loop-over-files example
View BitsTransferLoopSample.ps1
# AUTHOR: Nate Johnson, @njohnson9402/@natethedba,
# TYPE: PowerShell script
# DESCRIPTION/USAGE: example of 2 file copying loops using BITS Transfer cmdlets, targeted at SQL backups
# See corresponding blog post at
# ASSUMPTION: your SQL backup files are named with a date component in the form "yyyyMMdd", and use extension ".bak"
$source = "\\BigSQL\Backup\BigSQLInstance\" #network share or local directory
$destiny = "X:\Restore\BigSQLInstance\" #LOCAL drive destination
$testonly = 1 #set to 0 to actually do the copying!
View AsciiBunker.txt
*\ |* ___________
\| ___/ I___I___I_\___
/___I___I___I___I___\ Incoming!!
/__I___I___I___I___I__\ /
___ /_I___I___I___I___I___I_\ ___
/ \ __ /____I___/ \___I____\ __ / \
/ \ / \ _ /___I____/ O \____I___\ _ / \ / \
/ \_/ \_/ \__/__I___I__|____^____|__I___I__\__/ \_/ \_/ \
You can’t perform that action at this time.