One DBA's Ongoing Search for Clarity in the Middle of Nowhere


Yet Another Andy Writing About SQL Server

Thursday, September 15, 2016

#SQLSpeaking this Fall!

I have some great speaking dates coming up this Fall - here is the list:


First - the big one.  I am honored to be chosen for the first time to speak at the PASS Summit in Seattle.  The only negative is that I am not on until Friday morning, so I will get to spend the whole week sweating it!

I am giving my health check session "Does it Hurt When I Do This? Performing a SQL Server Health Check" -

IMPORTANT - the final price increase for registration is this Monday September 19th - register by midnight Sunday to beat the $200 increase!


Multiple SQL Saturdays on the agenda as well:

09/24 - SQL Saturday #548 Kansas City (#SQLSATKC)

10/01 - SQL Saturday #557 Minnesota (#SQLSATMN)

At both of these SQL Saturdays I will be making a final tune-ups on my health check session - I have given it multiple times in the past but will be walking through the "final" version before Summit.

11/19 0 SQL Saturday #552 Lincoln, NE (#SQLSAT552)

The formal speaker list/schedule hasn't been released yet but I am hopeful to be giving either my XEvents or health check session.


I look forward to seeing you at any or all of these events!

Saturday, July 23, 2016

Thank You SQL Saturday Sioux Falls!

I just finished giving my SQL Saturday presentation on "Getting Started with Extended Events" at SQLSaturday Sioux Falls.  The audience was great with some multiple questions and follow-ups, so it was awesome!

The sessions have been informative and the event team has done a wonderful job with all of the logistics, so the event is awesome - whether you are here now or not you should definitely put it on your calendar for next July!

My Powerpoint slides and demo scripts are now up on the SQLSaturday web page at - thanks!

Monday, June 13, 2016

Finding File Growths with Extended Events

I give a SQLSaturday session called "Introduction to Extended Events" and at a recent presentation I had a pair of attendees ask me afterwards for a more "real world" example.  I had a basic demo in my session showing setting up a session with a predicate filter to pull particular events for a single database, but it didn't really show using XEvents to solve a business problem.

I pondered for a while and decided to try to recreate something I normally do with the default trace.  The obvious example to me was pulling data on file growths.

What?  You didn't know you could pull information about file growths from the default trace?
I discovered this some time ago when I needed to find out why a database was blowing up at 1am without needing to be awake and online at first I was going to set up a manual trace when I found a pair of blog posts that helped me out.

The first was "Did we have recent autogrows?" from Tibor Karazi (blog/@TiborKaraszi) which clued me in to the fact that autogrow information is even in the default trace.  The catch to Tibor's query is that it relied on a specific trc file - the default trace is a rollover structure so at any given time there are usually multiple sequential trc files, and using his query meant I needed to run it, edit the file name, run it again, etc.

The next piece was the Simple Talk article "Collecting the Information in the Default Trace" from Feodor Georgiev (blog/@FeodorGeorgiev) that showed me how to dynamically build the log file path *and* the fact that I could simply reference log,trc (instead of log_123.trc) and the function would concatenate all of the trc files into a single resultset.

I have said it before and I will say it again - when you need help, odds are someone else has already solved the same (or a similar) problem - look around and you will probably find it!  When you save or reference someone else's work, always make sue to give credit (as I have done above) - even if you make modifications to code or ideas, always acknowledge the original material.
I took the two queries I found and combined them into a single modified query that used Tibor's base query to pull autogrowths but Feodor's concept of the custom built log path:
Default Trace AutoGrow Query
Modified from Tibor Karazi
and Feodor Georgiev

DECLARE @df bit
SELECT @df = is_default FROM sys.traces WHERE id = 1
IF @df = 0 OR @df IS NULL
  RAISERROR('No default trace running!', 16, 1)
SELECT as EventName
, t.DatabaseName
, t.FileName
, t.StartTime
, t.ApplicationName
, HostName
, LoginName
, Duration
, TextData
FROM fn_trace_gettable(
FROM    sys.traces
WHERE   is_default = 1
INNER JOIN sys.trace_events AS te
ON t.EventClass = te.trace_event_id
and LIKE '%Auto Grow'
--and DatabaseName='tempdb'
--and StartTime>'05/27/2014'
ORDER BY StartTime
SELECT TOP 1 'Oldest StartTime' as Label, t.StartTime
FROM fn_trace_gettable(
FROM    sys.traces
WHERE   is_default = 1
INNER JOIN sys.trace_events AS te
ON t.EventClass = te.trace_event_id
ORDER BY StartTime   
The top query returns all rows for an event like '%Auto Grow' (to capture DATA and LOG autogrowths) and as you can see you can also filter on a specific database, date range, etc.

The second query simply uses the same base table set to return the datetime of the oldest row in the current default trace.  Since the default trace is a rolling first in first out (FIFO) arrangement, there is no way to definitively say how far back the trace goes other than to actually look in the trace like this.  If you have a lot of trace-relevant events or a lot of service restarts, your default trace may only have a few days (or even hours) of data and not have what you need - for example if you are trying to troubleshoot a problem from last Saturday and by Monday it has already aged out of the trace, then this isn't useful.

The resultset looks like this:

Pretty slick, right?
...but what does this have to do with Extended Events?

As I started out, I was looking for something slick I do with a trace that I could replicate in Extended Events, and this was a great candidate.


The catch as I discovered, is that while file growths are captured in the default trace, they are *not* in the system health session...
This was slightly annoying, but it also gave me an excuse to create a demo for setting up an XEvents session :)


First, the session:
USE [master]
/* Create Extended Events Session */
IF EXISTS (SELECT 1 FROM master.sys.server_event_sessions WHERE name = 'DemoFileSize')
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
/* Note - no predicate/filter - will collect *all* DATA file size changes */
ADD EVENT sqlserver.databases_log_file_size_changed(
/* Note - no predicate/filter - will collect *all* LOG file size changes */
ADD TARGET package0.event_file(SET filename=N'c:\XE\DemoFileSize.xel',

This just creates a basic session, tracking two events, sqlserver.database_file_size_change and sqlserver.log_file_size_change, writing the output to a file target.


Next the database and table:
/* Create DemoGrowth database, dropping any existing DemoGrowth database */
IF EXISTS (SELECT database_id from sys.databases WHERE name = 'DemoGrowth')
( NAME = N'DemoGrowth_Data'
 , FILENAME = 'C:\Temp\DemoGrowth.MDF'
 /* Small increments for maximum autogrowths */
( NAME = N'DemoGrowth_log'
 , FILENAME = 'C:\Temp\DemoGrowth.LDF'


TO DISK = 'C:\TEMP\DemoGrowth.bak'

USE [DemoGrowth]
/* Create DemoGrow table, dropping any existing DemoGrow table */
IF OBJECT_ID('dbo.DemoGrow','U') is not null
DROP TABLE dbo.DemoGrow
/* Purposely large datatypes for maximum needed autogrowths */
Setting the table with an identity and a character column with a default value makes the test inserts straightforward to code.  The database was created small and the table with large datatypes in order to maximize potential auto-growths.


The next step is to Watch Live Data for the session in order to see that autogrowths are happening - note that the sessions as configured is set to write to an event_file target, so Watch Live Data is the easy way to watch events happen, albeit with some minor performance impact.

Next, let's insert some rows and see what happens:
/* Insert 6000 rows and return file sizes before and after inserts */
SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles 
GO 6000 
SELECT count(ID) from dbo.Grow (NOLOCK) 
SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles
/* If after sizes <> before sizes, check Watch Live Data for rows */
The sysfiles query shows whether the data and log files have grown after the inserts - it is possible (although highly unlikely) that the 6000 inserts don't cause either file to grow - if this is the case (or if you just want to witness more traffic) you re-run the code block multiple times.


Next we need to backup the LOG (remember that we are in FULL recovery) and manually shrink the database in order to see that the shrink also causes events:
TO DISK = 'C:\TEMP\DemoGrowth.trn'
/* Shrink files and return sizes before and after */
SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles 
DBCC SHRINKFILE (N'DemoGrowth_Data')
SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles
/* Assuming shrink occurs, check Watch Live Data for rows */
/* If the LOG doesn't show shrunk, then re-run LOG backup and shrink again */
Just as a shrink logs events, so do manual growths:
/* Manually grow DATA file to see effect on session */
SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles
/* Check Current File Size to set an appropriate size in ALTER grow statement */
ALTER DATABASE [DemoGrowth] MODIFY FILE ( NAME = N'DemoGrowth_Data', SIZE = 128MB )
SELECT name, size*8.0/1024.0 as SizeMB from DemoGrowth.sys.sysfiles
/* Check Watch Live Data for rows */

So what is in the event_file (XEL file)?  There is a system function (sys.fn_xe_file_target_read_file) to return that data:
/* So let's look at the XEL file */
SELECT * FROM sys.fn_xe_file_target_read_file('C:\temp\DemoFileSize*.xel',NULL, NULL, NULL);  


As always, someone has already come up with a better solution.  Google brought me to SQL Zealot and his query to shred the XML at  - I modified the query slightly to return the data elements I wanted, including the timestamp:
Case when file_type = 'Data file' Then 'Data File Grow' Else File_Type End AS [Event Name]
, database_name AS DatabaseName
, dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), timestamp1) as LocalTimeStamp
/* added the timestamp and in XE is UTC - this code converts it to local server time zone */
, file_names
, size_change_mb
, duration
, client_app_name AS Client_Application
, client_hostname
, session_id AS SessionID
, Is_Automatic
(n.value ('(data[@name="size_change_kb"]/value)[1]', 'int')/1024.0) AS size_change_mb
, n.value('(@timestamp)[1]', 'datetime2') as timestamp1
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS Client_HostName
, n.value ('(data[@name="file_name"]/value)[1]','nvarchar(50)') AS file_names
, n.value ('(data[@name="is_automatic"]/value)[1]','nvarchar(50)') AS Is_Automatic
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
) AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
ORDER BY timestamp1 desc
/* Much Better! */


So as you can see, a basic XEvents session provides the information comparable to the default trace.

But....XEvents is supposed to be better right?

I neglected to tell you one the query above you can see that there is a data element for sql_text - look what's in it:
Pretty slick, huh?  The element shows you *what query* caused the file size change - something the default trace does not.  If you go back to the default trace, you will see there is a textData field, but that field is NULL for all of the operations in question (it is used for a few other types of events such as DBCC events, but not for the file growth events).

Also you may have noticed that our XEvents are called sqlserver.database_file_size_change and sqlserver.log_file_size_change, and they return data for growths *and* shrinks.  The default trace only returns data on file growths directly (although you could scrape the "DBCC Events" looking for DBCC SHRINKDATABASE and DBCC SHRINKFILE, but there are a lot of other DBCC events to search through.


Extended Events are awesome, and with a little work can give you everything that trace gives you and so much more.

Hope this helps!

Monday, May 23, 2016

How Do I Change My Domain Password on Windows Server 2012?

As a DBA I spend a lot of time in RDP sessions, both to SQL Servers and to "jump"/pass-through servers on client domains.  Most clients (unfortunately not all of them - security is important, people!) have some variant of password expiration in their domains requiring regular password changes every 30/90/180 days.

Prior to Windows Server 2012, this was relatively straightforward - in the Start Menu select "Windows Security" (sometime hidden under Administrative Tools>>Windows Security):

...which then gives you a friendly menu where you can choose to "Change A Password":

Easy right?

** Often unknown tip - from the Change Password Prompt:

You can edit the top line to any account to which you have access - your accounts in other domains (assuming there is access to domain controllers in the other domain) or even other accounts altogether!  Even though I am logged into the above server as DOMAIN\agalbraith, I could modify the line to change the password for SOMEOTHERDOMAIN\agalbraith or DOMAIN\SQLServiceAccount,


The catch to all of this is that in Windows Server 2012, the easy method...went away.  How could they do that???

The Windows Security box is Dead....Long Live the Windows Security box!


Here are three different ways to get to the same screen in Windows Server 2012.  These three methods work in Windows Server 2008 and 2008 R2 as well, so once you get used to one of them you can use it on your old servers as well.
Maybe not *that* old....


The first method is the one I have known for a long time, and is very simple.  Instead of CTRL-ALT-DEL, use CTRL-ALT-END.  Most of the time, this takes you to the same prompt screen as we saw above:


The second method is a little more involved, but useful - and I have been in at least three situations where I *had* to use it - once when there was a keyboard mapping error in the RDP session (something I have only ever experienced once) and twice where I was several layers deep in RDP (RDP to RDP to RDP).  I found this method at

From a command prompt, type osk to bring up the On-Screen Keyboard (something I didn't know even existed at the time):

With the OSK up, press CTRL and ALT on your actual physical keyboard, and then click DEL on the OSK (CTRL-ALT-DEL all on the OSK just functions like a regular CTRL-ALT-DEL):



The third method was recently offered up by a member of the team here at Ntirety, Mike Skaff.  It is one more example of #YouCanDoAnythingWithPowerShell.

I don't know where Mike found it, but I was able to find references in a couple of places, including

From a PowerShell prompt, enter the following:
(New-Object -COM Shell.Application).WindowsSecurity()

Sure enough:

Like the OSK method above, this PowerShell method works from RDP in RDP in RDP as well - and it's PowerShell!


Hope this helps!

Tuesday, May 17, 2016

Catching Up on The #SQLPASS Virtual Chapters

(Didn't realize until I was posting that this was my 100th post...whew!)


A few months ago we purchased a new "smart" Blu-Ray player and one of its intriguing features was its built-in WiFi with pre-installed Internet apps like Amazon Video, YouTube, Vudu, etc. (Previously we had a 6-year-old "dumb" DVD player that was barely smart enough to eject its tray when you pushed the button).
Interestingly, the limitations of these pre-installed apps became one of my frustrations as we started to use the player more and more.  I wanted to be able to view Pluralsight videos and TED Talks, and my wife wanted to watch Craftsy videos, and lacking customized applications we couldn't do so.
After some searching on my laptop I found that TED Talks are available on YouTube!  Some more digging and I found a few PASS videos as well - and more intensive searching found that almost every single PASS Virtual Chapter has its own YouTube channel where they upload recordings of their presentations!

Add caption

Below I have lifted the list of PASS Virtual Chapters (current as of May 2016) and the descriptions provided by PASS.  I have gone through YouTube and found the link for each chapter's YouTube channel and listed them below.

(I apologize to any international readers but I have omitted the non-English speaking chapters as I don't have the language skills to properly search for their content.)

Chapter Name
Short Description
Virtual Chapter URL
YouTube Recordings
Application Development
Training and information for Application Developers
Big Data
Discuss Big Data technologies & Hadoop-based systems
Business Analytics
The PASS Business Analytics Chapter provides virtual train...
Business Intelligence
Connecting BI Professionals globally
Enabling cloud knowledge sharing
Data Architecture
Focusing on your data architecture concerns
Data Science
Advanced Analytics, Machine Learning, Data Mining
Database Administration
Forum for discussion on DBA topics
DBA Fundamentals
Rock solid foundations!
Excel Business Intelligence
Helping users achieve excellence in Excel
Connecting SQL Server Pros in the healthcare industry
High Availability and Disaster Recovery
Reduce the risk and impact of system faults and outages
Cover all technologies and solutions that may be integrate...
In Memory VC
Take advantage of the new In-Memory features
Discuss SQL Server performance-related content
Learn and share best practices around PowerShell
Professional Development
Join the conversation and share resources
Saturday Night SQL
Forum for discussion about BI and Databases
Guidance and education on SQL Server security topics
Improving management of SQL Server in virtual environments
Women in Technology
Forum for discussion of issues pertinent to WIT

What I like to do is to "subscribe" to each of the relevant channels and that makes it easy for me to receive content on our Blu-Ray player (as well as on the YouTube app on my Android).

The Virtual Chapter websites listed above do have archives of the recording of their meetings as well, but they aren't centralized and they are only available from a regular web browser as opposed to a custom app on your TV or phone.

There are also several other useful  YouTube channels I recommend:


Can't beat free info - it's free, and it's info!


Hope this helps!

Thursday, May 12, 2016

Upcoming SQL Saturdays - June 2016

I am speaking at two different SQL Saturdays in June!


First up on June 4th is SQL Saturday #517 Philadelphia (#SQLSAT517) - I am speaking at 2:45pm on "Getting Started with Extended Events."  This is quickly becoming my favorite of the talks that I give because I learn something new every time I present it:
Few subjects in Microsoft SQL Server inspire the same amount of Fear, Uncertainty, and Doubt (FUD) as Extended Events.  Many DBA's continue to use Profiler and SQL Trace even though they have been deprecated for years.  Why is this? 
Extended Events started out in SQL Server 2008 with no user interface and only a few voices in the community documenting the features as they found them.  Since then it has blossomed into a full feature of SQL Server and an amazingly low-impact replacement for Profiler and Trace. 
Come learn how to get started - the basics of sessions, events, actions, targets, packages, and more.  We will look at some base scenarios where Extended Events can be very useful as well as considering a few gotchas along the way.  You may never go back to Profiler again!
Register for SQL Saturday Philadelphia now (before it goes to wait-list!) at!

I am also excited about this event because I will be attending Karen Lopez's (blog/@Datachick) Friday pre-con "Model-Driven Database Development: Physical to Implementation" - Karen is a very engaging and entertaining speaker who really knows her stuff.  There are still tickets available at


The following Saturday June 11th is SQL Saturday #523 - Iowa City (#SQLSatIowa) - I am up at 10am with "Does it Hurt When I Do This? Performing a SQL Server Health Check":
How often do you review your SQL Servers for basic security, maintenance, and performance issues?  Many of the servers I "inherit" as a managed services provider have quite a few gaping holes. It is not unusual to find databases that are never backed up, servers with constant login failures (is it an attack or a bad connection string?), and servers that need more RAM/CPU/etc. (or sometimes that even have too much!)  
Come learn how to use freely available tools from multiple layers of the SQL Server stack to check your servers for basic issues like missing backups and CheckDB as well as for more advanced issues like page life expectancy problems and improper indexing. If you are responsible in any way for a Microsoft SQL Server (DBA, Windows Admin, even a Developer) you will see value in this session!
This talk covers a wide array of tools to check out your servers, including the awesome DMV scripts from Glenn Berry (blog/@GlennAlanBerry) of SQLskills.

Register for SQL Saturday Iowa City now at!

I am not headed to any pre-cons in Iowa City but there is an impressive line-up from many top speakers including my buddy David Klee (blog/@kleegeek) - he is covering "SQL Server Infrastructure Tuning for Availability and Performance" - tickets for his (and the other) pre-cons are available at


I have written multiple times about how amazing SQL Saturdays are as both training and networking opportunities.  If you are in the area either of these weekends come see me and check it out!


Hope this helps!