Single User Mode – Back to Basics

In a recent article, I took you on a trip through how to hack (ethically) a SQL Server to regain sysadmin access. In that article, I made quick mention of restarting SQL Server into single-user mode. It only makes sense to show quickly how to get into single-user mode.

Before getting into that, I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Single-User

So, what exactly is this single-user mode thing? Single-user mode is basically the official back-door into SQL Server for various reasons such as:

  • Somebody deleted all of the logins that were in the sysadmin role.
  • The sa account is disabled or the password has been forgotten.
  • Somebody deleted any Windows groups that were members of the sysadmin role.
  • All members of the sysadmin role are no longer with the company.
  • You need to restore the master database
  • You want to keep SQL Server all to yourself because you are greedy!

These are some pretty solid reasons to need to be able to use the back door. But how exactly do we get to the back door?

Two Paths

As luck would have it, there are two ways to enable single-user mode. You can either get there by making some changes for the SQL Server service in Configuration Manager, or you can utilize a command prompt. I won’t cover the gui path beyond the gentle reminder that you must remember to undo your change when using that method.

My preferred method is through the command line. Using my SQL Server 2017 as the experiment, I would navigate to the Binn directory for that instance. In this case, as shown in the next image.

Before getting too far ahead of myself, I am going to stop my SQL Server.

Notice, I also queried to find all of my services related to SQL before stopping the MSSQLServer service via the net stop mssqlserver command. We will come back to some net start and net stop commands later.

With the service successfully stopped, I can now restart the service in single-user mode.

And then the validation that we are indeed starting in single-user mode…

But wait, did you notice that bit of trickery on the startup command?

This is a pro-tip for when you must use single-user mode. Inevitably, somebody will steal the single-user connection and you will be locked out of the session. By using an app name after the single-user switch, you are telling SQL Server to only accept connections for that specific application. Since most apps will not be using sqlcmd, you will have far less contention to gain that connection and you will be able to complete your task much easier.

You could also pass something like this instead…

In this case, I would be limiting the connections to a query from SSMS (and not object explorer).

Now that I have a single-user connection, I can add a sysadmin or restore the master database or just sit on it and play devious. It all depends on what your objective for the single-user session happens to be.

More Command Line

Remember that reference to the NET commands? Well, it turns out we can also start SQL Server in single-user via net start. Let’s check it out.

The command is pretty simple:

The effect here is the same as navigating to the Binn directory and starting SQL Server with the sqlservr.exe executable. The big difference is considerably less typing and less verbose output of the service startup.

When using the net start method, you do need to know the service name of the SQL Server instance. To get that, I do recommend the following powershell script.

This will produce results similar to the following.

From the results, I can pick the SQL Server service and then pass that to the net start command fairly easily.

The Wrap

Starting SQL Server in single-user mode should be a tool every data professional holds in the bag. This is an essential tool that can be used in multiple scenarios and ensure you are able to fully maintain and control your server. I have shown how to get to single-user mode via two command line methods and mentioned a GUI method. The nice thing about the command line methods is that you don’t have to remember to undo the startup switch like you do with the GUI method.

If you feel the need to read more about single-user mode, here is an article and another on the topic.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Change SQL Server Collation – Back to Basics

One of my most favorite things in the world is the opportunity to deal with extremely varying database and environment requirements. Many vendors and databases seem to have a wide swath of different requirements. Some of the reasons for these requirements are absurd and some are not. That is a discussion for a different day.

When dealing with vendors, sometimes you get good documentation and requirements for the app. If you happen across one of these opportunities, you should consider buying a lottery ticket. Most of the time, the requirements and documentation are poorly assembled and then suffer from linguistic shortcomings.

What do you do when you run into poor documentation from a vendor? The typical answer would be to either call them or make a best guess (even if you call them, you are likely stuck with a best guess anyway). Then what do you do when you find that your best guess was completely wrong? Now it is time to back pedal and fix it, right?

When that mistake involves the server collation setting, the solution is simple – right? All you need to do is uninstall and reinstall SQL Server. That is the common solution and is frankly a horrific waste of time. This article will show some basics around fixing that problem quickly without a full reinstall.

I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Reinstall Prohibited

I am not a huge fan of wasting time doing something, especially if there is a more efficient way of achieving the same end result. I am not talking about cutting corners. If you cut corners, you likely just end up with having more work to do to fix the problems your sloppiness will have caused. That to me is not the same end result.

Having run into a bit of a problem with a vendor recently (with lacking requirements), I found myself with a server that was installed with the wrong collation instead of what the vendor wanted (never-mind they said nothing of it until a month after the server was setup and ready for them to use). The vendor needed the collation fixed immediately (basically it needed to be fixed yesterday). I really did not want to do a reinstall of the server and the sysadmins were just about to click through the uninstall and redo the install.

Oy Vey! Everybody hold up just a second here! First things first – verify with certainty there is good reason to need to change the server collation. It is perfectly legit to give the vendor the third degree here. Make sure they understand why they need the change. If they can answer the questions satisfactorily, then proceed with the change.

Next, just because the vendor says you have to uninstall/reinstall (or reboot) the server to make a certain change, does not mean they know what they are talking about. I have run into too many cases where the vendor thinks you must reboot the server to change the max memory setting in SQL Server (not true for sure).

Sure, common myth would say that you must reinstall SQL Server in order to change the default server collation. That is not entirely accurate. Reinstall is just one option that exists.

In the case of this vendor, they required that the SQL_Latin1_General_CP850_CS_AS collation be used. The server was set for SQL_Latin1_General_CP1_CI_AS. So, let’s see how we can change the collation without a reinstall.

The first thing to do is to confirm the collation we have set.

We can see from these results that indeed the collation is wrong and we need to change it in order to comply with the request from the vendor. Next we will need to stop the SQL Server services.

I think that is pretty clear there what to do. As a reminder, the preferred method to stop and start SQL Server services is via the SQL Server Configuration Manager. We won’t do every start/stop from here for this article for good reason.

Once the services are stopped, then we need to open an administrative command prompt and navigate to the SQL Server binn directory as shown here.

This is for a default instance on SQL Server 2017. If you have a named instance or a different version of SQL Server, you will need to navigate the instance folder structure for your instance.

Next is where the magic happens. We enter a command similar to this:

Here is a quick summary of those flags in this command:

[-m] single user admin mode
[-T] trace flag turned on at startup
[-q] new collation to be applied

There are more such as -s available in books online for your perusal.

If you are curious what is up with those Trace Flags, pretty simple. TF4022 is to bypass startup procs. TF3659 on the other hand is supposed to write errors to the error log (at least in theory).

When the script starts, you will see something like the next two screens:

In the first, you can see that it says it is attempting to change the collation. In the second, just before the completion message, it states that the default collation was successfully changed. Let’s close this command prompt window and then go start SQL Server and validate the change.

And that is a successful change. See how easy that is? This effort takes all of maybe 5 minutes to complete (validation, starting, stopping and so on). I would take this over a reinstall on most days.

Now that we have changed the collation, all I need to do is repeat the process to set the collation back to what it was originally (in my test lab) and make sure to bookmark the process so I can easily look it up the next time.

There is a bit of a caveat to this. On each change of the collation, I ran into permissions issues with my default logging directory (where the sql error logs are written). I just needed to reapply the permissions and it was fine after that (SQL Server would not start). That said, the permissions issue was not seen on the box related to the change for the vendor. So just be mindful of the permissions just in case.

The Wrap

Every now and again we have to deal with a sudden requirements change. When that happens, we sometimes just need to take a step back and evaluate the entire situation to ensure we are proceeding down the right path. It is better to be pensive about the course of action rather than to knee jerk into the course of action. Do you want to spend 5 minutes on the solution or 30-40 minutes doing the same thing? Changing collation can be an easy change, or it can be one met with a bit of pain doing a reinstall (more painful if more user databases are present). Keep that in mind and keep cool when you need to make a sudden change.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

SQL Server Extended Availability Groups

Comments: 1 Comment
Published on: April 1, 2018

It may come as no surprise to many that Microsoft has hastened the SQL Server development cycle. Furthermore, it may be no surprise to many that Microsoft has also hastened the patch cycle for SQL Server.

If you were unaware of this, consider this as your notice that Microsoft has indeed hastened the patch cycle. Not only has the patch cycle become more rapid, the idea of Service Packs is more or less a notion of history at this point. Critical Updates (or CUs) is the new norm. This is a pretty good thing due in large part to the rapid improvements that can be made to the product due to Azure.

With all of this considered now, there is some really awesome news. A hint to this awesome news is in the preceding image and title of this post. In a recent CU for SQL Server 2017, Availability Groups and Extended Events both have seen massive upgrades. The upgrades are so big in fact that it is mind blowing. These upgrades were no small feat by any means and it took some major investment and cooperation from the likes of some well known competitors.

Upgrade the first: Availability Groups have now been extended to be able to include nodes from MySQL, PostGres and MariaDB. Frankly, I don’t understand the MariaDB move there but it’s all good. I am 100% on board with the MySQL addition and may have to work really hard to find a use case to include PostGres.

Imagine the realm of possibility this change brings!! First we got SQL Server on Linux and now we can include a predominantly Linux flavored DBMS in a SQL Server High Availability solution. LAMP engineers have got to be losing their gourds right about now over this. Microsoft is taking away every anti-MS premise that has been used in recent years and turning the world on its ears to become more global and reachable in the architecture and DBMS world.

Upgrade the second: In order to help support and troubleshoot AGs on these other platforms, we need some tools. The tools of choice happen to be in the form of Extended Events. While there is nothing quite yet in place on these other platforms to properly monitor an AG, XE is able to capture some MySQL, PostGres and MariaDB information as transmitted across the wire when these platforms are added to an AG. How COOL is that?

If you are really chomping at the bit, I recommend procuring the latest CU that was recently released. You can find that CU from this Microsoft site here.

The Wrap

I am in full support of this new direction from Microsoft. Partnering with other large platforms to provide a supremely improved overall product is very next level type of stuff and frankly quite unheard of in this ultra competitive world. It is so unheard of in fact that this was a nicely crafted April Fools joke. Happy April Fools Day!

Having mentioned Extended Events, if you are interested, I do recommend a serious read from any number of articles posted in 60 day series.

Extended Events File Initialization Failure

It should come as no surprise that I write a lot of articles about Extended Events (XE). This happens to be another article on Extended Events. Truth be told, this article is hopefully something that is more of an edge case scenario. Well, I sure hope that is the case and that it is not a common problem.

One of the recommended methods to trap payload data in an XE session is via the use of the event_file target. Sending data to a file has numerous benefits such as being able to take the trace and evaluate the trace file from a different machine (locally to that machine).

Every once in a blue moon you just may run into various issues with the event_file such as explained here or here. Though slightly different, the net effect is quite similar and should be treated with roughly the same kind of troubleshooting steps.

Configuration Error

As luck would have it, I ran into one of these rare opportunities to troubleshoot an error occurring on a client server. Truth be told, I was unfamiliar with the actual error at first. Here is that error.

Error: 25602, Severity: 17, State: 22.

The preceding error was scraped out of the SQL Server error log. Obviously a little more detail was needed because this error is far from useful without that detail. Looking a little deeper, I found some errors like this.

Msg 25602, Level 17, State 22, Line 43
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”,
encountered a configuration error during initialization. Object cannot be added to the event session.

Some very good clues are actually contained in that particular message. Some of these clues include the following: a) the term “target”, b) the term “event_file”, and c) the phrase “event session.” Ok, I get it at this point. One of my Extended Event Sessions I had put on the server and used previously was broken. But, since it had been working and I know I had fetched data from it, I found myself puzzled as to why it might be busted.

The next logical thing to do at this point was to test the various sessions that are stopped and try to figure out which one is causing the problem and see if the error is reproduced. Finally upon finding the session that is failing, I ran into the complete message.

Msg 25602, Level 17, State 22, Line 43
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”,
encountered a configuration error during initialization. Object cannot be added to the event session.
The operating system returned error 3: ‘The system cannot find the path specified.
‘ while creating the file ‘C:\Database\XEDROPME\SVRLoginAudit_0_131650006658030000.xel’.

The additional info that I needed is in bold text in the previous text. So, for some reason, there is a problem with the path for the XE trace file output. Going out to the file system to check it out, I found that the client in this case decided to delete the entire folder. How does that happen? Well, it does! When it happens, the XE traces will start to fail and you will no longer capture the intended trace data. Let’s take a look at a simulated reproduction of this issue.

First, I will create a session and then start the session, then validate the session exists and then stop the session.

And here is what I see on my test server when I validate the session exists.

Perfect so far. Now, let’s make that trace output directory disappear. For this demo, you might note that I had created the directory as “C:\Database\XEDROPME”. My intent in the name was obviously to notify the world that the folder was to be dropped.

That statement is easy enough and is performed from my test environment for those getting weary of the use of xp_cmdshell. Now, let’s try to start that session that we knew was previously running.

I re-formatted the output of the error for ease of readability. Otherwise, the output in the preceding image is what will happen when the output directory is dropped. The fix is rather simple at this point – put the folder structure back into place. To read an introductory post about checking if a session exists or not on your server, check this out (a more advanced post is coming soon).

Conclusion

From time to time we will run into various problems supporting Extended Events. This is bound to happen more frequently as we support more varied environments with more hands in the kitchen (so to speak). We need to learn that even small changes can have a ripple effect to other things that may be running on the server. It is worthwhile to perform a little due diligence and clean things up as we make changes – or at minimum to observe the system for a time to ensure no unintended consequences have occurred.

Extended Events is a powerful tool to help in troubleshooting and tuning your environment. I recommend investing a little time in reading the 60 day series about Extended Events. This is not a short series but is designed to provide an array of topics to help learn the tool over time.

Profiler for Extended Events: Quick Settings

Not long ago, I wrote a rather long article about a new-ish feature within SQL Server Management Studio (SSMS) that impacted Extended Events. You can read that book – here! The XEvents Profiler feature is one of those things that you may or may not use. If you consider using the feature, I do believe it is important that you research it a bit and try to learn the pros and cons first.

With that there is a little more about the feature that the aforementioned book did not cover. In fact, this information has pretty much gone ignored and mostly stays hidden under the covers.

Settings

As of SSMS 17.4 we have been given the ability to control XEvents Profiler just a tiny bit more. For what it is worth, we as Database Professionals love to be able to control our database environment. So this teeny tiny bit of new control ability is potentially a huge win, right?

If you are the controlling type, or maybe just the curious type, you will be pleased to know that under “Options” from the Tools menu in SSMS, Microsoft has tucked some new control options to help you configure XEvents Profiler – to a degree. If you open options, you will see this new node.

If you expand the “XEvent Profiler” node (circled in red), you will discover the “options” node. If you click on this “options” node and do a quick comparison (in SSMS 17.4 and SSMS 17.5) you will also find that you don’t need t expand the “XEvent Profiler” node at all because the options are listed in the right hand pane for both nodes and they are exactly the same. So, choose one or the other and you will end up at the same place.

The options that you currently have are:

  • Stop Session on Viewer Closed
  • Toolbar commands stop and restart

You can either set these options to True or False. I recommend you play with them a bit to discover which you really prefer. That said, I do prefer to have the “Stop Session on Viewer Closed” set to true. There is “profiler” in the name of the feature afterall. And if you have read the “book” I wrote about this feature, you would know that the filtering offered by the default sessions of this feature basically turn on the fire hose effect and can have a negative impact on your server. Are you sure you want a profiler style fire hose running on your production server?

Conclusion

There surely will continue to be more development around this idea of an XE style profiler. More development generally means that the product will mature and get better over time. This article shows how there is more being added to the feature to try and give you better control over the tool. We love control so the addition of these options is actually a good thing. Is it enough to sway me away from using the already established, more mature, and high performing tools that have been there for several generations? Nope! I will continue to use TSQL and the GUI tools available for XE that predated the XEvent Profiler.

Some say this is a way of bridging the gap. In my opinion, that gap was already bridged with the GUI that has been available for several years. Some say that maybe this tool needs to integrate a way to shred XML faster. To that, I say there are methods already available for that such as Powershell, the live data viewer, the Target Data viewer, or even my tools I have provided in the 60 day series.

I would challenge those that are still unfamiliar with the XE GUI (out for nearly 6 years now) to go and read some of my articles or articles by Jonathan Kehayias about the power that is in XE as well as some of the power in the GUI.

SQL Server User Already Exists – Back to Basics

One of my all-time favorite things in SQL Server is security. No matter what, it always seems that there is a new way to abuse permissions. When people abuse their access level or abuse the way permissions should be set in a SQL Server environment, we get the pleasure of both fixing it and then trying to educate them on why what they did was wrong and how to do it the right way.

In similar fashion, I previously wrote about some fundamental misconceptions about permissions here and here. I have to bring those specific articles up because this latest experience involves the basics discussed in those articles along with a different twist.

I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Gimme Gimme Gimme…

It is not uncommon to need to create a login and grant that login access to a database (or associate that login to a database user. In fact, that is probably a fairly routine process. It is so routine, that I have a demo script for it right here.

I even went as far as to include some of the very routine mistakes I see happening on a frequent basis (as referenced by a prior post here).

To this point, we only have a mild abuse of how to set permissions for a principal. Now it is time for that twist I mentioned. This user account needs to be created on a secondary server that is participating in either a mirror or an Availability Group. Most people will take that user account that was just created on the first server and then use the same script to add the account to the secondary server. Let’s see how that might look.

For this example, I will not go to the extent of creating the mirror or AG. Rather, I will pretend I am just moving the database to a new server. So I have taken a backup and then I will restore the database to the new server.

Next, let’s go ahead and recreate the login we created on the previous server.

You see here that I am only going to create the login if it does not exist already. Running the script produces the following for me.

Now, let’s deviate a bit and grant permissions for the login just like so many administrators will do.

It seems pretty apparent that my login that I just created does not have access to the GimmeSA database, right? Let’s go ahead and add permissions to the GimmeSA database and see what happens.

Well, that did not work according to plan right? Enter twist the second.

What I am seeing more and more of, is people at this point will just grant that login (that was just created) sysadmin rights. You can pick up your jaw now. Indeed! People are just granting the user SA permissions and calling it good. This practice will certainly work – or appear to work. The fact is, the problem is not fixed. This practice has only camouflaged the problem and it will come back at some future date. That date may be when somebody like me comes along and starts working on stripping non-essential sysadmins from the system.

There are two legitimate fixes for this particular problem (and no granting sysadmin is definitely not one of them). First you can run an orphan fix with a script such as this one by Ted Krueger. That will map the user that already exists in the database to the login principal (thus the reason for the error we saw). Or, you can prep your environment better by using the SID syntax with the create login as follows.

The trick here is to go and lookup the SID for the login on the old server first and then use that sid to create the login on the new server. This will preserve the user to login mappings and prevent the orphan user issue we just saw. It will also prevent the band-aid need of adding the login to the sysadmin server role.

The Wrap

In this article I have introduced you to some basics in regards to creating and synchronizing principals across different servers. Sometimes we try to shortcut the basics and apply band-aids that make absolutely no sense from either a practical point of view or a security point of view. Adhering to better practices will ease your administration burden along with improving your overall security presence.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Database Corruption and IO Errors

A quick way to have your day turned upside down and rip your gut out with nerves and anxiety is to come in one day to find that users are panicked, applications are not working and the HelpDesk team is curled up in the fetal position in the corner. Why? The sky is falling and everybody thinks the database has blown up.

Calmly, you settle in and check the server and eventually find your way to the error logs to see the following:

Msg 823, Level 24, State 2, Line 1

The operating system returned error 1(Incorrect function.) to SQL Server during a read at offset 0x0000104c05e000 in file ‘E:\Database\myproddb.mdf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Suddenly you understand and feel the collective fear and paranoia. What do you do now that the world has seemingly come to an end for your database?

Corruption

What exactly does this error message mean? Well, typically, an 823 error is a very strong indicator that there is some sort of problem with the storage system, hardware or driver that is in the path of the I/O request.

Great! That fear is getting a little heavier knowing what the error represents. This doesn’t bode well for the database. Let’s go ahead and crack out the list of what we can do or check when a problem like this hits:

  1. Check msdb.dbo.suspect_pages
  2. Run a consistency check for all databases on the same volume
  3. Check Logs (SQL, Windows, Storage system) to see if there may be additional info (via different errors/warnings) in close proximity to the 823 error.
  4. Check your drivers
  5. Restore the database

This is where your experience, training, and preparedness come in handy. An experienced data professional will be prepared with database backups (including log backups). So you are not concerned here because all of your backups are reporting successful. As you prep to pull the backups (for the past couple of days just in case) you notice that there are no available backups in your repository. Looking closer at your backup jobs you discover that the backups completed in mere seconds where they normally take hours for this database.

Now that your heart is racing, forehead is beading up with sweat, gut is sinking and the fear is very palpable – what do you do? Time to step through the rest of the steps and pull out your lucky charms, right?

Querying against suspect_pages, you find the table to be completely empty. You know that checkdb runs regularly but maybe it didn’t run last night. That is easy enough to check with a little query from here. Since a consistency check does not seem to have run (as confirmed by the script) and is the second item on the checklist, let’s go ahead and run it now.

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Crud. Blood pressure and nerves are getting a little more frazzled now. Maybe we can cycle through the database and find which table is causing the problem. Let’s try a checktable of every table in the database. Before doing the checktable, one more check against suspect_pages still shows no rows to be found.

Running the checktable, every table is coming up clean except one. That one table produces the same sort of error as the checkdb and just so happens to be the largest and most critical table to the database. Blood pressure is closing in on critical now. We have a corruption issue that is severe enough that checktable cannot complete, we know we have 823 errors and some sort of IO issue and do not have a backup.

Wait…backup. Let’s try to force a backup and see what happens. We can tell the backup to continue after error so let’s see what happens. Maybe that will allow you to move the database to a different server or different spindles to try and just recover the data.

Msg 3202, Level 16, State 2, Line 1

Write on “E:\SQLBackups\myproddb.bak” failed: 1(Incorrect function.)

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

The situation just does not want to get any better at this point. Time for drastic measures – shut down the SQL Server services and try to xcopy the data and log files to a different server and try to re-attach from there. Anything is worth a shot, right?

Error 1: Incorrect Function

Glad to know it is not just SQL Server throwing the errors – sorta. The corruption is ever present and there is nothing more that can be done, right? All hope is lost. Time to fill out the resume and move on to something else, right? Give it one more shot. A spark of insanity hits and you wonder if a mere query against the clustered index will work, if not then maybe something against any of the indexes to try and spare any data at all.

You rub your luck horseshoe and query the table (yes the table that checktable aborts because of corruption). Lo and behold you get results from this very simple query. How is that possible. On a whim, you drop all the Non-Clustered Indexes and try a fresh backup.

Hallelujah! The backup completes without error. Time to take this backup and restore the database to a completely different server. Then on the new server run a consistency check to determine if it is all clear. To your liking, there is absolutely no corruption at this point so the non-clustered indexes can be recreated (easy to do because you have a script with the index definitions handy).

Wrap

This journey from fire and brimstone and the world ending to the epiphany and then the sweet euphoric feelings of success is not a normal resolution for these types of errors. This sort of thing happened for a client that called trying to get around the 823 errors. We had absolutely no indication whatsoever of where the corruption was beyond knowing we had failing disks at the time. We got lucky in that the non-clustered indexes in this case ended up being stored on the bad sectors and dropping those indexes allowed us to recover the database and make it usable.

When everything is breaking, the unconventional thought (especially without having a checkdb complete to tell you which index or which pages are corrupt) of dropping indexes may just save your bacon. It saved my clients bacon!

As a follow-up item, it is important to regularly check the dbccLastKnownGood for each database. That can be done by following the script in this article.

Checking the Health of your CLR

Comments: 1 Comment
Published on: January 16, 2018

The Common Language Runtime (CLR) is the fundamental nerve center of the Microsoft .NET Framework. It is this nerve center that provides the execution environment for all .NET Framework code. One may sometimes refer to CLR code as managed code.

CLR hosted from within SQL Server (CLR Integration), provides the ability to create stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates. And because managed code compiles to native code, we sometimes see a bit of a performance gain for various scenarios.

It is this bit of a performance gain, that we may see more CLR integration use within our SQL environments. And with increased CLR there will be an increased chance for something to go a little haywire. If things can (and will go haywire), it is important to have a means to be able to monitor them.

If you know me, you are probably aware that when it comes to monitoring a problem, I will most probably recommend a solution that involves a little bit of XE. If you don’t know me, here is a hint: some of those monitoring tools can be found in this series.

CLR

I had a client reach out recently because they were having all sorts of issues with their CLR procs. The client is losing some sleep, a little weight, and a fair amount of hair trying to figure out what is causing their CLR nightmares. This client has magic CLR. The CLR works fabulous or a day or three. Then suddenly the CLR procs just disappear and have to be re-created on the server. Granted this seems more like a problem of somebody did something they didn’t realize they were doing and poof the CLR is gone.

So, what can we do about this? The answer is quite simple. I am going to use an extended event session that will monitor the various aspects of CLR from a couple of different angles. First, I need to try and find events that fit my needs. I can do that with the queries and instructions found here.

As I query the XEvent catalog, I can see that there is a limited set of options and they can be seen in the following image.

Most of the events can be found in the sqlclr package and in the debug channel. The exception in both cases being the “assembly_load” event. I will go ahead and add all of those events to my session except for the garbage collection event.

The next step is to help cover my bases and see if somebody might be changing the objects and causing the odd behavior. I will do that with the object_created, object_altered, and object_deleted events. The caveat here is I will limit the scope of those events to only look for CLR specific changes. I will be able to do that by filtering on object_type in each of those three events. Here is a look at those object types.

 

In Extended Events, we see that we can monitor for object changes in the various different objects such as “TRIGASM”,”PROCASM”, and “FNTABASM”. What do these map to though? Well, here is the answer to that question!

  • 16724 TRIGASM = Assembly (CLR) DML trigger
  • 17232 PROCASM = Assembly (CLR) stored-procedure
  • 21313 ASM = CLR Assembly
  • 21318 FNSCLASM = Assembly (CLR) scalar-function
  • 21574 FNTABASM = Assembly (CLR) table-valued function
  • 17985 AGG — Assembl Aggregate function (CLR)

With all of this rolled together, we can now assemble the XE Session.

Once deployed, I can go ahead and follow the instructions here to test different CLR managed code objects.

The Wrap

Having a tool to be able to monitor CLR health will be essential as you deploy more and more managed code within SQL Server. Extended Events offers a great lightweight means to do just that. This article has shown how to deploy a session that will capture the various changes with CLR objects within our database environment. In addition, you will be able to capture various conditions related to performance or problems with the managed code. You may even recognize some of the CLR events from the system_health session.

If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.

SQL Server Configurations – Back to Basics

One thing that SQL Server does very well is come pre-configured in a lot of ways. These pre-configured settings would be called defaults. Having default settings is not a bad thing nor is it necessarily a good thing.

For me, the defaults lie somewhere in the middle ground and they are kind of just there. You see, having defaults can be good for a horde of people. On the other hand, the default settings can be far from optimal for your specific conditions.

The real key with default settings is to understand what they are and how to get to them. This article is going to go through some of the basics around one group of these defaults. That group of settings will be accessible via the sp_configure system stored procedure. You may already know some of these basics, and that is ok.

I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Some Assembly Required…

Three dreaded words we all love to despise but have learned to deal with over the past several years – some assembly required. More and more we find ourselves needing to assemble our own furniture, bookcases, barbecue grills, and bathroom sinks. We do occasionally want some form of set and forget it.

The problem with set it and forget it type of settings (or defaults) is as I mentioned – they don’t always work for every environment. We do occasionally need to manually adjust settings for what is optimal for that database, server, and/or environment.

When we fail to reconfigure the defaults, we could end up with a constant firefight that we just don’t ever seem to be able to win.

So how do we find some of these settings that can help us customize our environment for the better (or worse)? Let’s start taking a crack at this cool procedure called sp_configure! Ok, so maybe I oversold that a bit – but there is some coolness to it.

Looking at msdn about sp_configure I can see that it is a procedure to display or change global configuration settings for the current server.

If I run sp_configure without any parameters, I will get a complete result set of the configurable options via this procedure. Let’s look at how easy that is:

Ok, so that was exceptionally easy. I can see that the procedure returns the configurable settings, the max value for the setting, configured value, and the running value for each setting. That is basic information, right? If I want a little more detailed information, guess what? I can query a catalog view to learn even more about the configurations – sys.configurations.

That query will also show me (in addition to what I already know from sp_configure) a description for each setting, if the setting is a dynamic setting and whether or not the setting is an advanced configuration (and thus requires “show advanced options” to be enabled). Pro-tip: The procedure just queries the catalog view anyway. Here is a snippet from the proc text.

Seeing that we have some configurations that are advanced and there is this option called “show advanced options”, let’s play a little bit with how to enable or disable that setting.

With the result (on my system) being:

We can see there that the configuration had no effect because I already had the setting enabled. Nonetheless, the attempt to change still succeeded. Let’s try it a different way.

I ran a whole bunch of variations there for giggles. Notice how I continue to try different words or length of words until it finally errors? All of them have the same net effect (except the attempt that failed) they will change the configuration “show advanced options”. This is because all that is required (as portrayed in the failure message) is that the term provided is enough to make it unique. The uniqueness requirement (shortcut) is illustrated by this code block from sp_configure.

See the use of the wildcards and the “like” term? This is allowing us to shortcut the configuration name – as long as we use a unique term. If I select a term that is not unique, then the proc will output every configuration option that matches the term I used. From the example I used, I would get this output as duplicates to the term I used.

Ah, I can see the option I need! I can now just copy and paste that option (for the sake of simplicity) into my query and just proceed along my merry way. This is a great shortcut if you can’t remember the exact full config name or if you happen to be really bad at spelling.

The Wrap

In this article I have introduced you to some basics in regards to default server settings and how to quickly see or change those settings. Not every environment is able to rely on set-it and forget-it type of defaults. Adopting the mentality that “some assembly is required” with your environments is a good approach. It will help keep you on top of your configurations at the bare minimum. This article will help serve a decent foundation for some near future articles. Stay tuned!

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

Common Tempdb Trace Flags – Back to Basics

Once in a while I come across something that sounds fun or interesting and decide to dive a little deeper into it. That happened to me recently and caused me to preempt my scheduled post and work on writing up something entirely different. Why? Because this seemed like fun and useful.

So what is it I am yammering on about that was fun?

I think we can probably concede that there are some best practices flying around in regards to the configuration of tempdb. One of those best practices is in regards to two trace flags within SQL Server. These trace flags are 1117 and 1118. Here is a little bit of background on the trace flags and what they do.

A caveat I have now for the use of trace flags is that I err on the same side as Kendra (author of the article just mentioned). I don’t generally like to enable trace flags unless it is very warranted for a very specific condition. As Kendra mentions, TF 1117 will impact more than just the tempdb data files. So use that one with caution.

Ancient Artifacts

With the release of SQL Server 2016, these trace flags were rumored to be a thing of the past and hence completely unnecessary. That is partially true. The trace flag is unneeded and SQL 2016 does have some different behaviors, but does that mean you have to do nothing to get the benefits of these Trace Flags as implemented in 2016?

As it turns out, these trace flags no longer do what they did in previous editions. SQL Server now pretty much has it baked into the product. Buuuuut, do you have to do anything slightly different to make it work? This was something I came across while reading this post and wanted to double check everything. After all, I was also under the belief that it was automatically enabled. So let’s create a script that checks these things for me.

Holy cannoli batman – that is more than a simple script, right? Well, it may be a bit of overkill. I wanted it to work for version before and after and including SQL Server 2016 (when these sweeping changes went into effect). You see, I am checking for versions where the TF was required to make the change and also for versions after the change where the TF has no effect. In 2016 and later, these settings are database scoped and the TF is unnecessary.

The database scoped settings can actually be queried in 2016 more specifically with the following query.

In this query, I am able to determine if mixed_page_allocations and if is_autogrow_all_files are enabled. These settings can be retrieved from sys.databases and sys.filegroups respectively. If I run this query on a server where the defaults were accepted during the install, I would see something like the following.

You can see here, the default settings on my install show something different than the reported behavior. While autogrow all files is enabled, mixed_page_allocations is disabled. This matches what we expect to see by enabling the Trace Flags 1117 and 1118 – for the tempdb database at least. If I look at a user database, I will find that mixed pages is disabled by default still but that autogrow_all_files is also disabled.

In this case, you may or may not want a user database to have all data files grow at the same time. That is a great change to have implemented in SQL Server with SQL 2016. Should you choose to enable it, you can do so on a database by database basis.

As for the trace flags? My query checks to see if maybe you enabled them on your instance or if you don’t have them enabled for the older versions of SQL Server. Then the script generates the appropriate action scripts and allows you to determine if you want to run the generated script or not. And since we are changing trace flags (potentially) I recommend that you also look at this article of mine that discusses how to audit the changing of trace flags. And since that is an XEvent based article, I recommend freshening up on XEvents with this series too!

The Wrap

In this article I have introduced you to some basics in regards to default behaviors and settings in tempdb along with some best practices. It is advisable to investigate from time to time some of these recommendations and confirm what we are really being told so we can avoid confusion and mis-interpretation.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

«page 1 of 12

Calendar
June 2018
M T W T F S S
« May    
 123
45678910
11121314151617
18192021222324
252627282930  

Welcome , today is Sunday, June 24, 2018