I spent some time grieving, and re-evaluating what I really want from life. I traveled and found love in an unexpected place and I got married!

I met Peter while performing Mystery Theater Shows at Castle of Chaos in 2015. I was attending a show as a guest and asked questions about how to have a guest character for the show, to have more fun interacting with the cast, and the director misunderstood my question and thought I wanted to be in the show and said I should try out for the next show. Ryan and I joined the cast beginning our connection with Castle of Chaos. The cast of Mystery Theatre welcomed us and we all became friends. There were many jokes about the “No Relation” when announcing the cast with our last names at the end of shows because Peter, Ryan and I all had the last name Allred.

Peter and I performed as Drake and Mallory in “A Wedding to Die For” where we would get married and then Peter would die during the first dance. The audience would then spend the next hour uncovering the murderer. We calculated that we have already been theatrically married about 13 times.
After the heartbreaking loss of Ryan, some members of the cast group invited me to join a Disneyland trip. While on that trip, Peter and I started talking and never stopped.

We spent the next 7 months asking each other questions to get to know one another better. We met each others families and learned we have a lot in common; not the least of which our love of Disney, our passion for live theater, and many more. We spend a lot of time filled with joy in each others company.
In May, we went to Disneyland again. We had just finished riding “The Pirates of the Caribbean” as our last ride of the night. It was after midnight and the park was starting to clear out. Peter suggested we go back and look at the castle. As we looked at the castle, he directed us over to the Fantasy Faire area. We were looking at the statue of Rapunzel in her tower and her hair lit up. We loved how magical it felt and noticed an instrumental version of “See the Light” from Rapunzel had begun playing over the Disney speakers. We started dancing and Peter asked, “Would you do me the honor of being my princess?”.
In my excitement, I missed that he was holding the ring and just started saying “Yes” over and over. Peter told me I needed to put on the ring first and I was so excited and bouncing that it took me a minute to get it on and say “Yes” again.

We got married October 1st in an absolutely Royal Wedding. and went on the most incredible honeymoon. It has been humbling to see so many people show up to celebrate and love us.

This has been a big shift in life for me and I am loving it. I know there are more shifts on the horizon and am really excited for what the future holds.

Thanks for reading and have a magical day!

The song for this post is Stargazing by Myles Smith.

select 
    object_schema_name(i.object_id) as [schema],
    t.name as [table_name],
    i.name as [index_name],
    s.name as [partition_scheme]
from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id
    join sys.tables t on i.object_id = t.object_id  
WHERE i.type = 1

This query makes an assumption that the partitions are on the Clustered Index. In it, I am showing the schema, the table name, the name of the clustered index, and the partition scheme.

The song for this post is Boxes by the Goo Goo Dolls.

I spun up my new instance and restored SSISDB, which was wrong. So I deleted it. I found that I had missed the SSIS install piece during the install process, so I ran that again and installed SSIS.

Next I configured CLR on the new server:

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO

Since I didn’t have the password from the old server, I ran this on the old server:

Backup master key to file = 'D:\Backup\SSISDB_key' --Replace with the location you want to save it.
Encryption by password = 'StrongPassword' --Replace with your password

Then I created a new backup of the SSISDB on the old server.

Because I had restored my SSISDB when I shouldn’t have, I ran this powershell code on the new server to adjust all the little things I was going to miss:

# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”)

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”

Write-Host “Connecting to server …”

# Create a connection to the server
$sqlConnectionString = “Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;”
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace”.IntegrationServices” $sqlConnection

# Provision a new SSIS Catalog
$catalog = New-Object $ISNamespace”.Catalog” ($integrationServices, “SSISDB”, “StrongPassword”)  #REPLACE THE PASSWORD
$catalog.Create()

I restored the SSISDB to to the new server from the backup I took above. Then I restored the SSISDB_key that I created:

RESTORE MASTER KEY FROM FILE = 'D:\Install\SSISDB_key' --Change to the Location of your MasterKey Backup
DECRYPTION BY PASSWORD = 'StrongPassword' --Password used to encrypt. 
ENCRYPTION BY PASSWORD = 'StrongPassword' --New Password
FORCE

Then I opened and altered the master key:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword'--–‘Password used when creating SSISDB in Powershell Script’
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Next, I verified my keys were working:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';

Finally, I synchronized logins and made sure my new version was compatible by watching the compatibility status (0 means all is good)

EXEC catalog.check_schema_version 0 --0 for 64, 1 for 32

It was a lot, but I was so thrilled when it all started working.

The song for this post is Dancing with My Eyes Closed – Performed by Wind Walkers

Here I am using a small and unique section from the query I want to find in the WHERE clause between two percent signs to work with my LIKE:

    SELECT plan_handle, text
    FROM sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    WHERE text LIKE N'%SELECT S.SuperHeroName, S.SuperHeroDescription,%'
      AND text NOT LIKE N'%DBCC%'

I use the text that was returned to verify that I have the right query that I want to find in QueryStore. Then I take that plan_handle and put it in this query:

	SELECT qsp.query_id, qsp.plan_id
	FROM sys.dm_exec_query_stats eqs INNER JOIN sys.query_store_plan qsp
	ON eqs.query_plan_hash = qsp.query_plan_hash
	WHERE eqs.plan_handle = 0x05000900EBC0B12E005DDEC92402000001000000000000000000000000000000000000000000000000000000 --plan handle here

Finally, I can take that query id and plug it into the Tracked Queries report in QueryStore:

Then I can see my query and even force the plan that I want to use moving forward (as you can see I have done above).

The song for this post is Everything Turns Around by Dogstar.

I wanted to take a moment to thank all the people that have reached out with love, kindness and support in this difficult time. Your thoughtfulness has made all the difference and I can’t thank you all enough. Thank you to the people that helped me with arrangements, contributed to the gofundme, sent gifts, sent notes and just overall sent love my direction. I am so thankful for the SQLFamily and the support I have received. Even my family was blown away by all of the love.

I had set up auto-posting to my blog so it would keep going even when I couldn’t and have now run out of posts. I will get them going again as soon as I am able. I have been trying to write this post for weeks and am hoping that this time I can finish it.

If I am not around for a little while, it is because my heart is trying to remember how to beat with what is now the saddest part of me.

Thank you again for all the love.

The song for this post is Loneliest by Maneskin.

I started to build my index and had set RESUMABLE = ON.

CREATE INDEX [IX_SuperHero_BirthDate_PowerKey] ON [dbo].[SuperHero]
([BirthDate], [PowerKey])
INCLUDE ([FirstSeenKey]) WITH (ONLINE = ON, RESUMABLE = ON)

It had been building for about 10 minutes, when I realized I had the fields in the wrong order. No big deal, I just canceled it, corrected it and tried to start it again. “Tried” was the keyword here. I got an error because I had set RESUMABLE = ON but hadn’t properly cleared it out of the system. I started to furiously dig through articles about how to clear out an index that was resumable but had been canceled.

Thankfully I found this fantastic article from Brent Ozar which saved me. It told me that I had to “Abort” my index before I could start over.

ALTER INDEX [IX_SuperHero_BirthDate_PowerKey] ON [dbo].[SuperHero] ABORT

It ended my process and I was able to restart the create on my index. It took a long time but successfully created and took a 25 minute query down to a few seconds.

The song for this post is Unconditional by Matt Maeson.

We decided that for the Analysis table, we would only collect processes that had been running for longer than 5 minutes. How would we be able to tell that they had been running for 5 minutes or longer? We looked at the SQLText and count the number of times for that session_id and start_time that text appeared.

We started by creating the WhoIsActiveAnalysis table and the WhoIsActiveArchive Table again in my dba schema:

CREATE TABLE [dba].[whoisactiveAnalysis](
	[ProcessTime] [varchar](20) NULL,
	[session_id] [smallint] NOT NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[SQLText] [nvarchar](max) NULL,
	[SQLCommand] [nvarchar](max) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[collection_time] [datetime] NOT NULL,
	[EstimatedMinutes] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dba].[whoisactiveArchive](
	[dd hh:mm:ss.mss] [varchar](20) NULL,
	[percent_complete] [varchar](30) NULL,
	[host_name] [nvarchar](128) NULL,
	[session_id] [smallint] NOT NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[status] [varchar](30) NOT NULL,
	[sql_text] [xml] NULL,
	[wait_info] [nvarchar](4000) NULL,
	[blocking_session_id] [smallint] NULL,
	[blocked_session_count] [varchar](30) NULL,
	[reads] [varchar](30) NULL,
	[physical_reads] [varchar](30) NULL,
	[writes] [varchar](30) NULL,
	[tempdb_allocations] [varchar](30) NULL,
	[tempdb_current] [varchar](30) NULL,
	[CPU] [varchar](30) NULL,
	[used_memory] [varchar](30) NULL,
	[open_tran_count] [varchar](30) NULL,
	[sql_command] [xml] NULL,
	[database_name] [nvarchar](128) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[login_time] [datetime] NULL,
	[request_id] [int] NULL,
	[collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Next I created my stored procedure to load the Analysis table, load the Archive table, remove anything older than 70 days from the Archive table (we don’t want to keep too much) and empty the Log table so it can start fresh in the morning.

/*Get only what has been running longer than 5 minutes 
and only bring in one record for what has been running longer than 5 minutes.
There may be multiple records for the same process if there are multiple steps taking longer
than 5 minutes.*/

CREATE PROC [dba].[LoadWhoIsActiveAnalysis]

AS

INSERT INTO [dba].[whoisactiveAnalysis]
           ([ProcessTime]
           ,[session_id]
           ,[login_name]
           ,[SQLText]
           ,[SQLCommand]
           ,[program_name]
           ,[start_time]
           ,[collection_time]
           ,[EstimatedMinutes])
 SELECT AL.[dd hh:mm:ss.mss] AS ProcessTime  
	  ,AL.[session_id]
      ,AL.[login_name]     
	  ,CAST(AL.[sql_text] AS nvarchar(MAX)) AS SQLText    
	  ,CAST(AL.[sql_command] AS nvarchar(MAX)) AS SQLCommand 
	  ,AL.[program_name]
      ,AL.[start_time]    
	  ,AL.[collection_time]
	  ,AG.StepCount AS EstimatedMinutes
 FROM [dba].[whoisactiveLog] AL
 INNER JOIN (SELECT MAX([dd hh:mm:ss.mss]) AS ProcessTime
        , COUNT([dd hh:mm:ss.mss]) AS StepCount
        , CAST(sql_text AS nvarchar(MAX)) AS SQLText
        , session_id
        , start_time
  FROM [dba].[whoisactiveLog] 
  GROUP BY CAST(sql_text AS nvarchar(MAX)) 
		, start_time
        , session_id
 HAVING COUNT([dd hh:mm:ss.mss]) > 5) AG ON AL.session_id = AG.session_id AND AL.[dd hh:mm:ss.mss] = AG.ProcessTime


/*Insert everything from the Log into the Archive*/
INSERT INTO [dba].[whoisactiveArchive]
           ([dd hh:mm:ss.mss]
           ,[percent_complete]
           ,[host_name]
           ,[session_id]
           ,[login_name]
           ,[status]
           ,[sql_text]
           ,[wait_info]
           ,[blocking_session_id]
           ,[blocked_session_count]
           ,[reads]
           ,[physical_reads]
           ,[writes]
           ,[tempdb_allocations]
           ,[tempdb_current]
           ,[CPU]
           ,[used_memory]
           ,[open_tran_count]
           ,[sql_command]
           ,[database_name]
           ,[program_name]
           ,[start_time]
           ,[login_time]
           ,[request_id]
           ,[collection_time])

SELECT [dd hh:mm:ss.mss]
      ,[percent_complete]
      ,[host_name]
      ,[session_id]
      ,[login_name]
      ,[status]
      ,[sql_text]
      ,[wait_info]
      ,[blocking_session_id]
      ,[blocked_session_count]
      ,[reads]
      ,[physical_reads]
      ,[writes]
      ,[tempdb_allocations]
      ,[tempdb_current]
      ,[CPU]
      ,[used_memory]
      ,[open_tran_count]
      ,[sql_command]
      ,[database_name]
      ,[program_name]
      ,[start_time]
      ,[login_time]
      ,[request_id]
      ,[collection_time]
  FROM [dba].[whoisactiveLog]





/*Rolling Delete to remove anything older than 70 days from the Archive*/
DELETE 
FROM  [dba].[whoisactiveArchive]
WHERE collection_time < GETDATE()-70

/*Turncate after the archive has been completed to prepare for tomorrow with empty table*/
TRUNCATE TABLE [dba].[whoisactiveLog]

After creating this stored procedure I was ready to create a pipeline that executes once a day to do my Analysis and clean my tables. So far it has been great.

The song for this post is Follow Your Arrow by Kacey Musgraves.

First, I created my table in my special dba schema because this is for me:

CREATE TABLE [dba].[whoisactiveLog](
	[dd hh:mm:ss.mss] [varchar](20) NULL,
	[percent_complete] [varchar](30) NULL,
	[host_name] [nvarchar](128) NULL,
	[session_id] [smallint] NOT NULL,
	[login_name] [nvarchar](128) NOT NULL,
	[status] [varchar](30) NOT NULL,
	[sql_text] [xml] NULL,
	[wait_info] [nvarchar](4000) NULL,
	[blocking_session_id] [smallint] NULL,
	[blocked_session_count] [varchar](30) NULL,
	[reads] [varchar](30) NULL,
	[physical_reads] [varchar](30) NULL,
	[writes] [varchar](30) NULL,
	[tempdb_allocations] [varchar](30) NULL,
	[tempdb_current] [varchar](30) NULL,
	[CPU] [varchar](30) NULL,
	[used_memory] [varchar](30) NULL,
	[open_tran_count] [varchar](30) NULL,
	[sql_command] [xml] NULL,
	[database_name] [nvarchar](128) NULL,
	[program_name] [nvarchar](128) NULL,
	[start_time] [datetime] NOT NULL,
	[login_time] [datetime] NULL,
	[request_id] [int] NULL,
	[collection_time] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Then I built my procedure to collect the value of the hour and set up a while loop for as long as the hour matches this collected value, I want it to load my results from sp_whoisactive into my table. Then I have it wait for one minute and do it again.

CREATE PROCEDURE [dba].[LoadWhoIsActiveLog]

AS

DECLARE @Hour INT 
SET @Hour= (SELECT DATEPART(HOUR,GETDATE()))
WHILE ( @Hour = (SELECT DATEPART(HOUR,GETDATE())))
BEGIN
    
EXEC sp_whoisactive 
	 @get_outer_command = 1
	, @find_block_leaders = 1
	, @output_column_list = '[dd hh:mm:ss.mss][percent_complete][host_name][session_id][login_name][status][sql_text][wait_info][blocking_session_id][blocked_session_count][%]'
	, @sort_order = '[blocked_session_count] DESC, [start_time] ASC'
	, @destination_table = 'dba.whoisactiveLog' 

	BEGIN
	WAITFOR DELAY '0:01'
	END
END;

It runs exactly as I wanted. I put it in an Azure Pipeline and have it fire at the top of the hour during our busy hours so we can collect usage.

The song for this post is Call the Moondust by Glen Phillips.

First, I log into the Azure Portal (portal.azure.com)

I select “SQL servers”:

I pick the right server that my managed database is on (it has the type of “SQL server”) and I click on the name of the server (it is a link to where I need to go)

In the next screen under Settings, I select SQL databases:

I click on the name of the database that I need to restore. It will take me to another screen.

It gives me this list of options at the top (this is also where I would go to create a copy of a database for another environment like dev).

I select “Restore” and it takes me to another screen where it has most of the restore ready to go. Here is where I can pick a point in time recovery or the most recent possible. Since I knew about the time the delete had happened, I am going to restore to just a few minutes before the delete. (It will be in UTC time) Most recent would have not have what I need because it took the user a little bit of time to let me know what had happened.

I can name what the database is going to be, I can add a date or time to this name so I know when it was created.

I set my storage for the cheapest options because this is just a restore that I am going to pull data from back into my current database. I won’t be keeping it long.

I select review + create at the bottom and it will start restoring my database. Depending on the size, it might take time, but it was pretty quick for me.

I pull the data back into the table that I need and then go back to the SQL Databases screen and find my newly created database name. I click on the newly created database name and this time, instead of “Restore”, I select “Delete” and follow the prompts to remove it.

This was the easiest point in time restore I have EVER done. It was fast and I didn’t have to keep adding each log to roll forward.

The song for this post is Lucky by Elle King.

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate BETWEEN '1/1/1995' AND '12/31/1999'

OR

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate >= '1/1/1995' AND Birthdate <= '12/31/1999'

Imagine my surprise when one of the students turned in this:

SELECT FirstName, LastName, Birthdate
FROM Heroes
WHERE Birthdate BETWEEN '1995' AND '1999'

When I first saw the query I thought, “There is no way they ran that and it worked.” So I wrote it up and ran it on my data. Guess what? IT RUNS AND RETURNS DATA! I was shocked.

I started looking at the plan and what it did to the data and found that it had done an implicit conversion on the dates and assumed 1/1/1995 to 1/1/1999 based on the year. So we were missing data from the results, but I was still in shock that it had run in the first place and shared this information with my co-worker who reminded me that dates get crazy and if I only put in ’12/31/1999′ and there is a time in the field, it will cut off most of the times within that day because it will assume I want ’12/31/1999 00:00:00′. If I want the full day, I need to get in the habit of specifying ’12/31/1999 23:59:59′ or ‘1/1/2000 00:00:00’ and then test my results to make sure I am getting what I truly want back from the database.

The song for this post is BANNERS – Perfectly Broken.