Skip to content

Instantly share code, notes, and snippets.

Avatar
❤️

Brent Ozar BrentOzar

❤️
View GitHub Profile
@BrentOzar
BrentOzar / CreateDatabases
Created Jun 25, 2016
Create a bunch of databases in a loop
View CreateDatabases
DECLARE @StringTemplate VARCHAR(1000);
DECLARE @StringToExecute VARCHAR(1000);
DECLARE @Counter TINYINT = 1;
SET @StringTemplate = 'CREATE DATABASE [ConsumeMassQuantities##REPLACE##]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N''ConsumeMassQuantities'', FILENAME = N''D:\MSSQL\Data\ConsumeMassQuantities##REPLACE##.mdf'' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N''ConsumeMassQuantities_log'', FILENAME = N''D:\MSSQL\Data\ConsumeMassQuantities##REPLACE##_log.ldf'' , SIZE = 8192KB , FILEGROWTH = 65536KB )'
View gist:66f52a7b59a3dabf772a376cbf039850
CREATE DATABASE [Collation_100_CI_AS_KS_WS_SC]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Collation_100_CI_AS_KS_WS_SC', FILENAME = N'M:\MSSQL\Data\Collation_100_CI_AS_KS_WS_SC.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'Collation_100_CI_AS_KS_WS_SC_log', FILENAME = N'M:\MSSQL\Data\Collation_100_CI_AS_KS_WS_SC_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
COLLATE Latin1_General_100_CS_AS_KS_WS_SC
GO
sp_BlitzIndex @DatabaseName = 'Collation_100_CI_AS_KS_WS_SC';
@BrentOzar
BrentOzar / WaitCategories.sql
Created Sep 20, 2017
Create ##WaitCategories table to categorize SQL Server wait stats
View WaitCategories.sql
/*
Wait stat categories from: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql
Updated on: 2017/09/20
*/
IF OBJECT_ID('tempdb..##WaitCategories') IS NULL
BEGIN
/* We reuse this one by default rather than recreate it every time. */
CREATE TABLE ##WaitCategories
(
@BrentOzar
BrentOzar / StackOverflow-Indexes.sql
Created Sep 3, 2017
Creating basic indexes on the public Stack Overflow database export
View StackOverflow-Indexes.sql
CREATE INDEX IX_UserId ON dbo.Badges(UserId);
GO
CREATE INDEX IX_PostId ON dbo.Comments(PostId);
GO
CREATE INDEX IX_UserId ON dbo.Comments(UserId);
GO
CREATE INDEX IX_PostId ON dbo.PostLinks(PostId);
GO
CREATE INDEX IX_RelatedPostId ON dbo.PostLinks(RelatedPostId);
GO
View Testing Snapshot Materialized Views.sql
/*
This message gives me a clue about the database requirements:
10650 – Refresh of snapshot view(s) requires snapshot isolation to be enabled on the database.
So let's set that up:
*/
CREATE VIEW dbo.Ocean WITH SNAPSHOT
AS
@BrentOzar
BrentOzar / Finding undocumented trace flags.sql
Last active Jan 11, 2018
Stored procedure that continuously restarts SQL Server looking for undocumented trace flags
View Finding undocumented trace flags.sql
/*
THIS IS A SPECTACULARLY BAD IDEA.
Never, ever, ever run this in production.
Don't even run it on a VM that you care about.
(I specifically build a throwaway VM just to run this.)
Get the latest version:
https://gist.github.com/BrentOzar/5d8e4e8cbc1d89d1021b6f889762d835
*/
View Azure Managed Instance Errorlog 1
This file has been truncated, but you can view the full file.
Date,Source,Severity,Message
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] EndGetServiceDescription for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 80071bcd
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] WaitForCallbackInvocation for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] BeginGetServiceDescription for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/15/2018 21:01:51,spid120s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] GetFabricServiceNameInt for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
View Azure Managed Instance Errorlog 2
This file has been truncated, but you can view the full file.
Date,Source,Severity,Message
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] EndGetServiceDescription for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 80071bcd
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] WaitForCallbackInvocation for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] BeginGetServiceDescription for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
03/16/2018 00:37:03,spid142s,Unknown,[CFabricReplicaManager::GetServiceDescriptionFromFabric] GetFabricServiceNameInt for fabric service uri: fabric:/Worker.CL/ce39943cee8e/SQL.LogicalServer/ce39943cee8e completed with result: 0
View fn_hadr_fabric_get_node_health_states.xml
<HealthEvent>
<HealthInformation>
<SourceId>System.FM</SourceId>
<Property>State</Property>
<TimeToLiveSeconds>0</TimeToLiveSeconds>
<State>OK</State>
<Description>Fabric node is up.</Description>
<SequenceNumber>14</SequenceNumber>
<RemoveWhenExpired>false</RemoveWhenExpired>
</HealthInformation>
@BrentOzar
BrentOzar / Posts_MultiTenant.sql
Created Oct 24, 2018
Creating a multi-tenant version of StackOverflow.dbo.Posts
View Posts_MultiTenant.sql
CREATE TABLE [dbo].[Posts_MultiTenant](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CompanyCode] VARCHAR(10) NOT NULL,
[AcceptedAnswerId] [int] NULL,
[AnswerCount] [int] NULL,
[Body] [nvarchar](max) NOT NULL,
[ClosedDate] [datetime] NULL,
[CommentCount] [int] NULL,
[CommunityOwnedDate] [datetime] NULL,
[CreationDate] [datetime] NOT NULL,