When Your Options Are Limited

In my role I am still on-call several times each month, and many of the escalations that come are simply things that newer DBA's and Help Desk staff have yet to run into - things that many employees at their level *can* handle, once they have experienced it.

This is one of those stories.

https://memegenerator.net/img/instances/57630989/with-a-one-way-ticket-to-the-twilight-zone.jpg

Our monitoring alerted the Help Desk because a client SQL Server was down (in this case, SQL services were stopped but Windows was not down).  In most cases this is handled between our Help Desk and if needed, the client, but in this case it was paged out to me with this note from the help desk technician:

"Upon checking on server Generic SQL Server, SQL Server Agent and SQL Server are failed - these services have automatic startup type but I can't start the services because the start option is disabled  "

And they added a screen shot:

There are multiple reasons why you can't start a service, most of them around permissions (role memberships, explicit permissions, etc.)  With what we do as a managed services provider (MSP) we usually have extensive administrative privileges on the servers on which we work, so true permissions aren't usually a problem.

But UAC is.

https://pics.ballmemes.com/curses-foiled-again-6891606.png

User Account Control (UAC) was introduced in Windows Vista and has since been expanded across multiple Windows desktop and server platforms.  At a basic level, it is an extra layer of security to make sure that changes to the operating system and it's components are only performed by administrators, and only when actually intended - think of it as an "Are You Sure You Want To Do That?"

https://memegenerator.net/img/instances/55831706/is-that-your-final-answer.jpg

UAC is an important security feature but can also be pretty annoying when you perform administrative tasks all of the time.  When I was looking for a good descriptive link to reference in the paragraph above, five of the top ten items were some variant of "here's an easy way to turn off UAC" (and three of the other five were "why you should never turn off UAC!")

The functional gotcha is the screen shown above.  The technician had not run the services console "as administrator" and as such did not have the token authority to start/stop Windows services.

This is an easy fix, however...instead of double-clicking on the services icon, simply right-click and select "Run As Administrator"

If your login is a Windows administrator. this then runs the services console with the administrator token, and you can then start/stop/manage services (as well as break/delete/befoul services - be careful!) to your heart's content.

If your login is *not* a Windows Admin, you will receive a prompt which will allow you to enter administrative credentials which will be used solely to run the application in question - it isn't a full context switch to that login for your whole session.

Note - you will often also run into this on some systems with SQL Server Management Studio and other SQL Server tools if you are running them locally on a server - if you run the tool and cannot connect to your local SQL Server instance, it may be that you haven't run the tool "as administrator."  Simply right-click the icon for the tool and, as above, select "Run as Administrator."

--

Hope this helps!

Why I Believe In In-Person Training

I was recently asked a question from a manager regarding someone on my team when I requested to send them to a training experience: 

Are there CBT based alternatives that may be more affordable that [the team member] can take in his down time?

This question is definitely a tricky one.  The question is not really “is there an alternative?” – yes, in the current age there is always an online alternative.

Are they comparable alternatives?  No.

I have done many hours of online webinars, Pluralsight courses, and remote learning in my almost twenty years as a DBA.  I would never say online learning can’t work for a specific purpose when properly applied.  (Of course we often don’t properly apply it because we try to horn it in between all of the other distractions of work and life; this is one of the benefits to going away to training, even though travel costs money – it removes the learner from the distractions of not only work but also the rest of their lives when they don’t have to go home at night.)

https://i0.wp.com/yourdogsfriend.org/wp-content/uploads/2013/11/squirrel.jpg

I still think we need to pursue online courses, such as Pluralsight subscriptions.  I have written many times on this blog about Pluralsight, PASS Virtual Chapters, and other online experiences.

Having said that, the *most useful* training experiences I have ever had come from being present in-person at an event (a conference or a class), and interacting with the instructors *and attendees* at the event, both during the event time and before and after.

I cannot list the number of times that I have gained extra knowledge or solved real-world problems by interacting with classmates during downtime between lectures or exercises or at breakfast/lunch/dinner around the event.  I had an experience at my last employer before Ntirety (actually at a SQLskills class) where something went wrong at a client while we were at class, and the class including the instructors worked with me to brainstorm and ultimately resolve this issue.

https://pics.me.me/death-brainstorm-ing-some-of-the-best-ideas-were-found-16511986.png

Standing in a classroom or conference center with other professionals gives opportunities for interactive learning that just don’t – can’t – exist with a CBT.  The CBT can spit knowledge at you, but it can’t answer your responsive questions, and it definitely can’t take meaningful detours into related topics.

…and that’s what real high-end training and learning is all about, Charlie Brown.

https://www.faithstrongtoday.com/wp-content/uploads/2016/12/Thats-What-Christmas-Is-All-About-1600x600.jpg

On the subject of cost, there are less expensive in-person Microsoft Official Curriculum (MOC) classes about very broad topics offered by instructors at dedicated training facilities (such as New Horizons) who are usually certified *instructors* (material deliverers) but not certified *database professionals* and this greatly limits the value of the interaction as well as the quality of the material itself.  Training at higher-end classes and conferences are created and delivered by persons who work with the product daily as part of their lives, not people who just talk about the product M-F 8-5.

As always, this is my $.02.

Hope this helps!

Toolbox - Exporting SSMS Results to Excel

I was working on another post when I found myself needing to dump out query results to a grid format to include in the post.  This is a very normal situation - even in my day-to-day job I send emails with grids of results (we all do right?)

The easiest thing to do is to copy-paste from the SSMS grid results, and this is what I do 90%+ of the time just as I'm sure most of you do:

Execute with Results to Grid (usually the default), and then right-click in the upper-left of the the resultset and click "Copy With Headers" and then paste the data into Excel:

Easy right?

--

The gotcha is when you are returning something more interesting, something with punctuation like a query.  When you copy-paste that your Excel turns out like this:

https://imagessure.com/thumbs/jziYuTJbBMxlZLZRfveokk5JIwpvVzi7NMi2yjIJGioHL02jChLnoqGXf-dlGY9gDVCQVMvX-DPD-BV9R4Mnjg.jpg

The ugliness is usually caused by carriage return/line feed (CR/LF) in your query - we all like nice pretty TSQL, so we use lots of newlines along the way.  For a normally delimited resultset, this results in lots of new rows as seen above.

The easiest way to deal with this is using the REPLACE function to replace the offending characters.  Carriage Return and Line Feed are two different ASCII characters, CHAR(13) and CHAR(10) respectively.

To remove them with REPLACE, your code turns out like this:

--

SELECT REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ')  /* Change LF to Space */
as QueryText
FROM <whatever DMV>

--

The code first replaces any carriage returns with a space, and then uses replace a second time to replace any line feeds with a space.  

Using the REPLACEs without any other changes results into an Excel sheet that look like this:

Note the extra columns. When copy-pasting into Excel the presence of tabs causes the column breaks seen here.

Luckily, tabs are also replaceable as ASCII characters - CHAR(9):

--

SELECT REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
as QueryText
FROM <whatever DMV>

Which now looks like this:

SELECT @DownloadCount=COUNT(*)                   FROM (                   SELECT DISTINCT ma.Material_Attachment_ID                   FROM Topic_Main tm WITH (NOLOCK)                  INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID                   INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id                   WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que                   INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID    WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())                              -->Query to calculate Experts based on Topic ID (PA)

Getting closer.  Note that for some code, this may be the extent of what you need - you may not have all of that ugly whitespace.

To fix this last piece, let's try one more REPLACE to remove double spaces for single spaces:

--

SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '') /* Change TAB to Space */
, '  ', ' ')  /* Change Two Spaces to Space */
as QueryText
FROM <whatever DMV>

--

Which now looks like this:

SELECT @DownloadCount=COUNT(*)          FROM (          SELECT DISTINCT ma.Material_Attachment_ID          FROM Topic_Main tm WITH (NOLOCK)         INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID          INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id          WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que          INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID  WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())               -->Query to calculate Experts based on Topic ID (PA)

--

http://www.quickmeme.com/img/98/98dd84943a5bcb086e5ec689072c0e6caa04bcc9314a37ae721268b5b798d533.jpg

Better, but why didn't it solve our problem?

Replacing two spaces with one space does *not* replace three spaces with one space, or four spaces with one space, etc.  Using this REPLACE simply turns every two spaces into one (or four spaces into two, or six spaces into three) - it *doesn't* clean up all the white space.

There are two ways to handle this - the first is a brute force method of using lots of REPLACE statements to repetitively replace two spaces with one as many times as you think is important:

--

SELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(<MyStringWithLotsOfSpaces> , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') , ' ', ' ') as MyStringWithHopefullyNoMoreSpaces

--

In most cases with enough REPLACE statements this will work, but it is ugly.

The second, and more elegant, method is described by Jeff Moden (blog) in an article on SQLServerCentral and leverages the ability to REPLACE multiple characters at once.

For this method, instead of replacing a double space with a single space, we will replace it with what Jeff calls an "unlikely character" such as the Backspace (ASCII CHAR(8)):

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
as MyStringWithLotsOfSpacesAndNowBackSpaces

--

This makes a string that had multiple spaces now have a CHAR(8) as every other character.

Next, now that your former rows of strings ooooooooo instead looks like oxoxoxoxo, we will replace the flipped pattern, CHAR(8)+' '  with just an empty string - this removes all of the intermediate patterns:

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
as MyStringWithAtMoseOneSpaceAndAtMostOneBackSpace

--

This removes all of the "xo" pairs so now the oxoxoxoxo becomes just o (a space).   If there were originally an even number of spaces you would have had oxoxoxox and after the REPLACE you would end up with just ox (space+CHAR(8)) - this means you need one more REPLACE to strip off any remaining CHAR(8):

--

SELECT REPLACE(
REPLACE(<MyStringWithLotsOfSpaces>
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
, CHAR(8), '') /* Change Any Remaining Backspaces to Nothing */
as MyStringWithAtMostOneSpaceBetweenEachWord

--

As Jeff shows in his article, the result play out like this:

Original String (Odd Number)	ooooooooo
Step 1	oxoxoxoxo
Step 2	oxoxoxoxo
Step 3	o
Final	o
Original String (Even Number)	oooooooo
Step 1	oxoxoxox
Step 2	oxoxoxox
Step 3	ox
Final	o

--

Now that we know how to strip out those offending whitespaces, let's go back to our original query:

--

SELECT REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
as QueryText
FROM <whatever DMV>

--

We now need to wrap this in our space-remover REPLACES like this:

--

SELECT REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(QueryText
, CHAR(13), ' ' ) /* Change CR to Space */
, CHAR(10), ' ') /* Change LF to Space */
, CHAR(9), '')  /* Change TAB to Space */
, '  ', ' '+CHAR(8)) /* Change Two Spaces to A Space and a Backspace */
, CHAR(8)+' ','')  /* Change a Backspace and a Space to Nothing */
, CHAR(8), '') /* Change Any Remaining Backspaces to Nothing */
as QueryText
FROM <whatever DMV>

--

Is this ugly?  heck yeah!

Does it work?  HECK YEAH!

--

Original QueryText:

SELECT @DownloadCount=COUNT(*)                     
            FROM (                     
            SELECT DISTINCT ma.Material_Attachment_ID                     
            FROM Topic_Main tm  WITH (NOLOCK)                   
            INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID                     
            INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id                     
            WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que                     
            INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID=dad.Material_Attachment_ID
WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE())                   
                     
           -->Query to calculate Experts based on Topic ID (PA)                        

Repaired QueryText:

SELECT @DownloadCount=COUNT(*) FROM ( SELECT DISTINCT ma.Material_Attachment_ID FROM Topic_Main tm WITH (NOLOCK) INNER JOIN Material_Topic mt WITH (NOLOCK) ON tm.Topic_ID=mt.Topic_ID INNER JOIN Material_Attachment ma WITH (NOLOCK) ON mt.Material_id=ma.Material_id WHERE tm.Invisible_Flag=0 AND ma.Attachment_Doc_Type_ID=@DocID AND tm.Topic_ID IN (SELECT CountReturned FROM #CountResults)) AS que INNER JOIN Download_All_Distinct dad WITH (NOLOCK) ON que.Material_Attachment_ID= dad.Material_Attachment_ID WHERE dad.Download_Date >=DATEADD(year, -1, GETDATE()) -->Query to calculate Experts based on Topic ID (PA) 

--

This "repaired" text is easily paste-able into Excel into a single column, giving us an easily manageable spreadsheet.

...and all it takes is six REPLACEs!

Obviously you can *NOT* simply copy the "repaired" text into a query window and hit execute - if nothing else this method breaks inline comments which makes the code unexecutable.  It is useful now for manual analysis and comparison - for example looking at expensive queries, or pattern matching for certain object names in the code - but not for execution.

--

Do I advise you to wrap all of your text fields in six REPLACES?

https://i.imgflip.com/t362n.jpg

This is definitely an "edge" case - as mentioned above 90%+ of the time you will simply right-click, copy with headers, paste into Excel, and go on your merry way.

...but save this set of REPLACEs into your script repository - because sooner or later...you will need it.

I guarantee it.

https://memegenerator.net/img/instances/75015382/thats-the-fact-jack.jpg

Hope this helps!

T-SQL Tuesday #111 - Why Do You Do What You Do?

This month's T-SQL Tuesday is hosted by Andy Leonard (blog/@AndyLeonard) and his topic was this:

That’s the question this month: Why do you do what you do?

For me this was the easiest T-SQL Tuesday I have ever seen.  Some of you may consider this a cop-out, but here is my answer:

Why Can't I Shrink TempDB?

I know, I know....#OMGShrinkingIsBadDontEverDoThatOrElseEtcEtcEtc

We all know that there are simply times you have to shrink some files.  There are risks - blocking, significant I/O, fragmentation, and more - all of which mean you should not shrink a file willy-nilly without considering the impacts...but sometimes you are having a production issue and don't have any choice.  Similarly, sometimes you are in Dev/Test and it is simply more practical to shrink a file than to add drive space or re-architect the full solution.

In many cases it comes down to that unfortunate reality that there's a way the book/class does it, and another way we have to do it "in the field."

http://twitchlol.com/wp-content/uploads/2013/09/Battle-Plan-for-kids.jpg

Of course you also have to remember Law #9842 of being a DBA - all database systems are Production to someone.  It may be a developer or a QA team rather than an end user, but it is still PROD to them!)

--

In this story, TempDB DATA files were using almost all of the space on the drive, meaning TempLog couldn't grow, but the DATA files were mostly empty by the time it escalated to me.

Even though the files were mostly empty, my attempts to shrink them were throwing an error and the files were not shrinking:

--

Msg 5054, Level 16, State 1, Line 1
Could not cleanup worktable IAM chains to allow shrink or remove file operation.  Please try again when tempdb is idle.

--

www.deviantart.com/ieatatwaffenhouse/art/Skeletor-553757222

Googling the message led me to one of my top five authoritative sources, Paul Randal of SQLskills.com (@PaulRandal/blog).  The relevant blog post is https://www.sqlskills.com/blogs/paul/shrinking-tempdb-longer-prohibited/

While my error message isn't featured in the body of the blog post, it *is* in the final comment and reply in the article.  By that information, it appears that this error reflects that certain system structures are on pages that can't be moved without system restart, which means TempDB can't be manually shrunk on this instance w/o SQL service restart.

(Paul does describe in his article the old-school fear that "shrinking TempDB leads to corruption" and how his extensive experience leads him and Microsoft to now believe that is not true *on modern versions of SQL Server (2005+)*)

--

Hope this helps!