Blog

Company News

At this time of year, with the PASS Summit and SQL Intersections and Black Friday rapidly approaching, it’s so easy to get caught up in the rush of all the fun. Many of us are lucky enough to work at for-profit companies that pay well, send their employees to training classes, and buy them tools to do their job better.

This post is not for those lucky folks.

This post is for you, the data professionals at charities and non-profits like some of our past scholarship winners from 2016 and 2017:

Mercy Corps – at work right now in Puerto Rico.
International Justice Mission – a global organization that protects the poor from violence in the developing world.
Elizabeth Glaser Pediatric AIDS Foundation – 400 children are infected with HIV every day. I don’t know how to type those words without crying and taking a break from the keyboard.
Mencap – improving the lives of UK people with a learning disability

If you’re a data professional at an organization like that, we want to help you. (We also want to high-five you, hug you, and put a post-it note on your monitor that says, “Please keep up the good work because the world needs you, but also please take care of yourself because we want you to be around as long as possible.” (It would be a big post-it note.))

Our scholarship program is pretty simple: you get access to all the training we offer next year, live and recorded.

The fine print:

You must already have a job working with SQL Server.
You must work for a foundation, non-profit, charity, or similar company that’s doing good work. It can totally be a for-profit company, just as long as they’re making a difference. (If you work for Ginormous Profitable Global Corporation, forget it.)
Your company or government rules must allow you to receive free training. (Some companies prohibit their employees from accepting gifts.)

~~Update: Applications are closed.~~

[Video] Office Hours 2017/10/11 (With Transcriptions)

Last Updated October 15, 2017

Videos

This week, Brent, Erik, and Richie discuss the new Extended Events Profiler, using SQL audit vs a trigger to log what people do, log shipping, migrating from on-prem to cloud, best practice for a database owner, SQL Server performance improvement, the difference between simple, bulk-logged, and full recovery models and when to use them, tools for transferring databases from one server to another, VSS Snapshot backups, encryption and more.

No headlines this week – sorry about that! We’re hustlin’ to build you some new cool stuff for our Summit pre-con. We think you’re gonna like this a lot!

Office Hours Webcast - 2017/10/11

Watch this video on YouTube

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours – 10-11-17

Brent Ozar: Grahame says, “Does management studio 17.3’s new extended events profiler – does it cause the same performance issues as the old profiler?”

Erik Darling: I just installed it this morning and I haven’t tried it yet, so I don’t know.

Brent Ozar: Yeah, looks – and it’s based off of extended events now, so it should have a lower overhead…

Erik Darling: Should.

Brent Ozar: Just know that when you use extended events, there are fields that you can grab, just like you could with profiler, that will have a huge overhead. The more of those that you pick – things like execution plans – the more overhead you can have.

Erik Darling: Or even SQL text is another one that can cause a bunch of overhead; from just sort of messing around with it too. So for some things, there’s the integer field and then the text equivalent of the field, like say for database ID versus database name. And there’s even probably less overhead for just filtering on the database ID than filtering on the database name, because database name isn’t part of every single action or event, so you have to like add it sometimes and then filter on it, so it gets weird. I’m actually poking at it now… what are you doing?

Brent Ozar: And I’d heard, somebody on Twitter said, if you just close SSMS like you close the profiler piece of it, that the trace keeps running and it was rowing a file on his drive. So I don’t know if that makes a difference on how you stop the trace or what’s up with that.

Erik Darling: When you right click on it, you can stop or launch the session. So if you just close the window, I imagine the session keeps running until you right click and hit stop. Just like with a regular extended event, you have to stop it for it to stop.

Brent Ozar: Makes sense… [crosstalk]

Brent Ozar: Let’s see, Emily says – which is my sister’s name – “Apologies I’m late…” That’s alright, we were late getting to your question too. “What are the pros and cons of using a SQL audit, so the built-in audit functionality, versus a trigger, in order to log what people do?” Richie, have you ever had to build anything that logs everything that people do in a database?

Richie Rump: No, because that’s usually really slow, so I’ve…

Brent Ozar: But isn’t everything you build really slow though?

Richie Rump: Yes, that’s only because you touch it and magic happens…

Brent Ozar: What happens if I do this right here, Richie?

Richie Rump: Don’t touch that.

Erik Darling: It’s always the end user that’s the problem. Everything Richie builds is fine until someone has to use it, and then…

Richie Rump: That’s correct. [crosstalk]

Brent Ozar: I can testify to that. It’s mostly operator error. Erik, have you ever had to do this?

Erik Darling: Build it, no. Support it, yes. And, you know, relativity, the audit table, they were just massive triggers that every time something happened, everything would go and get logged, and there were some really bad column choices in there. There’s like an XML column, and then like an nverchar max version of the XML column. There’s some really goofy stuff in there. So I’ve had to support it and like deal with queries that hit it that were just unfortunate, but as far as actually design it, no, thankfully.

Brent Ozar: Yeah, the thing with SQL Server’s built-in auditing is that it’s kind of like a construction kit. Really, just like a trigger, you can go build things with a construction kit, but it’s up to you to build things like reporting. What I usually end up saying is, is it just because we want to do it, or is this something that legally we’re required to do? Like the auditors are standing out there with a gun to our heads going, “You better capture everything that anyone sees.” In that case, the third party appliances are a much better answer.

Brent Ozar: Let’s see, Wes says, “Let’s isolate that quote out of context, Richie said to Brent, only because you touch it; magic happens when you touch it.” That should go down in fame. Two years ago, three years ago, I was teaching a PASS Summit pre-con, and we started at the beginning of the day and I’m like, “Look, just so you know, I understand if you’re sitting in those hard seats all day, you know, and you’re sitting there for six hours your butt gets hard.” You know, something about hard things in your butt and after six hours. And as the words are coming out of my math, everyone is turning to me and it’s like, I’m just going to stop talking now.

Brent Ozar: Wes says, “I remember but probably not, I was at a place where…” Oh, he said at his last job he was at a place where he had to learn ProgressDB and he was like, nope, I’m out. That’s funny, because we had a question on Twitter from somebody this week who was asking me and Richie how much work is it to learn a NoSQL database.

Richie Rump: Hold on to your butts, big guy.

Brent Ozar: I can kind of feel you, because I was a SQL Server DBA for the longest time and I had the opportunity to learn Oracle, if I wanted to learn it; like there were a couple of Oracle DBAs in the same shop. And I liked them, they were really cool guys, but I was like, “Nah, you know what, I’m really kind of good right here. I kind of know what I know here and I’m kind of fine.” It’s not that I don’t like to love – like to learn, I just didn’t want to learn Oracle at that time.

Richie Rump: He also doesn’t like to love.

Brent Ozar: I don’t like to love. I love to hate.

Erik Darling: Learning and loving, off the map.

Brent Ozar: And I’m not learning to love, and I’m not loving to learn. That’s totally not true; I adore learning and I’m going to be all over Postgres here shortly. Not because I think it’s a better database than SQL Server, we’re just using it for some of the projects that we have in-house.

Richie Rump: But he’s …

Erik Darling: do you have any hobbyhorse prod things that you’re learning, or are you all just work focused on learning?

Brent Ozar: Oh, so I love learning about what dotcoms are doing, just architecturally what other platforms are out there? Where are people storing their data? Why do they make those choices? What are the pros and cons of those architectures? Like I could learn about a new database every day for a year, and I would just love doing that. Managing it from day to day and being on call for it? No, I’m not really interested in that. But learning about them, I’m like yeah this is really cool. The compromise is that they make – so you’re starting to learn C, right? Or you were dabbling in it? Yeah, C++…

Erik Darling: You can see from the bookmark that I even made a little bit of progress.

Brent Ozar: That’s awesome. Not progress in the database but just progress. See that actually calls to me, only because I would love to learn to read MySQL, Postgres, like the source code behind those databases. That, to me, is a good time.

Erik Darling: Nope, it’s all C in and C out; that’s all it is. [crosstalk]

Richie Rump: The only C I want to see is this C right here, that’s it. No language of C…

Brent Ozar: But you want to see a W next to that.

Richie Rump: The W is right here.

Brent Ozar: Joseph says, “101010,” in the questions.

Brent Ozar: Thomas says, “If I log ship to another SQL Server to test before migrating, can I use those databases on the secondary to set up an availability group before dropping the log shipping; like ship from one place to one place to the other?” The thing with log shipping is, whenever you start restoring on the other end, if you want to do something with those databases, like test them, you’re generally going to break out of log shipping, because you’re going to want to write to those. You’re going to want to do inserts, updates, and deletes. So generally, log shipping isn’t as useful for migration testing. It’s great for migration; just not as great for the testing part.

Erik Darling: You can put them in standby and just let some queries fly at them, but they won’t be writable.

Brent Ozar: Khalid says, “I have one more question, please…” Absolutely, just in the side of your monitor, you’re going to see a slot for $20 bills, just slide it right inside – oh, your monitor may not have that. Well if your monitor doesn’t have that, you can ask questions for free; just keep asking them over in the questions tab.

Brent Ozar: J.H. says, “Our app team is looking to migrate their app servers up into the Amazon web services cloud, and they want to continue…” They want their apps up in Amazon and they want their SQL Server on premises. “Is there anything that I should do on the database administrator side?” Run for your life. So what would be the problems you guys would expect to see with the apps in the cloud and the SQL Server on premises?

Erik Darling: Richie’s favorite cloud word, permissions, and security, would be the first – those would be the first two things that come to mind.

Richie Rump: Yeah, connections. So the app may not be ready to handle drop connections. So talking to the cloud, if you don’t have automatic retries in your code, you’re going to need that, especially when you talking to a database, and maybe wrapping transactions around things, because the connection could drop at any time. –

Brent Ozar: Latency is going to suck pretty bad too. If you have an app that does one-row insert and then waits to get it back, another row insert, waits to get it back, that is a recipe for slow latency.

Richie rump: Yeah, and where’s the app living if the app is leaving in your own kind of collocation facility, now that has got to jump, go through the internet, go through all the hoops to get into the cloud, and then the own network in the cloud, and then it’s got to go all the way back. So that’s why you have a lot of cloud apps there in the cloud. They don’t have this separation between my network and your network and oh we’re going to have it in the cloud, because that whole latency thing is…

Brent Ozar: I’d be curious to hear more about why you’re putting the app up there and keeping the SQL Server database down on premises too, just out of curiosity.

Erik Darling: That might even be temporary. Get the app up there and then move the database up during another window?

Brent Ozar: In which case, stuff like log shipping and availability groups may make it easier for you to get that up their faster. I would move [crosstalk]…

Erik Darling: That’s not replication.

Brent Ozar: Khalid says, “What’s the best practice on who should be the owner of the database?” He says, “It defaults to the person who created it, but what’s like a best practice for the database owner?”

Erik Darling: Usually SA. I’ve never seen anything terrible happen to a database from a user owning it. They will have, you know, elevated permissions or privileges on it, but it’s not like with an agent job or something, where if the Windows account gets disabled then the database will shut down.

Brent Ozar: We alert you for it in sp_Blitz too, like that a database is owned by an end user. And we’ve got more details on that link. If any of you haven’t run it before, sp_Blitz is our totally free health check utility that goes and gives you a quick report, like an Excel spreadsheet type analysis of the problems with your SQL Server.

Brent Ozar: DB… Oh, DBA – funny how they spelled their name there – says, “In my first DBA job I inherited servers set up by a non-DBA.” I think that’s kind of how it always happens too. “We have a mess of over permissions and nonspecific accounts. Can you talk a little bit about the accounts that are used for proxy accounts versus credential accounts? How many proxies do normal shops use? Is the command shell proxy credential normally ever needed?” No…

Erik Darling: You should have read that one first.

Brent Ozar: No… We don’t deal much with security, but I don’t touch proxy accounts at all; like I’ve never used a proxy account. What I would do is – so here’s the problem. If you want to tighten down security permissions, if you want to start taking things away from people, you need to expect that apps are going to break. Things that are currently working today are going to start breaking. So what I would do, instead of tightening down on servers that already exist, is go build yourself – this is going to sound like a consultant answer – go build yourself the next SQL Server. Go build the SQL 2016 or 2017 that you want to move towards, with only the permissions needed inside there. And as people start to migrate their applications and test on the new box, it can be really tightened down. And when people say, I want SA permissions on this or I want to use a different proxy account or I need passwords to some proxy account, you can start asking why, and that’s where the documentation comes from. Until then, you just say no, you can’t have that. That’s why they joke that DBA stands for don’t bother asking.

Brent Ozar: Aaron says, “We’re upgrading our SQL Server from 2008 R2 to 2017…” Leaps and bounds there. “We currently run our ERP app on the same box as SQL Server. If we were to separate those two boxes, can we expect to see a noticeable performance improvement? We typically have 20 concurrent users in the system?”

Erik Darling: That’s a very broad question.

Brent Ozar: If someone says, I want to find a performance improvement in the server, where do you start?

Erik Darling: Memory, always memory. Memory is like my first thing.

Brent Ozar: What did you say, I forgot?

Richie Rump: Jeez.

Erik Darling: Even Richie cringed on that one, man, that’s rough. That’s a bad reception if Richie…

Brent Ozar: If you had a limited amount of RAM in the VM or whatever before and it was all on this same box and now you’re splitting it out, maybe now the app gets more memory. But maybe you’re changing the box too, maybe you’re changing how much horsepower is in the box. I would look at what are your primary wait stats today, like what wait types are your biggest ones? And it just so happens, watch our blog for this week, we have a post on how to do a performance check.

Erik Darling: Another thing that I see sometimes when people say that they have an app- running alongside it is it’s not just an app running. People are like terminaling in to use the app on the server, so there’s like the overhead or RDP or however else they get in on there to do it. Sometimes the app is like your browser interface, so when you have like 15 people in with IE open just clanking away on stuff, you’re like, stop.

Richie Rump: Yeah, I’d like to ask a question, and the question is, what’s your performance problem on the machine right now? So if you know what your performance issues are, then you could probably say having two different boxes will improve performance.

Erik Darling: Or you can say we’re going to move this to such a gigantic box that no one will trample on each other and everything will be fine.

Richie Rump: Or maybe you don’t have any performance issues and it’s already super fats and moving over to another box with a lot of hardware is not going to do anything?

Erik Darling: A lot of ifs in there.

Brent Ozar: There’s also licensing too. Since SQL Server is licensed by the CPU core, sometimes you can get bang for the buck just by peeling out to lots more cores, taking the apps and putting it somewhere else, and then SQL Server gets its own cores. That may not even affect performance altogether. You might be able to shrink the amount of CPU cores that you have licensed for SQL Server. Aaron follows up with, “The app is the normal bottleneck, and it tends to be CPU limited.” Perfect, in that case, set yourself up other VMs for the app server, give them as much CPU power as you want because you don’t have to pay SQL Server licensing for those cores. That will be good.

Erik Darling: When I was dealing with relativity stuff, it’s very carefully spelled out that you have your SQL Servers over here doing stuff, you have your app servers over here, you have worker servers over here that do things; everything is very separated out. Like some VMs will pull double duty, like with what they do, but they have just one application on them doing it, you know, whether if it’s DTI indexing or doing whatever agent-y stuff has to be done in the background to make sure files are in the right places. But really, it’s a good way to look at architecture for an app, because it gives you some precedence to say no, we need to separate these things out into different places.

Brent Ozar: Perfect.

Brent Ozar: Chris asks, “What’s the difference between simple recovery model, bulk-logged and full recovery model, and when would I use each one?”

Erik Darling: Wow, you just want to like start up here and travel down…

Brent Ozar: In the beginning, there was a database…

Erik Darling: So, the thing that’s not the difference, aside from some circumstances, is what gets logged. A lot of people think that is they use simple recovery, it logs less. Or if I use bulk recover, it logs less. Really, only when you get minimal logging on insert is that true, and for certain other things like index rebuilds can that be true. But for every other regular transaction, so like every other update, delete, all that other stuff, inserts that aren’t minimally logged, the same amount of stuff gets logged. What changes is who’s responsible for transaction log backups and also what HADR text can run on different recovery models. So in simple, its SQL Server will truncate the log and do stuff that it needs to; in bulk and full that’s on you. And bulk you can log ship, full you can log ship, full you can do mirroring, AGs, failover clusters, it doesn’t matter a lick. I don’t know, what are some other good ones?

Brent Ozar: Usually it all comes down to how much data you’re willing to lose; that’s the big thing I start with.

Erik Darling: When would you use them? RPO and RTO – that’s business though.

Brent Ozar: Yeah, long-running transactions would be the other thing that scares me. If some yo-yo does a BEGINTRAN and locks his workstation, goes home for the week after inserting one record… Hello, full hard drive.

Richie Rump: Why are you looking at me when you say that, Brent?

Brent Ozar: My own webcams are right there, so I’m also looking at myself…

Richie Rump: Oh sure, uh-huh.

Brent Ozar: Carlos asks, “Any tools or suggestions to transfer a bunch of databases from one SQL Server to another? No migration, just transfer them.” Oh, just like copying – sp_DatabaseRestore. So if you download our first responder kit, Tara wrote this cool stored procedure that just restores all of the database backups in a folder. It just pulls the most recent full, the most recent diff and all the logs since. So in our first responder kit, look for sp_DatabaseRestore, it works beautifully with Ola Hallengren’s maintenance scripts as well.

Brent Ozar: Grahame says, “Do you always recommend running…” I don’t always recommend, but when I do, it usually involves Dos Equis. “Do you recommend always running update statistics after someone drops and creates a temp table? I’ve noticed performance improvements, but should I always do this?

Erik Darling: Well, if you want to know more – I don’t think we’ll go into the full thing here, but a fellow named Paul White, who if you don’t know the name by now, you should jump off a cliff, has a couple of cool articles. One is called, Temp Tables and Stored Procedures, or Temporary Tables and Stored Procedures – Paul is a gentleman, so he spells the whole thing out. He’s not a lazybones like me. And then there’s another one called Temporary Table Caching Explained, and those two can give you a thorough and rigorous mound of information about how temp tables and stored procedures, and often real life, work too. You should ignore my bonehead posts about ghosts of temp tables where I create them and then stored procedures fail because of them.

Richie Rump: Before you read it, just get a cup of coffee. Now make sure you have a quiet place for a couple of hours…

Erik Darling: Yeah, get a cup of coffee, get a divorce, get a cabin in the woods, print the stuff out, go up there, you know.

Brent Ozar: It’s true.

Brent Ozar: Khalid says, “I’m migrating some old databases from 2008 to 2016. In my databases in 2008 there’s some text in text and image fields. I heard these aren’t supported in newer versions. Can I just set the compatibility mode back?” I think text in text and image are still in 2017, aren’t they?

Erik Darling: They are deprecated, but they are still in there. So you don’t even have to set compatibility back, you just have to deal with crappy old data typed.

Brent Ozar: Let’s see, next up Michael says, “I worked with an ERP app where the vendor recommended installing their app on the SQL Server. I refuse to do it.” [crosstalk] I hate it when vendors do that. No one’s around to give you the little gold star for being a good database administrator.

Brent Ozar: Ben says, “Do you have an opinion…” Odds are yes; probably not valid but we have an opinion.”Do you have an opinion on VSS snapshot backups being used for SQL database daily backups? We have app owners and management only wanting to do daily backups.” As long as they’re okay with losing a day’s worth of data, they’re actually magical; they’re fantastic. Just make sure that you’re getting a product that actually integrates with SQL Server, you should see it as a real backup in your backup history. If you don’t then it’s doing a dirty crash consistent snapshot, and your database backups may come back corrupt.

Erik Darling: So the way Brent said, as long as everyone’s cool with the fact that you can lose a day’s worth of data, going back to when that VSS snap is, then proceed. One thing you’re going to want to keep a really close eye on though is your error log. Because when legitimate snaps get taken, you’re going to see two messages happen for each database, one where I/O is frozen – because it quiesces drive activity serially each data file at a time. And as it quiesces them, it starts taking the snapshot, and then when it’s done, it thaws. So you need to be really careful that there’s not a big chunk of time between the freeze and the thaw, because that means that your drive is essentially frozen in time for the entire duration of that. and there have been so many times, you know, me, Tara, even Brent has worked with clients and seen this in the error log where data gets frozen for 30 seconds a minute every time these VSS snaps get takes, either because it takes so long to quiesce dive activity or because it takes so long to copy the snapshot somewhere. It can have a real impact on performance. For you, taking it once a day, there’s a pretty good chance that even if it does that, it will be during some maintenance window; it will be at like midnight when nothing else is going on. But we’ve seen people who are taking like 15 minutes to have some of this stuff for like five, ten, 20 seconds. You’re like, well you just added like 20 seconds onto every query, no wonder it’s slow.

Brent Ozar: Looks like a big long blocking chain when you’re looking in sp_whoisactive.

Richie Rump: So this is how long I’ve been around and how scarred I’ve become. Every time I hear VSS is visual workload store safe, and I’m like no, don’t put it in there, it won’t come out; it’s corrupted.

Brent Ozar: That was my first experience with source control, both as a developer and then as a DBA. I built these VB script things to continuously, like every hour, patrol my databases and check them into visual store safe. And I was so proud of myself until I started losing data in corruption, and then I was not quite as proud of myself.

Richie Rump: Yeah, that wasn’t my first introduction, but when – it was like, we’ve got to backup like all the damn time, because it’s going to get corrupted, that’s what it is.

Brent Ozar: I love how one person, let’s see here, the question is from Grahame. He says, “Looking at premium assurance SQL 2008 R2, I see all the licenses need to have PA purchased.” And as he’s asking the question, as I’m reading the question from him in GoToWebinar, he goes on to Stack Exchange and asks the exact same question. So Grahame, now I know what you’re account is, Grahame, you’re apparently ThunderCougarFalcon. That’s an awesome, ThunderCougarFalcon, how cool is that? That’s fantastic.

Erik Darling: It’s like one of those password generators where it’s like three random words and then some numbers.

Brent Ozar: Correctcoursebatteriestable.

Erik Darling: I bet that’s his password too, we should all try it.

Brent Ozar: Next question in here, let’s see, Joe asks, “Are you aware of a way to create something like a virtual SQL Server for developers? My developers want to develop against SQL Server, but I don’t want to give them a whole full blown SQL Server. What should I use? Like a preconfigured VM?”

Erik Darling: Oh, that’s a good question.

Brent Ozar: Richie’s looking like he swallowed a cat.

Richie Rump: I just install the developer edition in my machine. That’s like – when I get a machine, the first thing I do is install developer edition. But I’m probably a little bit different than most of your developers because I hang in the middle of development and database land. So I’m like this weird oddball guy. There are ways that you can actually get a VM of that and if somebody else talks for a minute then I’ll look it up.

Brent Ozar: I would say also, this is what – so developer edition, first off, is free. There are no strings around it, totally free, you can install it on their machine. If you don’t want to patch it, this is one of the cool things that Microsoft came up with in 2017, Docker support. So you could spin up a Docker VM as often as you wanted. I actually think it’s easier for most Windows shops to just install SQL Server developer edition on the developer’s machine. But if you’re a Linux friendly shop or a Mac-friendly shop, that’s where spinning up a Docker VM can make a difference. [crosstalk]

Richie Rump: You could even go to the Docker store or package manager or whatever and download MS SQL and it will just be there. So Docker was the thing that I was thinking of, but I haven’t jumped into the Docker pool yet, I’m still old school; I run 46s with VX2. [crosstalk]

Erik Darling: If you’re on any sort of modern-ish version of SQL too, it actually just came to 2012 SP4, and you don’t want to have a full-blown database on every developer machine, you can use DBCC CLONEDATABASE to have a statistics only copy of your database available for people to do development against. Just don’t rebuild indexes or update stats, because everything will go… But, as long as they don’t do anything goofy like that, the DBCC CLONEDATABASE is a pretty cool command. It’s just a stats only copy of your database. You can do that on 2012 SP4, 2014 SP1, that was where it dropped, 2016 and 2017. So all the good places that you would hopefully be doing your development against, that’s available.

Brent Ozar: And kind of following up, related, Thomas says, “What’s the best way to give devs a comparable database to prod without giving them prod data?” This is a really easy way to do it, just keep in mind it has no data. And if you want any config tables, for example, you may have to script those and include those as well.

Richie Rump: That’s like one of the hardest problems we have as developers, to create a good test data set so that we could actually do things that prod is eventually going to do. And we don’t want prod data, there’s no way that I think any sane developer would ever want prod data. And if they do want prod data, it’s because they want to make sure that it’s running at the same size, breadth, and speed that prod would get into it. So that’s why getting a stats only copy would be good, but if I’m actually writing T-SQL and testing it and doing all that, it doesn’t help me much because there’s no data here.

Brent Ozar: One quick one, Chris says, “I get the following error when running sp_Blitz on a 2008 R2 box. I have an arithmetic overflow error.” Make sure, first off, that you’re on the latest version of sp_Blitz because it’s possible that that’s an older one. We’ve fixed several of those bugs. It may also be that the thing has been up for, say, two or three years and wait stats have gotten really high. I thought we’d fixed that bug, but just in case, I would go look in the wait stats question. Or just try – this is going to be awful to say – I bet Erik has a better answer than me.

Erik Darling: I know I do.

Brent Ozar: Go ahead.

Erik Darling: So just – if you want to try running it with debug equals one, you can figure out which step that happens on. Then, if you want – you can figure out if it’s the query that’s doing it or if we’re doing something wrong. And if we’re doing something wrong, you can tell us about it on GitHub. Just make sure you have whatever check ID is running when the arithmetic overflow happens, and we can do a better job investigating.

Richie Rump: What if you put debug equals 42?

Erik Darling: I think it’s a bit, so you’ll probably get an arithmetic overflow error there. Actually no, it’s a tiny int, because you can do debug equals two if you want to print out the dynamic SQL that runs as well as the check IDs.

Brent Ozar: Then J.H. asks, “When we’re trying to encrypt all out network connections, what should I look out for?” Go his this post by Aaron Bertrand, SQL Server Support for TLS 1.2. There are about another dozen questions that we didn’t get a chance to get to. Don’t hang around waiting for us for another week, head on over to DBA.StackExchange.com, and you can post questions about SQL Server. We answer all the time. It happens to be a different VM than I work in. I work in this one but if I had my real one up, it would show my reputation and all that kind of thing. But Erik and I sit around all day answering questions on Stack, not in here webcast.

Richie Rump: That’s right, they do.

Brent Ozar: So thanks everybody for hanging out with us this week, and we will see you all next week on Office Hours. Adios.

How to Log Wait Stats to Table with sp_BlitzFirst

Last Updated October 13, 2017

First Responder Kit, Wait Stats

When you’re analyzing SQL Server performance, one of the best places to start is wait stats. What’s your SQL Server waiting on?

sp_BlitzFirst makes it really easy to trend this data over time for free with all currently supported versions of SQL Server (2008+). Just set up an Agent job to run this every 15 minutes:

EXEC sp_BlitzFirst 
  @OutputDatabaseName = 'DBAtools', 
  @OutputSchemaName = 'dbo', 
  @OutputTableName = 'BlitzFirst',
  @OutputTableNameFileStats = 'BlitzFirst_FileStats',
  @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
  @OutputTableNameWaitStats = 'BlitzFirst_WaitStats';

EXEC sp_BlitzFirst

@OutputDatabaseName = 'DBAtools',

@OutputSchemaName = 'dbo',

@OutputTableName = 'BlitzFirst',

@OutputTableNameFileStats = 'BlitzFirst_FileStats',

@OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',

@OutputTableNameWaitStats = 'BlitzFirst_WaitStats';

sp_BlitzFirst will create those tables if they don’t already exist. (The database & schema have to already exist, though.) It logs cumulative data in those tables just like the DMVs they’re sourced from:

sys.dm_os_wait_stats
sys.dm_os_performance_counters
sys.dm_io_virtual_file_stats

The tables are designed to be combined centrally from all of your servers, too – they include columns for ServerName and CheckDate (which is stored in datetimeoffset format, so you can report on data from servers in different time zones.)

But since that data is cumulative, it can be a little painful to query – so sp_BlitzFirst also creates a set of delta views for you. They’re named exactly the same as the table names you picked, but with _Deltas added at the end, like BlitzFirst_WaitStats_Deltas. These join the current sample with the prior sample to figure out how much time elapsed between samples, and how much wait time you had per minute during that sample.

Throw that into Microsoft’s free Power BI tool, and presto:

From here, you can drill down into the other tables to see why the SQL Server was slow during that time range. Heck, armed with the other First Responder Kit scripts and their abilities to log data to table, you can even find out what queries were causing your high wait stats.

And it’s all totally free.

Wanna learn how? We’ll teach you in the very first module of our Expert Performance Tuning for 2016 & 2017 class (and yes, that includes those of you in our PASS Summit pre-con!)

How to Do a SQL Server Architecture Review

Last Updated October 12, 2017

Architecture, Processes and Practices

We’ve talked about how to do a free SQL Server health check, and then how to drill deeper with performance check.

After these two, management usually steps back and asks, “Wait a minute – how’d we get into this mess? And long term, strategically, what should we do next?”

And usually, we’re in a hot mess because we’ve underprovisioned hardware capabilities relative to our query performance goals, not to mention our backup/recovery goals. Our databases grew larger and our maintenance window got smaller, and now if we get into trouble, we can’t restore our databases fast enough.

Step 1: Fill out the HA/DR RPO/RTO worksheet.

In our free First Responder Kit, open up the file “Worksheet – High Availability and Disaster Recovery Planning.pdf.”

Fill out the “Current State” columns based on your HA/DR setup, your backup schedules, and your restore speeds. If you haven’t tested restores or failovers in the last 6-12 months, put “UNKNOWN” across the appropriate areas.

Make sure to read the details carefully (c’mon, it’s a short page) to understand each of the failure modes. For example, folks often tell me, “We have high availability built in because we use VMware or Hyper-V.” No, those are not high availability – they’re just availability. Read those failure modes, and you’ll notice that if some of them occur, it doesn’t matter which host your VM boots up on – it’s still down for the count.

This stuff really does happen. For real-life examples, check out the post 9 Ways to Lose Data.

Step 2: Hand it to your stakeholders & management.

Say, “Here’s our current RPO/RTO. If you want to improve these numbers – and our query performance – tell me what your new goals and budget are.” That’s where the second page of the RPO/RTO worksheet come in – I don’t wanna spoil that one for you, but suffice it to say that I usually print page 1 & page 2 back-to-back on the same sheet of paper.

Yes, the business does default to wanting zero data loss and zero downtime, but they change their minds as soon as they see page 2.

If you stay in the room while they discuss their new goals, they’ll usually ask, “Can you give us more specific costs for these?” Unfortunately, no – that requires building out a project plan, hardware, licensing estimates, etc – and it’s a ton of work. Management picks the box first (which estimates a cost range), and then us geeks get started building a more accurate cost estimate based on the right technical fit for the database, team, and RPO/RTO goals.

Management will also often ask, “What about 15 minutes? Can we do 6 hours? How’s about 12 parsecs?” We can – but let’s keep things simple at first with 0, 1 minute, 1 hour, or 1 day because that’s roughly what SQL Server’s capabilities line up with.

At zero RTO, we need automatic, always-running solutions. SQL Server doesn’t have anything built in that allows for automatic failover in zero time, preserving transactions in flight, so this is a super-expensive category.
At one minute RTO, we need automatic solutions, but we have a few native choices that will fail over automatically when everything’s working properly. (Of course it’s not working properly, and that’s why you’re failing over, but I digress.)
At one hour, we start to get enough time for a meatbag to get the phone call, get to a computer, log in, do some very fast troubleshooting, and perform a well-rehearsed (and documented) process.
At one day, we’ve bought ourselves enough time for that meatbag to sober up, do in-depth troubleshooting, try to bring a server back from the dead, and when it fails, restore databases from scratch.

If they want something in between those times, that’s cool – but we need to think big picture first.

Step 3: With their RPO/RTO goals, sketch out an architecture.

The RPO/RTO goals and rough cost estimates drive the shape of your architecture diagram: the number of boxes, the arrows that connect the boxes together.

Your performance goals drive the size of the boxes.

In the previous posts in this series, I’ve given you sample schedules on what you can do in one hour or one day. Architecture reviews are a lot more ambitious – plus, they usually assume you’ve already done the health check and performance check. (You need to know the workloads before you sketch out the sizes of the boxes.) So for this one, I don’t have an easy button – but to see an example of a deliverable, check out Patient C’s sample findings for our SQL Critical Care®.

Now that you’ve seen all three parts of this series, you have a better feel for what free tools are out there to help – and what you can realistically expect to accomplish given the time that you have. (Toss in lots of servers, and the problem becomes a lot harder to solve today at scale.)

How to Do a Free SQL Server Performance Check

Last Updated November 18, 2017

First Responder Kit, Processes and Practices, sp_BlitzCache, sp_BlitzFirst

Your client or boss just said, “Find out why this SQL Server is slow.” You’ve already done a free SQL Server health check, and now you wanna investigate deeper.

Step 1: Download & run sp_BlitzFirst.

Start with wait stats, which tell you what the SQL Server has been waiting on since the service was last started, or since someone cleared wait stats with DBCC SQLPERF, whichever is more recent.

Go to our download page and get our First Responder Kit. There’s a bunch of scripts and white papers in there, but the one to start with is sp_BlitzFirst.sql. Open that in SSMS, run the script, and it will install sp_BlitzFirst in whatever database you’re in. (I usually put it in the master database, but you don’t have to – it works anywhere.)

Then, run sp_BlitzFirst with these options:

EXEC sp_BlitzFirst @SinceStartup = 1;

1	EXEC sp_BlitzFirst @SinceStartup = 1;

Here’s the first set of results:

We’re going to focus only on this first result set. It returns a lot of other stuff – for info on that, hit the documentation page.

This is a prioritized list of what your SQL Server queries have been waiting on – but it is indeed all queries since startup, which includes jobs like backups, index rebuilds, CHECKDB, etc. In a perfect world, you’d want to log it to a table, and trend it over time. There’s more info on how to do that on the documentation page, but for a quick performance check, we’re just going to start here.

Step 2: Review what your top wait type means.

Start with your top wait type, and work your way down. Here’s some common wait types and how to get more information about ’em:

CXPACKET – parallelism, which isn’t necessarily bad
PAGEIOLATCH% – waiting to read data pages from data files on disk (we didn’t have enough memory to cache them, or we have bad indexes, or bad queries, or slow storage, or all of the above)
SOS_SCHEDULER_YIELD – queries need more CPU time (but doesn’t necessarily mean that the entire server is running 100% CPU, which makes things kinda tricky)
WRITELOG – which is, uh, exactly what it sounds like

There are hundreds of wait types – way beyond what I can do justice to here – but usually you can Google your way through a lot of pages explaining what each wait type means.

Heads up, though: when you’re reading about a wait type, be aware that the page’s author may have an agenda. For example, if you read a page about PAGEIOLATCH written by a storage vendor, their primary goal may be telling you your storage is too slow, and you should invest in faster storage. They might be right – but try to find the queries causing the wait first, and see if you can tune those for free. (Your time, after all, is free. At least that’s what your boss told me.)

Step 3: Find the queries causing that wait type.

Run sp_BlitzCache, another script from our First Responder Kit that analyzes your plan cache to find your most resource-intensive queries. Here’s how:

EXEC sp_BlitzCache @SortOrder = 'cpu';

1	EXEC sp_BlitzCache @SortOrder = 'cpu';

In that example, I’m sorting the plan cache to find the most CPU-intensive queries. You’ll want to use a sort order that matches up with your primary wait type – for example, if you’re waiting on PAGEIOLATCH (reading data pages from the data file), you’ll want to use @SortOrder = ‘reads’.

To get the full list of sort order options, plus all kinds of other stuff like explanations for what each column means, run:

EXEC sp_BlitzCache @Help = 1;

1	EXEC sp_BlitzCache @Help = 1;

Here’s what the default sp_BlitzCache output looks like:

In the Query Type column, check out lines 1 & 2.

Line 1 is “Procedure or Function: GetCommentsDetails” – which means that this stored procedure overall is the top CPU-using query.

Line 2 is “Statement (parent: [dbo].[GetCommentsDetails]) – which means it’s a line inside GetCommentsDetails. This one line is our biggest CPU-using statement overall. If you were going to tune GetCommentsDetails, this is the line in the proc that you want to focus on.

Then, check out the Warnings column. sp_BlitzCache does all kinds of checks in the query plan’s XML to spot common issues like unparameterized queries, missing indexes, joining to user-defined-functions, low-cost-but-high-CPU plans, and much more. They’re not a final verdict – they’re just giving you tips on where to start with your investigation.

Step 4: Write up your analysis.

If you only have an hour, play mad libs:

Our SQL Server’s primary wait type is _____, which means we’re waiting on _____. To fix it, I recommend that we try to tune these 5 queries and the indexes they use:

_____
_____
_____
_____
_____

If you have a day, start doing preliminary investigations into those queries. Are they reasonable queries, things that the SQL Server’s hardware should be able to accomplish in a timely basis? Are they being called an appropriate number of times, or are they victims of an N+1 bug in the app code? Could they be fixed quickly with judicious indexing? Your goal in a day isn’t to fix them, but just to estimate time requirements. (Sometimes you actually can fix ’em – but set expectations, because query result sets need to be tested too.)

If you have a few days, and if you’re not allowed to change the code or indexes or server right away, then write up a more detailed overview. Tie the server’s health together by correlating the hardware performance levels, SQL Server’s top wait types, and the queries causing those waits. While you might be biased that there’s a single root cause – say, the application is garbage or the server is a Speak-n-Spell – there may still be multiple ways to fix it. For examples of that storytelling, check out the sample findings from our SQL Critical Care® process.

Next, learn how to do a SQL Server architecture review.

How to Do a Free SQL Server Health Check

Last Updated November 18, 2017

First Responder Kit, Processes and Practices, sp_Blitz

Your boss or client came to you and said, “Give me a quick health check on this SQL Server.”

Step 1: Download & run sp_Blitz.

Go to our download page and get our First Responder Kit. There’s a bunch of scripts and white papers in there, but the one to start with is sp_Blitz.sql. Open that in SSMS, run the script, and it will install sp_Blitz in whatever database you’re in. (I usually put it in the master database, but you don’t have to – it works anywhere.)

Then, run it with these options:

EXEC sp_Blitz @CheckUserDatabaseObjects = 0, @CheckServerInfo = 1;

1	EXEC sp_Blitz @CheckUserDatabaseObjects = 0, @CheckServerInfo = 1;

These two parameters give you a server-level check without looking inside databases (slowly) for things like heaps and triggers.

Here’s what the output looks like (click to zoom):

The results are prioritized:

Priority 1-50 – these are super important. Jump on ’em right away, lest you lose data or suffer huge performance issues. If you’re doing a health check for a client, you should probably include a quote for this work as part of your health check deliverables.
Priorities 51-150 – solve these later as you get time.
Priorities 151+ – mostly informational, FYI stuff. You may not even want to do anything about these, but knowing that the SQL Server is configured this way may prevent surprises later. You probably don’t want to include these in the health check.

Each check has a URL that you can copy/paste into a web browser for more details about what the warning means, and what work you’ll need to do in order to fix it.

Step 2: Summarize the health risks.

sp_Blitz is written by DBAs, for DBAs. We try to make it as easy to consume as we can, but if you’re going to present the findings to end users or managers, you may need to spend more time explaining the results.

If you only have ten minutes, run sp_Blitz, copy/paste the results into Excel, and then start hiding or deleting the columns or rows that you don’t need management to see. For example, if you’re a consultant, you probably wanna delete the URL column so your clients don’t see our names all over it.

I know what you’re thinking: “But wait, Brent – you can’t be okay with that.” Sure I am! Your client has already hired you to do this, right? No sense in wasting time reinventing the wheel. That’s why we share this stuff as open source to begin with.

If you have an hour or two, add an executive summary in Word that explains:

The risks that scare you the most (like what will happen if the SQL Server dies at 5PM on Friday)
What actions you recommend in order to mitigate those risks (like how to configure better backups or corruption checking)
How much time (or money) it’ll take for you to perform those actions

Your goal is to make it as easy as possible for the reader to say, “Wow, yeah, that scares me too, and we should definitely spend $X right now to get this taken care of.”

If you have a day, build a PowerPoint presentation, set up a meeting with the stakeholders, and then walk them through the findings. To see examples of that, check out the sample findings from our SQL Critical Care®.

And that’s it.

You don’t have to spend money or read a book or watch a video. Just go download sp_Blitz and get started.

Next, learn how to do a free SQL Server performance review.

[Video] Office Hours 2017/10/4 (With Transcriptions)

Last Updated October 7, 2017

SQL Server, Videos

This week, Brent, Tara, and Richie discuss what they think about having no more service packs for SQL Server 2017 and newer versions, shrinking log files, how many servers can one DBA manage, log backups, mirroring vs Availability Groups vs log shipping, and their next company retreat location.

Office Hours Webcast - 2017/10/04

Watch this video on YouTube

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours 10-4-2017

What if I have a really long question…

Brent Ozar: Doug says – Doug has a whole paragraph; my goodness. He says, “We have several SQL Servers running agent jobs and they each record their own history in tables. I’m trying to centralize everything… I don’t see anything that links job steps together in any system table or DMV; is there?” I don’t know; I’ve never tried that.

Tara Kizer: What are we linking with? Job steps to what?

Brent Ozar: I don’t know, job steps through the same job ID?

Tara Kizer: Yeah, I mean that’s in there [crosstalk]

Brent Ozar: I would see – ask what you’re going for on Stack Exchange. Go to DBA.StackExchange and then break out what information you’ve got and the DMV script you’ve got so far. Also, the other thing with Stack Exchange is, as you’re pasting in the question, or as you’re typing in the question, they fill in for you other related questions. And I know I’ve seen a bunch of questions about how to write query for job history in there.

What are your thoughts about no more service packs?

Brent Ozar: John says, “What are y’all’s thoughts about no more service packs for SQL Server 2017 and newer?”

Tara Kizer: Well I think that we’re going to find out in ten years when everybody has upgraded to 2017, finally. What’s the adoption going to be anyway? So I don’t think we’re going to see much of an impact at all? I mean, who’s going to be running 2017?

Brent Ozar: I didn’t think of that until you had said it in the company chat room, and then I went back through and looked today. If we go to – and I’ll fire up a web browser and go pull it up – if we go to SpotlightEssentials.com, Spotlight Essentials is Quest’s free monitoring software, and they post the data that they gather. If you click on Collective IQ up at the top, it shows you all kinds of stuff. Like right now, the current adoption rate for SQL Server 2016 is 9%; 9% out of all the servers that they monitor, and that’s more than a year old now. 2016 is more than a year old, so…

Tara Kizer: And I would bet that most people aren’t running – this is a free tool, right? So I would bet that a lot of people who are running SQL Servers aren’t even using free monitoring tools because a lot of people running SQL Servers don’t even have IT staff, so they wouldn’t even know to look for some free monitoring tools or what to do with these things. So this, probably, is just for people that have IT staff, and it’s probably a small percentage of the SQL Server world.

Brent Ozar: That’s true, because people with money, budget and time and all that, they’re more likely to buy monitoring tools. They’re also, I think, more likely to go early cutting edge on adoption stuff. Yeah, Richie feels the pain of constant upgrades. Richie’s in the midst of upgrading PasteThePlan and had to deal with all kinds of dependencies this week.

Richie Rump: Yeah, it’s interesting, y’all complain about an SP, you know, once every six months or whatever. Meanwhile, I have – PasteThePlan has a good 12 to 15 different packages that it’s using via Node Package Manager and every single one of them need to be updated; every single one of them. So yes, that’s what I did for the upgrade…

Brent Ozar: And it breaks everything. Like upgrading one of those breaks everything else.

Richie Rump: It could. I mean, it definitely could. I got lucky with this one; there wasn’t much breaking. But that’s why you have unit tests, to make sure that when you upgrade, the tests run and everything kind of works. But it gets kind of dicey when you’re just doing blanket updates all over the place.

Brent Ozar: My feelings about no more service packs – I love it, it’s fantastic. Cumulative updates make everything so simple, there’s just one train of updates. Before, people were like, “Should I be on this branch or this branch? Which one’s the most current?” Makes it easier for vendors; they just test their stuff on one path straight through the code. I’m just a huge fan. They’re now coming out monthly, which I’m a huge fan of if the quality is good. So cumulative updates will now come out every month for the first year. Most people don’t install them in the first year, so it’s not heartbreaking. It’s only going to be the cutting-edge people. But every month for a cumulative update – when I read through the list of what gets fixed in some cumulative updates, I think it’s kind of scary. But then you read what gets broken in some CUs, like CU 4 recently broke NOLOCK – I say recently it was about a year ago, 2016 CU 4 broke NOLOCK. I’m like, that’s not the kind of risk you want to take.

Richie Rump: Oh, and there was a bunch of stuff that happened when 2012 was released.

Brent Ozar: Service Pack 1 was a mess.

Tara Kizer: I wonder if this change in policy by Microsoft is going to even delay people further from upgrading because it’s going to be 2017 and all newer versions. So if you work for a company that only will apply Service Packs, maybe they’ll even avoid 2017 or higher for a while.

Richie Rump: Yeah, or maybe just say, “You know what, if it’s been around a year, we’ll install it.” And the one year is our, you know, “We’ll start playing with it then. Until then, we’re just going to let them get the bugs out.”

Brent Ozar: Which makes sense, I mean, if at the one year point it switches from every month cumulative updates to every quarter, so it’s just easier to manage.

Richie Rump: But that’s not us here.

Brent Ozar: Yeah, we’re rough.

Why is my transaction log so big?

Brent Ozar: Chris Fair says, “I ran sp_Blitz and I got the message that my transaction log was larger than my data file. This database is in bulk-logged recovery mode and we only do a log backup once a day at noon…” I love the next sentence that comes out of his mouth. “I’ve been here a month and I don’t know the reasons behind either decision. Backups have been only 1GB in size, yet the log file is 67GB. I know you generally frown upon shrinking log files, but in this case, is it okay? And I’m only going to shrink the log file.”

Tara Kizer: It’s okay, as long as you fix the reason why it’s so big. The reason why it’s so big is because you’re only doing one log backup per day. So fix that, run maybe hourly or even more frequent, and then see what size is needed. So shrink it down. I’d probably shrink it down to 0.5GB, and then with those hourly or more frequent log backups see what size it grows to. It might still grow a bit past your 1GB data file, but maybe you need every five minutes log backups. I’ve had now four clients that have done one log backup per day, and it just is crazy. I don’t even know what the point of this is.

Brent Ozar: If you’re only going to recover to one point in time, that’s what simple model is for, simple recovery model. If it’s like a data warehouse where you’re only loading stuff once per day, yeah, maybe simple recovery model is okay. But odds are, Chris, they brought you in because the last person was either incompetent or there was no last person. So good news, you’re asking very good questions. I bet money, the last person didn’t even know about sp_Blitz toop, because that happens all the time. Even when we come onsite with customers, sometimes they’ll be like, “sp_Blitz, tell me more…” [crosstalk]

I have over a thousand SQL Server 2008s…

Brent Ozar: Chris says, “No question – here’s my sadness. I have 39 2008 SP3 servers, I have 1326 2008 SP4 servers…” And then goes on with the rest of his version numbers. But you have enough time to sit here and watch webcasts when you’ve got over 2000 SQL Servers. You can’t be that bad off.

Tara Kizer: I imagine he’s working for a large company, and I would hope there’s a large DBA team for this amount of servers. The most I’ve had at a company was 700 servers, but we had five people at one point, I think at the lowest, and then eight people near when I left.

Brent Ozar: We got this blog post that I did a while back: How Many Servers Can One Person Manage? Where I talked about – I’m going to show a little graphic up here on the screen. If all you manage is hardware, so if you work for Facebook, Amazon, Microsoft, Google, whoever, if all you manage is hardware, you’re running through racks and data centers, one person can manage thousands of servers. When you layer on an OS, Windows, Linux, whatever, as soon as you layer on the OS and you’re responsible for installation, patching, security, configuration and requests from end users, one person can typically manage hundreds of boxes. When you layer on SQL Server, or really any database, one person can effectively manage 50 to 100 instances of SQL Server.

But you’ll notice, as you get to this layer you’re often giving up the previous layers. If you’re managing SQL Server and you want to manage, say 100, instances by yourself or per DBA, you’re probably giving up hardware, maybe even OS, to other teams, and then one person can effectively manage more instances. When you layer on high availability and disaster recover, clustering, replication, log shipping, always on availability groups, one person can really manage 10 to 50 instances of SQL Server. And then when you layer on performance, one person can effectively manage between one and five applications. This is where it gets a little weird. Sometimes, if you’re kCura Relativity DBA, one kCura relativity DBA may be able to manage performance across 50 SQL Servers, because they’re all running the same app. Performance tuning is exactly the same across all of them. But it just lays out a good idea of how many servers one person can manage. Sometimes when I see that, people will go, “Trust me, I manage more than that.” Well yeah, but you do a crappy job; I looked at your servers…

Tara Kizer: And Christopher followed up, they do have one DBA guy. Christopher’s not a DBA guy, he’s a developer. One DBA guy that also handles the hardware – he’s also the sys.admin. So I can’t even imagine what a hot mess these servers are in if there’s this many SQL Servers.

Brent Ozar: And imagine having a failover from one data center to another.

Tara Kizer: They don’t even have that in place – or it’s some kind of VM snapshot or something.

Updates just introduce more problems

Brent Ozar: Raul points out, “Updates sometimes just introduce more problems and complexity.” Yeah, I’d agree with that. I mean, a lot of times, you look at a lot of the cumulative updates, they’re fixing amazing stuff, don’t get me wrong, I’m really happy with what Microsoft’s doing out there with the product.

Richie Rump: That’s one of the things I like about sp_Blitz, is it will tell you if you’re running a dangerous build. If you apply one of those patches or whatever and it has a problem, running sp_Blitz will tell you you’re running something that’s bad; you probably should upgrade.

Brent Ozar: We even warn you if you’re on 2016 and you’re not on Service Pack 1, if you’re in Standard Edition, we’re like, “Yo, dog, get up to 2016 Service Pack 1, it’s amazing.”

Should we be in simple or full recovery model?

Brent Ozar: Steve asks, “We back up databases and logs every hour. Is there any reason for us to be in full recovery model?”

Tara Kizer: Is this the one that was in simple recovery model, same one? No?

Brent Ozar: No, it’s a different guy.

Tara Kizer: I mean, if you’re doing hourly log backups you need to be in bulk-logged or full recovery model in order to do that. You can’t do log backups in simple, so which recovery model you’re going to pick depends upon the business. It’s not for us to decide. I mean, does the business require you to do point in time recovery to, say, data loss RPO goal of say five minutes. You need full recovery model. I don’t even like bulk-logged recovery model on critical databases. I mean, it reduces my recovery point, so why would I want that?

Brent Ozar: I know what it is, and I’ve been through certification tests and I’ve seen what the purpose of it is, I’ve just never actually used it. I know there’s somebody out there who makes good sense of it, but it just has never been …

Tara threw out there the letters RPO and RTO; we’ve got a blog post on that – The Nine Letters That Get DBAs Fired. So we talk about what RPO and RTO is, we talk about how to CYA using our disaster recovery spreadsheet, where you check out what your RPO and RTO are and hand that to the business to that everybody’s on the same page. That’s our post, The Nine Letters That Get DBAs Fired.

Tara Kizer: My client this week, as well as last week, their RPO and RTO goals have been one day across the board for all eight boxes. It’s like wahoo, it’s so easy. [crosstalk] At five minutes it gets a little challenging. How much money do you have?

Brent Ozar: Everybody, usually when we show them that spreadsheet is like, “I don’t want to lose data and I don’t ever want to go down.” What’s your budget? “$5000.”

What’s the right HA/DR technology for me?

Brent Ozar: Raul asks, “If your organization has the means and budget, their RTO, recovery time objective, is 45 minutes. Would you go for AGs or mirroring?” I know what Tara’s answer is.

Tara Kizer: Well yes, I’d go with availability groups, just because it has more flexibility and is not deprecated. But as far as AG or mirroring, I don’t care one way or the other for the specific question. I want AGs if I want multiple replicas, because with mirroring you only get one mirror, and you have to pick asynchronous or synchronous; you can’t have both. So availability groups give me HA and DR and the option to have reporting as well. So, either one is fine. As far as a 45-minute RTO goal, that’s fine. [crosstalk]

Just go for log shipping at that point. I’m a fan of log shipping, but it’s harder to failover. So if your organization is going to be testing failovers, that is why we moved away from it at my organization. We moved to mirroring and availability groups because we did failover during disaster recovery sites and ran production out of there on purpose. And so we were trying to reduce our downtime for these failovers, and log shipping’s a lot harder to do.

Brent Ozar: It is. In that HA and DR worksheet that’s – on The Nine Letters That Get DBAs Fired, it’s got this three-part worksheet and down the left-hand side you find how much data you’re willing to lose, and then across the top is how long you’re willing to be down for. 45 minutes, you’re basically in the one-hour category, and if you go down through there, the methods are very different depending on how much data you’re willing to lose. If they’re not willing to lose any then it’s kind of tougher. You’re really down to either synchronous AGs or synchronous mirroring, and even those have some gotchas there. But if you’re willing to lose, say an hour, maybe if you’re willing to be down 45 minutes, maybe you’re willing to lose half an hour, an hour’s worth of data, log shipping can be pretty cool.

You mentioned that failover has to be automatic and seamless to the clients. There’s a thing called a DNS CNAME that you can use to point around the server from one place to another; it’s just way more work than availability groups. I say way more work, that’s probably a stretch.

Tara Kizer: Also, client redirection needs to transparent, that means on failover the user can keep working and doesn’t see any interruption, and neither availability groups nor mirroring give you that, or anything really.

Brent Ozar: I read that as like we can’t change the client app, but it’s going to be down for 45 minutes. It is not going to be transparent, they’re going to see right through your 45-minute delay.

Brent Ozar: Raul says – oh come on Raul… He follows up with, “Log shipping requires a lot of DBA intervention plus failover testing.”

Tara Kizer: So do the others.

Brent Ozar: If you’re saying that you think you don’t need testing for AGs or mirroring, think again, they are kind of tricky. Alright, well that’s actually all the questions that we have, folks. Y’all got done in 16 minutes. I’ll give you guys another minute or two to throw out any questions you’ve got there, otherwise, we’ll let you go early to lunch today.

Richie Rump: I realize that I have an ampersand on my shirt and so do you. It’s weird. It’s like ampersand day.

Tara Kizer: I do not.

Richie Rump: You didn’t read the memo.

Brent Ozar: Tara’s the only one in the group with taste.

Tara Kizer: Actually, this is my pajama top, so I don’t know about that…

Brent Ozar: Somebody asked when I was in the Isle of Man last week, they were like, “Do you guys actually get ready for work and get dressed?” I was like, “Well we have a webcam and we work with clients on webcam, so we have to put shirts on at least.”

Tara Kizer: It was funny when we were out in Austin to work with Dell, the Quest people now, and we all dressed in whatever we wanted to wear and a lot of the people at Dell were a little bit fancier and Claudia one look at my attire and said, “I wish I could dress like that here.” I don’t know if she was insinuating…

Brent Ozar: I wish you could dress like that here too.

Is it possible to roll back sp_changepassword?

Brent Ozar: J.H. asks, “Is it possible to rollback sp_changepassword?” No, not that I know of.

Tara Kizer: Rollback if he wrote down the password somewhere, the old one.

Brent Ozar: Michael asks, “What’s a good way to filter bad data in a big ETL job? Someone fat-fingered a date of January the first of 2107 instead of 2017.”

Tara Kizer: Well, data validation, I guess, it’s probably part of your packaging.

Brent Ozar: Richie, do you still have that sp data profile?

Richie Rump: Yeah, I haven’t touched it in a while, but that won’t tell you – it will tell you the min and max of some stuff…

Brent Ozar: Just that you have wacko edge case data.

Richie Rump: Yeah.

Brent Ozar: Alright, well thanks everybody for hanging out this week and we will see you next week on Office Hours. Adios everybody.

[Video] Office Hours 2017/09/13 (With Transcriptions)

Last Updated October 7, 2017

SQL Server, Videos

Note: this is coming to you from the past due to technical difficulties.

This week, Brent and Erik discuss Availability Group issues, database diagram tools, AWS vs Azure, diagnosing async io waits, troubleshooting deadlocks, extended events, using same login credentials for everyone in the company, adjusting and more.

First Responder Kit, Processes and Practices

Office Hours Webcast - 2017/09/13

Watch this video on YouTube

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast 9-13-17

Why is my AG database log file growing?

Brent Ozar: Leah asks, “I need help; I am out of ideas…” Boy, are we the right people to ask. She says, “I have multiple availability groups using SQL Server 2016. Every database is in its own availability group. However, I have one database that has a full transaction log file and it’s growing. It says the log reuse wait description says availability replica, but all replicas show everything is healthy. What can I do, other than remove AGs or throwing the database in simple recovery model?”

Erik Darling: Kendra has a great post. Kendra, about why your availability group transaction log can be full without you having messed anything up.

Brent Ozar: Let’s see if we can find it. Let’s see, Kendra Little transaction log – is it on her site or ours?

Erik Darling: No, it’s on hers.

Brent Ozar: LittleKendra.com… For those of you listening to the podcast, you can’t see that we’re typing. Why is my transaction log growing in my availability group… Oh, she has an episode on it; fantastic, on her IDERA SQL DBA podcast. Oh, it’s nice. She doesn’t transcribe the whole thing, but you can either get it in video or in audio. She does a great job on the podcasts there. You should also totally subscribe to her podcast while you’re there too.

Off the top of my head, I can’t remember what would cause that. Obviously, a long-running transaction, but that would be different, it wouldn’t show availability group in the list there.

Erik Darling: Stuff like running out of disk space on the replica – like if your transaction log is full over there and it can’t grow anymore to receive new transactions, it’ll be stuck. And then – I had another good one – like if stuff gets – it can’t clear out row versioning or – no, that was tempdb…

Brent Ozer: I was thinking, if you added a file on an invalid drive letter, you know, where the secondary doesn’t have it, then the replication will stop working. I would also check and see, sometimes you think that things are healthy when you look at the availability group dashboard, but it’s not; because the availability group dashboard stops updating when you run into worker thread exhaustion. You might actually not be in sync over on the other side. If this is a Standard Edition AG, you’re not supposed to query the secondaries; you know, it’s not readable on the secondaries. If this is in Enterprise though, I would actually go in and look at the tables on the secondary, just to make sure the data’s actually up to date, because it may not be.

What’s a good database diagram tool?

Brent Ozar: James says, “I have a 1.2TB database and I need a database diagram. Do you recommend any good third-party tools?”

Erik Darling: Oh, this is a Richie question, because Richie actually knows several of these. I can’t remember any off the top of my head that I like.

Brent Ozar: I hear people say Erwin, ER/Studio, Toad Data Modeler. Those are the three that I hear the most often. I don’t know anything about pros and cons to any of them, but I know they’re expensive; usually like three, four, five thousand bucks.

Erik Darling: I mean, James, not to punt again, if you search back through our episodes about database diagrams, there’s specifically one where Richie talked about several different that he had used over the years that he had various experiences with. So, I would search back through Office Hours episodes and see if you can find searching for database diagrams because Richie had a great answer about them.

Brent Ozar: Or – so this isn’t a good one to post on Stack Exchange, because they’re like never post-shopping list questions; but the person I’d ask is Lewis Davidson. Lewis Davidson has a blog over on SQLBlog.com. If you search for SQLBlog Lewis Davidson, I bet his contact info is over there; and he wrote the book on relational data modeling for SQL Server. Nice guy, has an unbelievable sense of humor. Super dry, like he’s talking and you don’t even realize the number of jokes he’s dropping in there.

Should I use AWS or Azure?

Brent Ozar: Next up, Brent – not me but the other Brent; I shouldn’t use last names – says his, “Company has mandated that we’re moving to the cloud. Now they’re counting on me to come up with which service to use; AWS or Azure. What should I consider?” Oh, that’s good.

So, you have to work with all of them because we go through and do things like builder days. What do you think about – if you were going to go pick one, what were the kinds of things you’d think about?

Erik Darling: Oh, which sales person can fight the best. No, but I think what you should do and the way you should decide which cloud service you want to pick is you should go to the Azure portal and try to figure it out.

Brent Ozar: That is just mean.

Erik Darling; I know, but the good thing is you get a quick answer out of that.

Brent Ozar: Yeah, come up with a list of like four or five things that you need to do; spin up a new VM, take a snapshot backup of it – I’m trying to think of the other common things that I would do. Networking security, holy cow. Spin up two VMs in two different areas of the US, or internationally, wherever you want to do it. One on the East Coast, one on the West, and then get them to talk to each other, ideally without putting them both on the internet. Holy cow, that’s a pain in the rear…

Erik Darling: I hope you like subnets.

Brent Ozar: And I don’t want to sound like I’m shilling for anybody, but this is one of the things actually that Google does really well. Any of your VMs can talk to any of your other VMs, regardless of what data center they’re in. You don’t have to deal with any of the complex VPN stuff be default. The other thing I would ask of your developers is, are there any services that are ion one cloud that you’re thinking about using that are not in other clouds?

So, for example, Microsoft will try to pull you in with stuff like Azure Data Warehouse or Azure Data Lakes that are proprietary to them. Amazon will try to pull you in with things like Redshift, S3, you know, services that they use that other people don’t. Google will try to pull you in with their Kubernetes type stuff, but just ask with your developers to see which ones they use.

Erik Darling: Another thing I’d want to do is try to find instance sizes that are sort of commensurate to the machines that I have running now and check out pricing there and see how – just see what’s available that you could probably get a close match on. Because one thing that you’re going to find in the cloud – Brent talked about networking, I’m just going to talk about networking speed. For the instance sizes you want, it might make sense for the – they might be a close match to the kind of machines you’re running now. The networking and the disks that you get might not be all that hot.

Brent Ozar: When you go through and look at pricing in comparison too, I would start here; EC2instances.info. Now this is only for Amazon, obviously. There’s similar kinds of sites for Google and Azure. I think it’s easier to find pricing for either Google or Azure. For example, with Google, there’s no predefined machine types that really hone you in. you get a lot more knobs and buttons that you can play with for sizing. But in here, I love this EC2 instances comparison, where you can choose what kind of licensing you want, whether or not you’re worried about how many compute units you get, whether or not you’re doing IPB6; all kinds of fun stuff. And then you can sort by that. So you can say, I want VMs with at least 60GB of RAM and at least eight cores, and then I can sort by pricing; how much is Windows pricing on demand? All kinds of neat stuff.

Erik Darling: But what’s really important to pay attention to is that network performance column, because not all of those are so great. So like up to 10GB, high, doesn’t even do all that high. And then you have moderate and low… Be careful.

Brent Ozar: They really are slow. They are desperately slow; same thing with storage throughput. If you go to columns and, say, EBS optimized throughput, you can see a lot of these are just dog slow. If you run CrystalDiskMark and compare it to some of these, man, your data would be better off on a USB thumb drive.

Erik Darling: If you need really large sizes and you’re deciding to go with Azure then you might want to hold out for Azure Managed Instances, because those are pretty beefy sizes and…

Brent Ozar: It’s wonderful; so Azure SQLDB, it doesn’t sound like you’re interested in, but Azure Managed Instances – so what are the kinds of things that would draw you to managed instances that SQLDB doesn’t have?

Erik Darling: Just size and some of the AG stuff I guess.

Brent Ozar: Cross database queries is a big one.

Where can I see past GroupBy sessions?

Brent Ozar: Kevin asks, “I couldn’t watch all of the GroupBy sessions, where can I see them now, please?” I like that he asked please at the end of it. Go to GroupBy.org and there’s a big thing up at the top that says “Watch past sessions”. That’s exactly where you should click. All totally free too.

Should I have different SIDs in development?

Brent Ozar: Amy says, “Is it okay to have the same login SID for databases that are on different SQL Servers; production, backup, test and dev?” Oh wow, I never thought about that.

Erik Darling: Me either…

Brent Ozar: Yeah, it’ll be fine. After all, you’re going to probably restore – oh god, I’m going to get in so much trouble for saying this – what you’re probably going to do in many environments is you’re going to restore from production over to other places like test and dev. And then you get stabbed by some security auditor who goes, “Developers should never have production data.” This is the way the world works.

Erik darling: It’s the agile way.

Brent Ozar: Amy says, “Yes I am, thank you.” I figured someone would stab me for that.

How should I diagnose ASYNC_NETWORK_IO?

Brent Ozar: Samuel asks, “What are the next steps in diagnosing async network I/O waits when I’ve confirmed that networking is not the issue?”

Erik Darling: Doesn’t it sound like it should be the network?

Brent Ozar: Yes – so many [crosstalk]

Erik Darling: Oh, that’s good. I would start with checking your SQL Server code and see if you’re running any scalar functions or cursors in there that might be sending results to your application one row at a time. The next thing I would check is your actual application code and see if they’re doing any processing on a dataset that comes in, in like a four-reach loop; where they’ll take a row and do some processing and then another row and do some processing. Because typically what async network I/O is, is when SQL is basically shooting data at a client and the client’s not saying that it has all the data because it’s doing a bunch of stuff client side.

Brent Ozar: You could run sp_whoisactive. Go run sp_whoisactive and look at that wait stat column. In there, it will show which queries are waiting for async network I/O; and then you can go back to those developers too. It shows you – I love sp_whoisactive so much. It shows you which machine it’s coming from, all kinds of fun stuff.

Should I hint my isolation level?

Brent Ozar: Next up, J.H. says, “A developer was experiencing deadlocks and I recommended to do their changes, inserts updates and deletes, in smaller batches. Now that did turn out helping, but we’re still experiencing some. Do you recommend locking isolation hints?”

Erik Darling: Not necessarily.

Brent Ozar: What would you start with first if you were facing deadlocks?

Erik Darling: Well, I think it would depend on where the deadlocks are coming from, a bit. Usually if it’s a couple of stored procs, I would go and look at those, and I would maybe look for missing index requests, and I would maybe look at smarter ways to index for updates. Because a lot of – a reason that I come across that happens quite a bit for when deadlocks occur is that there’s an update or a delete. And there’s a where clause for it, but the where clause doesn’t have an appropriate index. So, SQL ends up escalating all sorts of locks from row to page locks or to table locks, and you end up with these kinds of strict locks that get held for the duration of the modification, and then anything else trying to do work around it kind of gets bonked. So, I would just make sure, for my modification queries, updates and deletes, there’s a where clause that I’m kind of appropriately indexed for that.

Brent Ozar: I’d also say too, make sure that you don’t have too many indexes. A shop I was at yesterday had 60 to 70 indexes per table. And so whenever someone wanted to change data, we were having to grab locks across all kinds of indexes because every index included all kinds of fields from the table, fields that changed all the time; so you had to do way more locks than necessary. It usually seems like – I often joke with people that you should aim for five or less indexes per table and five or less fields per index. It’s not that that’s a magic number, it’s just that the more indexes you start to have, the more problems you end up with; stuff like blocking and deadlocks. And the less indexes that you have, the more you run into that problem too.

Erik Darling: So, one really good and quick way to get yourself a diagnosis on this is during or after – probably after one of those deadlocking things occurs, run sp_BlitzIndex and see if you get warnings for aggressive indexes. Aggressive indexes will point out indexes that have had significant row or page locks and how long they’ve had them for. So, you can figure out which indexes have these long locks being held on them and which ones might be the reason for the deadlocks occurring.

Brent Ozar: I remember the first time Kendra showed me that you could get locking stuff out of the index DMVs. And I was just like, witchcraft, how is this even possible? I used to try and focus too much on what are the queries involved in the blocking and the deadlocking, but man, you don’t even need it once you know which indexes are involved. Then it’s easier to look at the tables and see, how’s our indexing strategy on those?

Can you show me Extended Events?

(Note: you’ll need to watch the YouTube recording to see the demo in action.)

Brent Ozar: Don says, “Good morning. I tend to use SQL Profiler to do an audit on queries or find the queries that are getting called from a web page. The developers won’t tell me and I’ve got to try to figure it out. With SQL Profiler being deprecated, is there a specific extended event that I can use to do this? Or how should I use the new way?”

Erik Darling: Don, I have such good news for you. Assuming that you’re on a fairly modern version of SQL Server and using a fairly modern version of SSMS, when you go into extended events and you go into sessions – when you right click on sessions and hit new session, one of your options under the general tab is you can use a template. And there are a whole bunch of templates in there that are equivalent to Profiler. So, whatever you would normally fire up for Profiler, you can probably find a pretty close match for Profiler equivalence. So, you can do anything that you have in there, and then it gives you the definition of the extended events session once you go onto other parts of it; and you can see which events are tracked. And so, you could even narrow stuff down and pull stuff out and concentrate on stuff that you’re familiar with in Profiler.

So that’s what I would recommend. If you’re using Profiler to do something specific now like that, start with the templates there because they make your life a lot easier. You can get a lot of commensurate information up front from across those.

Brent Ozar: Session name – I’m like why can’t I do this demo? And then you can hit the script up thing…

Erik Darling: Don’t be afraid to hit the configure button and kind of poke through, because there’s going to be a whole bunch of extra columns and filters and stuff that you might want to – I have a car being stolen behind me – but there’s all sorts of stuff that you might want to pull out or add into this session that you saw on Profiler that you’re not seeing just off the bat in the [inaudible].

Brent Ozar: I mean, watched live data isn’t always completely accurate, it doesn’t show things exactly when it comes in…

Erik Darling: Sometimes it crashes management studio.

Brent Ozar: So, there’s a lot of gotchas with it. It just is nowhere near as bad as it used to be. The other thing I’ll say too is, if you don’t want to learn it, you don’t have to. Like you can still continue to use Profiler. It’s heavier, it isn’t nearly as cool and won’t gather all kinds of cool stuff; but it still works if you only need to use it part time, like once a month or once a quarter, it still works fine, and it’s still in SQL Server 2017.

Erik Darling: Go back to management studio real quick.

Brent Ozar: Oh man, you’re not my real dad.

Erik Darling: Come on, we’ve got so much time to kill. So, if you want to get results and watch live data or end the data file faster, just turn max disk patch latency down to a lower number. Right now, it’s at 30 seconds, so it could take 30 seconds for stuff to go in. you could change that to one or two seconds, which is what I do sometimes in demos to get the data into the file faster; which isn’t necessarily a good idea on a production server, but if you just want to see the data faster, that’s a knob you can tune to see it faster.

Brent Ozar: There’s also, as long as we’re going through this, allow event loss. So, you might look, as you’re going through the wizard, oh I shouldn’t allow any loss of events whatsoever; no, don’t do that. Let SQL Server drop some events because it’s not mission critical and it will be faster that way.

Everybody knows the SA password. What do I do?

Brent Ozar: M.M. says – I assume that’s not Eminem. Times are hard in the rap business…

Erik Darling: I heard that new album, man…

Brent Ozar: He goes, alright I give up, I’m going to go into databases instead. “The company I’m working for uses the exact same ID and password for…” Oh, now I see why you went anonymous with your name. “Uses the exact same ID and password for the SQL proxy, command shell credential, owner for all jobs. All the developers know the password, my developers work in production. They log in SSMS as that ID in order to create jobs. I think this is bad and I want to change it. Is it okay for the proxy and credential to be the same?” Man, that would be the least of my worries.

Erik Darling: As long as all of management is aware of it and they are okay with it, I mean – maybe let’s not bark up that tree. If you wanted to, you know, write them a nice email, maybe have a brown bag meeting where you talk about all the potential security problems that presents. Being a DBA and being a developer and handling data like this is a trust position. So, if you don’t necessarily trust everyone accessing the environment, you may not want them to have access to all of your customer data or whatever data it is coming in. If it’s medical data especially, that could be rough. But I would make sure that everyone is aware of it and aware of the potential downsides of it. One thing that I will caution against though is shooting off the handle and changing a password, because you could make a bunch of stuff break like that.

Brent Ozar: One thing I’ll throw in there, I have up on the screen this old blog post I wrote – I say old, I didn’t realize it was only a year and a half ago. How to talk people out of the SA account. I give a graceful way to do it and then in option two I give the really ugly way to do it. It involves, after all, if everyone knows the SA password, or whatever password, you could go in and change things in the production database and play stupid. “I don’t know who dropped that table, it could have been anybody. Man, it’s a shame we can’t figure it out, but with all you knowing the password, I guess there’s just no way we’ll ever know.” “Well you’ve got to stop people from doing that.” “I can’t, my hands are tied. I’ll see you again next week. I predict we’ll have an outage on Thursday at two.”

Erik Darling: All your queries are going to run really funny in about five minutes. You know what he could do? That stored procedure that we wrote at the last DBA Days, Chaos Slop. Change his settings.

Brent Ozar: Oh god, that was funny. Describe what that does.

Erik Darling: So, the nice way to put it is that at random it will – if you schedule it as an agent job, every time it runs it will assign random values to a whole bunch of different, important to performance, variables. Like max memory, cost threshold for parallelism, MAXDOP – so you could just get wild variances in execution plans and, you know, querying a plan cache. All sorts of bad stuff happens when you start changing that stuff on the fly. And Brent left a process running overnight while Chaos Slow was also running. So just the wait stats were awful, it felt like me on the cruise ship where I was just… It was inspired by the Amazon Chaos Monkey thing that randomly goes and breaks things.

Brent Ozar: It’s amazing Dell never let us into their data center. That was pretty spectacular.

What’s your favorite data visualization tool?

Brent Ozar: Wes asks, “Have you ever used any data visualization tools and if so what are your favorites?”

Erik Darling: Excel…

Brent Ozar: That’s exactly what I was going to say. It’s easy, it’s cheap and it produces results that I can manage. But I don’t know anything about visualization beyond that though. And if you’ve ever seen any of my reports, you’ll understand why. “You can see by this 3D pie chart filled with Pac Men…

How do I fail over to DR with AGs?

Brent Ozar: Sri says, “In a multi-site Always On Availability Group…” I like how you spelled it out absolutely perfectly there, SRI, you’re already ahead of most shops. “I have an even number of nodes. I’ve got four nodes and I’ve got a quorum file share in my primary data center. What happens when my primary data center dies? Like if I have a flood, a disaster? Will we be able to manually bring up our DR site?”

Erik Darling: take it away, Brent.

Brent Ozar: Alright, so assuming – it’s easier almost, with the camera with my number of fingers. So, I have two nodes in each data center and then I also have the extra file share witness over in my primary data center. When you lose the majority of voters all at once, the two that are still remaining over in the DR data center, they don’t know that they aren’t just left alone in the cold. Like maybe their internet connection died and they can’t see the majority; so, they will gracefully shut down their cluster services. Now, you can force them online. There’s a set of steps that are documented in Books Online about forcing the cluster online, fixing quorum, then forcing SQL Server to start the AG with allowed data losses on. But this is super dangerous and the checklist to do this is like a page and a half long in Books Online, and every one of the lines links off to other articles in Books Online. It is something that you want to rehearse the Bajeesus out of before you go and do it the first time.

The thing that really worried me when folks do this is, whenever you failover to that other data center, when you decide to pull the trigger, there is no easy way to get the data back from that other data center. The simple way to think about it is order IDs. Say you’ve got a table with an identity field, starts at one and goes up to a bajillion. In your primary data center, y9ou had order ID number 100, and that one hadn’t made it over to DR yet. Well, DR was only up at, say, order ID 90. When we failover and we start inserting into the orders table, we’re going to be using that order ID 100 again, and I have to be able to figure out are these two different orders or are they the same order where fields have been changed? Tools like Redgate’s data compare and schema compare will help you identify that there’s differences, but not which one is right or whether it’s two separate rows or one row. So, it’s super, super dangerous. It’s something that you actually want to step through as a company before you go live and understand what the ramifications are.

Erik Darling: See that was a much better answer than what I was going to give. I was going to say maybe you should think about log shipping.

Brent Ozar: Yeah, so much easier. Because you lose the primary data center, the first think you have to do when you go to failover to DR is fix the cluster. Before you can even get started you have to fix the cluster to bring that online. With log shipping, who cares? You just go live over on the other side.

Erik Darling: This is part of the reason why I have such a hard time finding HA and DR stuff sexy, is because it’s like nope, everything is lost. It’s like a dude saying he’s not wearing any underwear; you’re like yeah, who cares?

Brent Ozar: It’s amazing how I look at it and I go, if you do it wrong, it’s way less reliable instead of more reliable. It’s very easy to get less reliable. Now either I just lost internet or Erik did, one of the two. Let me see – yeah, I’m still pulling up internet pages, so it must just be Erik’s frozen in time.

Let’s see, SRI says thank you and Vlad says you are visible. Okay, cool.

How do I catch people using the DAC?

Brent Ozar: So Samuel says, “I have an issue of a process logging into my SQL Server using the DAC overnight, the dedicated admin connection. Can I run a trace, like an extended events or profiler query to capture this user? You actually don’t need to run a trace or an extended events type thing. The easy way to do it is, just log sp_whoisactive to a table. If you Google for – we’ll go ahead and do it real quick on ours – if you search for log sp_whoisactive to table, Tara Kizer has a great post explaining how you go about doing this. there’s two on here. Part two is the better one that goes – I say better one, it’s just newer. We go into more details there. So, you can set this up as a job to run every five minutes, every ten minutes, however often that you want. Then there’s also a parameter that you may want to play around with, show sleeping SPIDs, I believe it is.

Let’s go fire it open just to see if I’ve got this right – sp_whoisactive. Show sleeping SPIDs I think, equals one. Look at that, I even remember. So, this will catch even if someone just logs into the DAC and doesn’t do anything. If you wanted to get more specific about which query is which, like which query is using the DAC, you could go into more detail through things like sys.dm_exec_sessions. But I would just do this because it’s a great technique to understand how to use for all kinds of reasons. I know a lot of shops – a lot is probably a stretch. I know some shops who will set up a job to log sp_whoisactive to a table, they’ll set up an agent job to do it and schedule, like every minute. And they’ll only enable it when they know they’re going to have or they’re having problems; they’ll teach their helpdesk staff to go enable that job.

So, since you know roughly when that’s happening, you could simply schedule the job to run during those windows, and you’ll be able to catch the culprit pretty quickly.

Are there drawbacks to changing MAXDOP?

Brent Ozar: Finally, the last question we’ll do today, J.H. says, “I recall seeing a YouTube clip of Grant Fritchey saying maybe not always change the MAXDOP is the default. Is there a time when it changes?” Yes, absolutely. So first off I’m going to throw a link up here on the page so you all can see it. If you go to BrentOzar.com/go/cxpacket – and I’ll copy paste that so I can paste that in again whenever the page refreshes… I’ve got a video on there where I explain what’s going on with MAXDOP, what’s going on with cost threshold. And I haven’t seen that clip from Grant, but I can tell you, because I can probably agree with it, there are probably cases where you don’t want to throttle down how many cores your query gets. However, the gotcha with that is it’s usually talking about fairly small servers. So, think like eight to 12 cores as opposed to 16, 32, 64 cores. it’s extremely rare that on a 16-core server or a 32-core server that you actually want one query going parallel across all of those threads. Because remember, it’s not like just one query is going to go parallel. Odds are you’re going to have a bunch of queries going parallel and it can cause a problem.

The typical scenarios where you want to go with a larger MAXDOP are things like data warehouses, where you have a really repeatable load every day and then you have repeatable nightly scheduled reports that come in and do big huge awful ugly queries. What’s cool about data warehouses is you can change your MAXDOP over time, say run experiments from one week to the next to figure out where your sweet spot on MAXDOP is. This is one of the experiments we ran at Dell DBA Days. Both Erik and Doug had run experiments on this. One of them was an experiment on CHECKDB MAXDOP, for example, where we found that the sweet spot for some workloads or some databases was closer to 14 or 18 in terms of MAXDOP.

Alright, well that sums up today’s office hours. Thanks everybody for hanging out. If you have a question that we didn’t get to, feel free to go over to DBA.StackExchange.com. We’re over there all the time posting answers on that, and we’ll see you guys over there. See you all next week at Office Hours.

Getting Help with a Slow Server

Last Updated March 8, 2018

Tara Kizer

StackExchange users often ask, “Why is my server slow?” Here’s a few tips to get better, faster answers.

Server information

You may have already included server information in your question, but it may not be sufficient. Run sp_Blitz to output a lot of server information fast.

EXEC sp_Blitz @OutputType = ‘markdown’, @CheckServerInfo = 1

1	EXEC sp_Blitz @OutputType = ‘markdown’, @CheckServerInfo = 1

Click on the XML mumbo jumbo and post the output to your question. Here’s an example of a question with the sp_Blitz markdown included. This is the bare minimum of stuff we need to give you a decent answer – but keep reading, putting in more info in, and we’ll be able to give you a great answer.

What is SQL Server waiting on?

As a performance tuner, I start my analysis with the wait stats. If I’m going to help answer why your server is slow, I need to know what SQL Server is waiting on.

I want to know what the waits are since startup and what the waits are right now. That information gives me a good picture of the server.

You can use sp_BlitzFirst to get that data.

To get the waits since startup, run:

EXEC sp_BlitzFirst @SinceStartup = 1

1	EXEC sp_BlitzFirst @SinceStartup = 1

Screenshot the first result set and add it to your question.

If you are clearing out your wait stats, the @SinceStartup data is not going to be good. Please don’t clear out your wait stats! If you are clearing out your wait stats, tell us how many hours ago the last clear was. We always need to know what our sample size is when looking at the waits.

To get the waits right now, run:

EXEC sp_BlitzFirst @ExpertMode = 1, @Seconds = 30

1	EXEC sp_BlitzFirst @ExpertMode = 1, @Seconds = 30

Screenshot the third result set (wait stats) and add it to your question. It would be beneficial to also screenshot the other result sets, but for now we need to know the waits.

You can use other values for @Seconds. 30 seconds gives a good picture if the performance issue is happening right now.

If the top wait is ASYNC_NETWORK_IO, you can stop here. You have your answer. It is not a SQL Server problem. It’s almost never a network problem even though the wait stat name makes you think it is. It’s sometimes a bottleneck on the application server, but it’s usually an application bottleneck. Applications that do row-by-row processing while SQL Server is sending the data cause ASYNC_NETWORK_IO waits. SQL Server is sending the data fast, but the application is telling SQL Server to stop while it processes what it has been sent so far. While SQL Server is waiting to be told to send more data, it’s recording waiting time on ASYNC_NETWORK_IO. To fix it, modify the application so that it consumes all of the data from SQL Server and THEN does its row-by-row processing.

If the top wait is PAGEIOLATCH_SH, run sp_BlitzIndex @Mode = 0 as it is indicative of memory pressure which can often be fixed by adding high-value missing indexes. Screenshot the “Indexaphobia: High value missing index with High Impact section”. You can hide your object and database names. We need to see the Details, Usage and Size columns. You can hide your object names in the Details column to protect your company.

For all other waits, I dig into the other result sets from the second sp_BlitzFirst call (waits right now).

Let sp_BlitzFirst help us help you

When you run it with @ExpertMode = 1:

Full output of sp_BlitzFirst @ExpertMode = 1 (click to zoom)

The first result set shows current activity at the very start of that sample. Any long-running queries in there? Any non-NULL wait_info or non-NULL blocking_session_id values? Show us that output.

The second result set gives you informational data as well as high priority data. Was there high CPU utilization? Are there any waits of note in there (though we’ll already have seen those with the 3rd result set that you posted)?

The fourth result set shows the I/O performance during that sample for any database file that had some reads or writes. You may have database files that aren’t listed there, it’s just that there weren’t any reads or writes on them during the collection. Do any of the files that are listed have an average stall of over 30 milliseconds. My target for reads is 15 milliseconds or less, and my target for writes is 5 milliseconds or less. Are any files showing high average stalls? The wait stats should reflect I/O waits if there’s a current I/O bottleneck.

The fifth result set is a dump of the SQL Server Performance Monitor counters. I generally skip over that section as I’ll usually pull that up in Performance Monitor as I want to see the values over time.

The sixth result set? I have no idea. Ask Erik.

And finally the seventh result set. That one shows current activity at the very end of the sample. Anything of note in there? Maybe there wasn’t any blocking when the sample started but maybe there is now.

Current activity via sp_WhoIsActive

I also use sp_WhoIsActive to look at current activity. On servers I’ve supported in the past, I have it logging to a table every 30-60 seconds and retaining the data for a few days so I can go back in time. I’ll query the WhoIsActive table in that case. If you don’t have that in place, run sp_WhoIsActive. If you see any long-running queries or blocking, run sp_WhoIsActive again with @get_plans = 1. This will give you the respective estimated execution plan, if it exists. If there’s a specific query you’d like help with, share the execution plan with us via Paste The Plan.

Sometimes fast, sometimes slow

If users are suddenly complaining that things are slow when it was just fine earlier, you might be experiencing what’s called bad Parameter Sniffing. Parameter Sniffing is a good thing, but it can cause problems.

If bad Parameter Sniffing is occurring on a frequently executed query, it could cause high CPU utilization which would slow everything down.

Grab the plan’s XML and save it. Then try removing the plan from the cache for that specific query. If users are now happy, you found the culprit and can now work towards a more permanent fix. We can help with that if you post the plan that you saved prior to removing it from the plan cache. It would also be helpful to see the plan after things are back to normal so that we can do a comparison. You may also need to post the query if the query is big as it may get truncated in the plan.

Check your question for questions and answers

Be sure to periodically check your question to see if it has been answered but also to see if anyone has any questions that could help get your question answered. Your question will be looked at by a lot of people soon after it has been posted. Refresh the page a few minutes after it is posted and again an hour later. Do this hourly for the first day and then daily until it has been answered. Most questions should get a good answer fairly quickly if the question is clear and if enough data has been provided.

PasteThePlan October 2017 Update

Last Updated October 4, 2017

Richie Rump

PasteThePlan.com

We’re back at it again. We took a bit of time out of our busy schedule to put in a few enhancements into PasteThePlan.com. Here’s some of the highlights:

Updated to the latest version of HTML Query Plan.

In order to create a graphical query plan we use Justin Pealing’s awesome HTML Query Plan. Recently, Justin updated the project with some improvements. The biggest change was the support for StmtCond, StmtUseDb, StmtCursor, and CursorPlan operators. This means PasteThePlan will display plans with cursors. No more table flipping. The lines between the operators were also updated and now we have arrows showing the flow of the data.

New “Download” plan button

We’ve added a “Download” button so you can download the plan in as a .sqlplan file. When downloaded, the plan can be opened directly in SSMS or Plan Explorer.

SQL Server 2017 and 2016 SP1 query plan support

With the advent of SQL Server 2016 SP1 and SQL Server 2017, query plans were given some changes in the underlying XML. As a result, these plans were not validating correctly in PasteThePlan. The issue has been fixed and all plans now validate correctly.

Missing Icons

There were a few plan operator icons that were not being displayed so we decided to fix that. Now KeyLookup, PopulateQuery, StmtCursor, FastForward, and SnapShot operators all have their corresponding icons.

Have a suggestion for a new feature or enhancement? Let us know down in the comments.

Bad Idea Jeans: Finding Undocumented Trace Flags

Last Updated October 4, 2017

Bad Idea Jeans

I need to start this blog post with a disclaimer: you should not be doing this. Hell, *I* should not be doing this.

But when SQL Server 2017 came out Monday, I noticed a few unusual SNAPSHOT_MATERIALIZATION entries in sys.messages and a few related undocumented system objects that hinted about a new feature Microsoft hadn’t announced yet. I played around with it for a while, couldn’t get it to work, so I posted a question on DBA.se: How do you create a view with SNAPSHOT_MATERIALIZATION? Sure enough, within minutes, Paul White had his debugger going, and he’d run the world’s first demo of that feature. Head on over to that Stack question if you want a sneak preview at what the feature does, and give the guy an upvote while you’re there. He worked hard on that one.

For the next step, I wanted to see if folks could play with it without using a debugger. I had a hunch that the feature might be turned on via trace flag, so I started running experiments. Like Edison would say, I haven’t failed yet – I’ve just found 2,289 ways (as of this morning) it won’t work. But just running the experiments are a hell of a lot of fun, and I figure that you, dear reader, might enjoy seeing what goes on in the labs here at Brent Ozar Unlimited when nobody’s looking.

Now, On with the Bad Idea Jeans

Let’s say, just theoretically, that a new version of SQL Server came out, and it had a new undocumented stored procedure that failed with an error message saying it was disabled.

The first thing you might try is looping through trace flags, one at a time, in pseudocode like this:

DECLARE @CurrentTraceFlag INT = 11500;
WHILE @CurrentTraceFlag >= 0
  BEGIN
  DBCC TRACEON(@CurrentTraceFlag, -1);
  BEGIN TRY
    /* Try to run some stored proc, and log if it works */
  END TRY
  BEGIN CATCH
    /* Log that it didn't work */
  END CATCH
  
  IF EXISTS (SELECT * FROM dbo.OurLoggingTable WHERE Success = 1)
    RETURN
  ELSE
    SET @CurrentTraceFlag = @CurrentTraceFlag - 1;
  END

DECLARE @CurrentTraceFlag INT = 11500;

WHILE @CurrentTraceFlag >= 0

BEGIN

DBCC TRACEON(@CurrentTraceFlag, -1);

BEGIN TRY

/* Try to run some stored proc, and log if it works */

END TRY

BEGIN CATCH

/* Log that it didn't work */

END CATCH

IF EXISTS (SELECT * FROM dbo.OurLoggingTable WHERE Success = 1)

RETURN

ELSE

SET @CurrentTraceFlag = @CurrentTraceFlag - 1;

END

In our theoretical case here, none of the trace flags enabled our theoretically hidden stored procedure. However, sometimes trace flags have to be enabled at startup, not just at runtime.

Testing Undocumented Startup Trace Flags

The DBCC TRACEON command works when the SQL Server is started up, but that’s too late to set some trace flags. Here’s the tools we’re going to use:

Startup stored procedure – so that as soon as the SQL Server engine starts, it runs a stored procedure to test our trace flag, log progress, set the next trace flag, and restart.
xp_readerrorlog – undocumented stored proc to read the error log. Here, I’m using it to dump the log into a table each time SQL Server starts up with a different trace flag. That way, I can analyze the log strings later to look for unusual outputs. More on that in a second.
xp_regwrite – undocumented stored procedure to set trace flags in the registry so that they run at startup. I don’t do this often, needless to say, so I started with this SQLServerCentral post’s code.
xp_cmdshell calling shutdown – I wish I could just reliably restart the SQL Server service from inside T-SQL, but I had some unreliable results with my stored procs rolling back when I tried the net start commands to restart the service from inside a proc. I could have also done this with an Agent job doing a waitfor for maybe 60 seconds and then restart, but I wanted very fast restarts, since I gotta do ~500 of them.

I gotta be honest with you, dear reader: any one of those above things is generally a pair of Bad Idea Jeans in and of itself, but we’re gonna layer up. (Not lawyer up, although that’d probably be a good idea too, as heinous as this script is.)

Add those all together, and you get this hot mess. Seriously, you shouldn’t ever run this on a server you care about.

The Payoff: Startup Error Logs by Trace Flag

Each time SQL Server starts up, I’m dumping the error logs into a table. Then, I can go back later to sort through the log messages that each trace flag produced. In the below screenshot:

TraceFlag – the flag that was on when the SQL Server started up
Text – an unusual error log message that wasn’t present by default, or wasn’t produced by too many other trace flags

So in that screenshot, we’ve discovered a few things:

Trace flag 9706 says “Software Usage Metrics is disabled.”
Trace flag 9850 dumps more diagnostic stuff in the log.
Trace flag 10264 says “Polybase mode enabled for SqlComposable.”
And a bunch of trace flags have been deprecated (but you probably weren’t using those anyway)

Should We Use These Trace Flags?

Of course not! That would be really, really stupid. We have no idea what behavior these trace flags really trigger under the covers. For all I know, one of them could report “Flintstones Chewable Data Pages Enabled” when it really runs auto-shrink every ten seconds.

However, when my poor VM has worked its way through all of the trace flags – started at 11498, now down to 9216 – I’ll post the full error log list in a backup so you folks can go spelunking through it. I’d rather have you do that than beat the hell out of some poor SQL Server VM.

This party won’t last long, either. Going forward, given that lots of folks at Microsoft seem to read this blog (surely for comedic value), it’s a fair bet that trace flag behavior will change going forward. They’ll probably switch to more cryptic logging at startup, like saying “Trace Flag 9706 Enabled” rather than saying “Software Usage Metrics is disabled.”

No More SQL Server Service Packs: Is CU12 the New SP1?

Last Updated October 3, 2017

Production Database Administration, SQL Server 2017

Starting with yesterday’s release of SQL Server 2017, Microsoft has a new servicing model: they’re only delivering Cumulative Updates, and not doing Service Packs.

Pedro Lopes writes:

SPs will no longer be made available. Only CUs, and GDRs when needed.

CUs will be delivered more often at first and then less frequently. Every month for the first 12 months, and every quarter for the remainder 4 years of the full 5-year mainstream lifecycle.

CUs are delivered on the same week of the month: week of 3rd Tuesday.

I adore this change for a few reasons:

It makes patching simpler – no more trying to figure out which CU branch you’re supposed to be on
It makes scheduling easier – if you take, say, quarterly outages, then you can just apply the most recent CU available based on your downtime schedule
It removes the “wait until SP1” stigma – there’s a good chunk of the population that thinks a product isn’t ready until the first major update, and now maybe they’ll see the first monthly CU1 as that adoption point.

This will influence how people see versions.

Examine this sentence closely:

“Every month for the first 12 months, and every quarter for the remainder 4 years…”

That means in November 2017 – October 2018, SQL Server 2017 is going to get monthly patches. However, starting in November 2018, it’s going to be more stable.

So now fast forward to late 2018, early 2019. You’re about to build a new SQL Server for a project, and you have two choices:

SQL Server 2018 – which is basically the new dev branch, getting monthly updates, or
SQL Server 2017 (or 2016, or 2014) – which is the stable branch, getting quarterly updates

Once a version has hit CU12, and it only gets updates once a quarter, it might be considered Good Enough For Our Apps. Managers might see 2017/2016/2014 interchangeably at that point – which might be great for the second most recent version’s adoption.

CU 12 might be the new SP1.

I can see managers saying, “Well, I don’t wanna go to a new version of SQL Server until they’ve eased up on the fast-and-furious update stream. I can’t take an outage every month to apply patches. Plus, based on the looks of some of these bugs they’re fixing, the software’s not really ready for prime time yet.”

And I wouldn’t blame them. After all, check out just some of the highlights from the most recent Cumulative Update, 2016 SP1 CU5:

And remember, this isn’t the fifth Cumulative Update for 2016: it’s the fifth update FOR 2016 SERVICE PACK 1. If you deployed SQL Server 2016 in June 2016 when it came out, you’d have had 8 patching outages by now to fix stuff like this.

Which is fine: adoption is slow anyway.

In the company chat room, Tara pointed out that few people jump on the most recent release anyway. SpotlightEssentials reports that <10% of servers are using SQL Server 2016 even today. That means ~90% of servers would be on the stable branch, getting CUs only once per quarter.

Years from now, I think we’ll all be glad that Microsoft took this step and switched to a simpler servicing model. There’s going to be a few pains as we explain to managers that no, you don’t need to wait for SP1 anymore – but depending on your stability goals and patch windows, you might wanna wait for CU12. (However, 90% of you aren’t installing the new version in the first 12 months anyway – so you’re unaffected.)

New System Stored Procedures in SQL Server 2017

Last Updated October 3, 2017

Columnstore Indexes, SQL Server 2017

Got a few new stored procs for columnstore indexes:

sp_add_columnstore_column_dictionary – input params for @table_id, @column_id
sp_is_columnstore_column_dictionary_enabled – as above, plus output param for @is_enabled bit
sp_remove_columnstore_column_dictionary – input params for @table_id, @column_id

And a couple for snapshot views (which appear to be related to the new snapshot messages in sys.messages)

sp_refresh_single_snapshot_view – input param for @view_name nvarchar(261) (!), @rgCode int
sp_refresh_snapshot_views – input param for @rgCode int

To see the public portions of their source code, hit up master.sys.all_sql_modules. Documentation doesn’t appear to be live yet at docs.microsoft.com, nor in the Github repo.

New sys.messages Entries in SQL Server 2017

Last Updated October 2, 2017

SQL Server 2017

I’m only showing the English (1033) ones here, message ID and then text. Some of the new messages are very Azure-specific, and while I love to think that we’d get some of these features in the boxed product, I think Microsoft is just getting down to a standard shared list of message IDs.

However, some of them are rather…well, I’ll just bold the ones that made me raise an eyebrow:

10147 – Cannot create %S_MSG on the view ‘%.*ls’ because it uses temporal FOR SYSTEM_TIME clause.
10148 – Cannot create %S_MSG on the view ‘%.*ls’ because it uses OPENJSON.
10149 – Index that has SNAPSHOT_MATERIALIZATION cannot be created on view ‘%.*ls’ because view definition contains memory-optimized table(s).
10342 – Assembly ‘%.*ls’ cannot be loaded because this edition of SQL Server only supports SAFE assemblies.
10343 – CREATE or ALTER ASSEMBLY for assembly ‘%.*ls’ with the SAFE or EXTERNAL_ACCESS option failed because the ‘clr strict security’ option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.
10344 – Internal table access error: failed to access the Trusted Assemblies internal table with HRESULT: 0x%x. Contact Customer Support Services.
10345 – The assembly hash ‘0x%.*ls’ is already trusted.
10346 – The assembly hash ‘0x%.*ls’ is not currently trusted. No action was taken.
10636 – Ignore in Optimization cannot be set for ‘%.*ls’ on ‘%.*ls.%.*ls’ because it is only applicable to non-clustered B-tree or columnstore indexes.
10637 – Cannot perform this operation on ‘%.*ls’ with ID %I64d as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.
10638 – ALTER INDEX ‘%S_MSG’ failed. There is no pending resumable index operation for the index ‘%.*ls’ on ‘%.*ls’.
10639 – Resumable index operation for %S_MSG ‘%.*ls’ failed because the index contains column ‘%.*ls’ of type timestamp as a key column.
10640 – Ignore in Optimization cannot be set for an index on ‘%.*ls.%.*ls’ because it is only applicable to indexes on user defined disk-based tables.
10641 – ALTER INDEX PAUSE failed. There is no actively running resumable index operation for the index ‘%.*ls’ on ‘%.*ls’.
10642 – SNAPSHOT_MATERIALIZATION cannot be set for index ‘%.*ls’ on ‘%.*ls’ because it is only applicable to indexes on views.
10643 – SNAPSHOT_MATERIALIZATION cannot be set for ‘%.*ls’ on ‘%.*ls’ because it is only applicable to clustered indexes on views.
10644 – Resumable index operation for %S_MSG ‘%.*ls’ failed because the index contains the computed column ‘%.*ls’ as a key or partitioning column. If this is a non clustered index, the column will be implicitly included as a key column if it is part of the clustered index key.
10645 – Warning: An existing resumable operation with the same options was identified for the same index on ‘%.*ls’. The existing operation will be resumed instead.
10646 – Index ‘%.*ls’ on view ‘%.*ls’ that has SNAPSHOT_MATERIALIZATION cannot be dropped using the DROP_EXISTING option.
10647 – Index ‘%.*ls’ on ‘%.*ls’ that has SNAPSHOT_MATERIALIZATION cannot be altered.
10648 – SNAPSHOT_MATERIALIZATION cannot be set for partitioned index ‘%.*ls’ on ‘%.*ls’.
10649 – Nonclustered index ‘%.*ls’ cannot be created on ‘%.*ls’ that has clustered index ‘%.*ls’ with SNAPSHOT_MATERIALIZATION.
10650 – Refresh of snapshot view(s) requires snapshot isolation to be enabled on the database.
10651 – Refresh of snapshot view failed because view ‘%.*ls.%.*ls’ does not exist.
10652 – Refresh of snapshot view failed because ‘%.*ls.%.*ls’ is not a snapshot view.
10653 – Refresh of snapshot view failed.
10654 – Refresh of snapshot view failed because lock could not be obtained on the database.
10655 – Refresh of snapshot view failed because view is not specified in two-part name format.
10656 – The resumable index operation for index ‘%.*ls’ on object ‘%.*ls’ was paused.
10657 – The resumable index ‘%.*ls’ on object ‘%.*ls’ has been paused for ‘%.2f’ hours.
10658 – Stored procedure ‘%ls’ is currently disabled.
10659 – An ALTER INDEX ‘%S_MSG’ was executed for index ‘%.*ls’ on object ‘%.*ls’ by hostname ‘%.*ls’.
10712 – The pseudocolumn ‘%.*ls’ cannot be part of an index.
10719 – Foreign key relationships from a memory optimized table with DURABILITY set to SCHEMA_AND_DATA to a memory optimized table with DURABILITY set to SCHEMA_ONLY are not supported.
10936 – Resource ID : %d. The %ls limit for the elastic pool is %d and has been reached. See ‘http://go.microsoft.com/fwlink/?LinkId=267637’ for assistance. (Note from Brent – remember, this message is in the on-premises boxed product.)
10937 – Default workload group does not allow to alter attribute ‘%.*ls’.
1104 – TEMPDB ran out of space during spilling. Create space by dropping objects and/or rewrite the query to consume fewer rows. If the issue still persists, consider upgrading to a higher service level objective.
11431 – The %S_MSG option is not permitted as the %S_MSG option is not turned ‘%ls’.
11432 – %S_MSG = %d is not a valid value; %S_MSG must be greater than 0 and less than %d.
11433 – ‘%.*ls’ with %S_MSG option is not suppported on %S_MSG ‘%.*ls’.
11434 – The %S_MSG option is not supported for the disabled index ‘%.*ls’ on %S_MSG ‘%.*ls’.
11435 – The %S_MSG option is not supported for Heaps at %S_MSG ‘%.*ls’.
11436 – Cannot alter table ‘%.*ls’ because non-nullable column ‘%.*ls’ cannot be declared as hidden without either a DEFAULT constraint, the GENERATED ALWAYS attribute, or the IDENTITY attribute.
11437 – Cannot alter table ‘%.*ls’ because at least one column must be declared as non-hidden.
11438 – The %S_MSG option cannot be set to ‘%ls’ when the %S_MSG option is set to ‘%ls’.
11557 – The stored procedure ‘sp_get_jobs_database_scoped_credential’ is not available.
11558 – The distributed_move JSON string could not be parsed. Please check the formatting of the JSON.
11559 – The distributed_move JSON parameter %.*ls is missing or incorrectly formatted. Please check the formatting of the JSON.
11560 – The distributed_move parameter %.*ls value is outside its valid range. Please try again with a valid parameter.
11903 – Cannot create table ‘%.*ls’ because non-nullable column ‘%.*ls’ cannot be declared as hidden without either a DEFAULT constraint, the GENERATED ALWAYS attribute, or the IDENTITY attribute.
11904 – Cannot create table ‘%.*ls’ because at least one column must be declared as non-hidden.
11905 – Resumable index operations can only be performed in Enterprise edition of SQL Server.
12110 – Statement ‘%.*ls’ failed, because it attempted to set the ‘%.*ls’ option for the secondaries replica while this option is only allowed to be set for the primary.
12352 – System stored procedures that cause schema changes to %S_MSG are not supported inside user transactions.
12353 – Computed columns in indexes on memory optimized tables must be declared as PERSISTED.
12354 – The function %ls with an argument in a Japanese_140 collation is currently not supported with %S_MSG.
12355 – The persistence of a computed column in a memory optimized table cannot be altered. Drop and re-create the column with the desired persistence.
12445 – Cannot set Query Store max_storage_size_mb to %lu. The maximum allowed limit is %lu MB.
12446 – Query Store stored procedure is not available on readable secondary replica.
12447 – Query plan with plan_id (%ld) cannot be forced for query with query_id (%ld) as plan forcing is not supported for this type of statement.
12449 – Query plan cannot be forced for this query as plan forcing is not supported for resumable index builds.
12500 – SELECT INTO not allowed in the CTAS statement.
12501 – Different number of columns in CREATE TABLE and SELECT query.
12502 – Data types cannot be defined using CREATE TABLE AS SELECT syntax.
12503 – Specifying schema elements in the CREATE SCHEMA statement is not supported.
12504 – Distribution column ‘%.*ls’ must be implictly or explictly specified in the insert select query as the target table ‘%.*ls’ is a hash distributed table.
12701 – Failed to lock external data source object (EDS name: %.*ls). Lock Mode: %.*ls.
12702 – Failed to lock external data source manager. Lock Mode: %.*ls.
12703 – Referenced external data source “%ls” not found.
12704 – Bad or inaccessible location specified in external data source “%ls”.
12705 – Referenced external data source “%ls” type is “%ls”. Please use BLOB_STORAGE type to reference Azure Blob Storage locations.
12844 – ALTER DATABASE statement failed; this functionality is not available in the current edition of SQL Server.
13327 – external library
13328 – QUERY_TUNING_MODE
13329 – QDS server name
13330 – WAIT_STATS_CAPTURE_MODE
13764 – Online alter column is not supported for system-versioned temporal table ‘%.*ls’.
13765 – Setting finite retention period failed on system-versioned temporal table ‘%.*ls’ because the history table ‘%.*ls’ does not contain required clustered index. Consider creating a clustered columnstore or B-tree index starting with the column that matches end of SYSTEM_TIME period, on the history table.
13766 – Cannot drop the clustered index ‘%.*ls’ because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned temporal table if you need to drop this index.
13767 – The clustered index ‘%.*ls’ is being used for automatic cleanup of aged data, and can be converted to either a clustered columnstore or B-tree index starting with the column that matches end of SYSTEM_TIME period, on the history table. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned temporal table if you need to rebuild this index with a modified column specification, not supported with temporal history retention.
13768 – Retention cleanup of history table for a temporal table (database id %lu, table id %ld) has not been executed. Either the cleanup is disabled on the database, appropriate lock could not be obtained or the temporal table does not exist anymore.
13769 – Setting SYSTEM_VERSIONING to ON failed for table ‘%.*ls’ because ‘%.*ls’ with REMOTE_DATA_ARCHIVE enabled cannot be used as a history table when a finite retention period is specified.
13770 – Setting finite retention period failed on system-versioned temporal table ‘%.*ls’ because REMOTE_DATA_ARCHIVE is enabled on the corresponding history table ‘%.*ls’.
13771 – Setting finite retention period failed on system-versioned temporal table ‘%.*ls’ because corresponding history table ‘%.*ls’ has both clustered columnstore and non-clustered indexes. Consider dropping non-clustered indexes from the history table and try again.
13772 – Cannot create non-clustered index on temporal history table ‘%.*ls’ since it has finite retention period and clustered columnstore index defined.
13773 – Clustered column store temporal retention cleanup attempted on non-clustered column store table ‘%.*ls’.
13776 – Table ‘%.*ls’ does not exist or you do not have the required permissions to complete the operation.
13777 – Stored procedure ‘sp_cleanup_temporal_history’ is temporarily disabled. Please contact customer support for more information.
13778 – The only allowed ALTER TABLE operations on temporal in-memory history table ‘%.*ls’ are ADD INDEX, DROP INDEX and ALTER INDEX.
13779 – Defining a column store index on temporal in-memory history table ‘%.*ls’ is not supported.
13780 – Setting finite retention period failed on system-versioned temporal table ‘%.*ls’ beacause the history table ‘%.*ls’ is clustered column store that contains large object columns.
13781 – Wrong type for column ‘%.*ls’: the system-versioned temporal table ‘%.*ls’ cannot contain large object columns, because it has finite retention and the clustered column store history table ‘%.*ls’.
13782 – Cannot create clustered column store index ‘%.*ls’ on table ‘%.*ls’ because it contains large object columns and is the history table of temporal table ‘%.*ls’ that has finite retention.
13783 – Data modification failed on system-versioned table ‘%.*ls’ because column ‘%.*ls’ cannot be output into the history table ‘%.*ls’.
13800 – Insert bulk statement does not support recompile.
13900 – Identifier ‘%.*ls’ in a MATCH clause could not be bound.
13901 – Identifier ‘%.*ls’ in a MATCH clause is not a node table or an alias for a node table.
13902 – Identifier ‘%.*ls’ in a MATCH clause is not an edge table or an alias for an edge table.
13903 – Edge table ‘%.*ls’ used in more than one MATCH pattern.
13904 – All node and edge tables within a single MATCH clause must reside in the same database.
13905 – A MATCH clause may not be directly combined with other expressions using OR or NOT.
13906 – A MATCH clause is only permitted in a WHERE clause or in the ON clause of a GRAPH JOIN.
13907 – Cannot modify permissions for graph column ‘%.*s’.
13908 – Cannot access internal graph column ‘%.*ls’.
13909 – The internal graph column ‘%.*ls’ cannot be added to an index; use pseudocolumn ‘%.*ls’ instead.
13910 – Cannot create a node or edge table as memory optimized.
13911 – Cannot create a node or edge table as a remote data archive.
13912 – A node or edge table cannot be created as or altered to a temporal table.
13913 – Internal graph columns cannot be altered.
13914 – Cannot create a node or edge table as a temporary table.
13915 – Cannot create a node or edge table as an external table.
13916 – The graph column ‘%.*ls’ cannot be used as a non-key column in an index.
13917 – After expanding the computed graph column ‘%.*ls”, the index ‘%.*ls’ on table ‘%.*ls’ exceeds the maximum number of columns in the key list. The maximum number of index key columns is %d.
13918 – Adding constraint to column ‘%.*s’ of a node or edge table is not allowed.
13919 – Identifier ‘%.*ls’ in a MATCH clause corresponds to a derived table. Derived tables are not supported in MATCH clauses.
13920 – Identifier ‘%.*ls’ in a MATCH clause is used with a JOIN clause or APPLY operator. JOIN and APPLY are not supported with MATCH clauses.
13921 – JSON data for INSERT/UPDATE of graph pseudocolumn ‘%.*ls’ is malformed.
13922 – Remote tables are not supported in MATCH clauses.
13923 – Pseudocolumn ‘%.*s’ is invalid because it references a node or edge table that is not in the current database.
13924 – Select * is not permitted because it refers to a node or edge table ‘%.*s’ which is not in the current database.
13925 – Cannot drop or disable index or constraint ‘%.*s’ because the last unique index or constraint on ‘%.*s’ cannot be dropped or disabled.
13926 – The use of replication is not supported for node or edge table ‘%ls’.
13927 – Change Data Capture is not supported for node or edge table ‘%ls’.
13928 – Use pseudocolumn ‘%.*ls’ to add a constraint on a node or edge table.
13929 – The pseudocolumn ‘%.*ls’ cannot be part of an index unless the index is on a node or edge table that contains that pseudocolumn.
15701 – Statement ‘%.*ls’ failed, because it attempted to set the Automatic Tuning option ‘%.*ls’ multiple times.
15702 – Cannot perform action because Automatic Tuning cannot be enabled on system database %.*ls.
15703 – Setting Automatic Tuning option ‘%.*ls’ to %.*ls for database ‘%.*ls’.
15704 – Automatic Tuning option ‘%.*ls’ is disabled by system.
15705 – Automatic Tuning option %.*ls cannot be enabled, because Query Store is not turned on.
15706 – Automatic Tuning option %.*ls cannot be enabled, because Query Store is in READ_ONLY mode.
15707 – Automatic Tuning is only available for enterprise customers. (Note from Brent: that’s interesting wording. Usually they call it Enterprise Edition because even enterprise customers run Standard Edition.)
15716 – Only one Automatic Tuning option can be given in ALTER DATABASE statement.
16202 – Keyword or statement option ‘%.*ls’ is not supported on the ‘%.*ls’ platform.
16601 – Credential of database ‘%ls’ are invalid.
16602 – Cannot delete sync agent ‘%ls’ because it is used by sync member ‘%ls’.
16603 – Cannot add database ‘%ls’ into sync group because the database name is invalid.
16604 – Hub database ‘%ls’ is invalid. (Note from Brent: these sync/hub/member messages are related to SQL Data Sync.)
16605 – Member database ‘%ls’ is invalid.
16606 – Database ‘%ls’ cannot be deleted because it is used as a sync metadata database which still contains sync groups and/or sync agents.
16607 – Sync metadata database ‘%ls’ is invalid.
16608 – Cannot create or update sync group because the sync group name ‘%ls’ is invalid.
16609 – Cannot create or update sync group ‘%ls’ because the conflict resolution policy is invalid.
16610 – Cannot create sync group because the sync group name ‘%ls’ is used.
16611 – Cannot create or update sync group ‘%ls’ because the sync schema contains circular reference.
16612 – Cannot create or update sync group ‘%ls’ because the table ‘%ls’ in sync schema contains no clustered index.
16613 – Cannot delete sync group ‘%ls’ because the sync group is syncing.
16614 – Cannot create or update sync group ‘%ls’ because database ‘%ls’ is invalid.
16615 – Cannot create or update sync group ‘%ls’ because the sync interval is invalid.
16616 – Cannot update sync schema because the data type change is not supported.
16617 – Sync group ‘%ls’ is not ready to update sync schema because there are some ongoing operations on the sync group.
16618 – Cannot update sync schema because some columns are missing in database ‘%ls’.
16619 – Cannot update sync schema because some tables are missing in database ‘%ls’.
16620 – Cannot update sync schema because the format of sync schema is invalid.
16621 – Sync group ‘%ls’ is not in active state. Make sure the sync schema of it is set.
16622 – Hub database ‘%ls’ is suspended because the credential of it is invalid.
16623 – Sync group ‘%ls’ is invalid.
16624 – Cannot create or update sync member because the sync member name ‘%ls’ is invalid.
16625 – Cannot create or update the sync member ‘%ls’ because the database type ‘%ls’ provided is invalid.
16626 – Cannot create or update the sync member ‘%ls’ because the sync direction ‘%ls’ provided is invalid.
16627 – Cannot create or update the sync member ‘%ls’ because the sync agent ‘%ls’ provided is invalid.
16628 – Cannot create or update the sync member ‘%ls’ because the SQL Server database ID ‘%ls’ provided is invalid.
16629 – Cannot create sync member because the sync member name ‘%ls’ provided is used.
16630 – Cannot create sync member ‘%ls’ because the database ‘%ls’ provided is already added as a sync member.
16631 – Cannot delete sync member ‘%ls’ when it is syncing.
16632 – Sync member ‘%ls’ is invalid.
16633 – Cannot create sync agent because the sync agent name ‘%ls’ provided is used.
16634 – Sync agent ‘%ls’ is invalid.
16635 – Cannot create sync group ‘%ls’ because the maximum number of sync groups can be created is %d.
16636 – Cannot create sync member ‘%ls’ because the maximum number of sync members can be created in a sync group is %d.
16637 – Cannot create or update sync group ‘%ls’ because the maximum count of tables in sync schema is %d.
16638 – Cannot create or update sync group ‘%ls’ because the table ‘%ls’ in sync schema contains no primary key.
16639 – Cannot create or update sync group ‘%ls’ because the sync schema provided contains unsupported column data type.
16640 – Cannot refresh schema of the database ‘%ls’.
16641 – Cannot create sync agent ‘%ls’ under a different SQL Server than the one of sync metadata database.
1735 – Duplicate range-partitioning function specified for the table being created. Remove one of the range-partitioning function specifications and resubmit the request.
1737 – Duplicate distribution clause specified for the table being created. Remove one of the distribution clauses and resubmit the request.
17900 – A network error occurred in the established connection; the connection has been closed.
22119 – Cannot enable change tracking on table ‘%.*ls’. Change tracking requires a primary key constraint on the table to be enabled. Enable the primary key constraint on the table before enabling change tracking.
22120 – Invalid value for cleanup batch size.
22121 – Deleted %ld row(s) per millisecond from %s
22845 – Cannot enable change data capture in this edition of SQL Server.
25748 – The file “%s” contains audit logs. Audit logs can only be accessed by using the fn_get_audit_file function.
27242 – Cannot find the cluster execution job instance ‘%ls’ because it does not exist or you do not have sufficient permissions. (Note from Brent – this range of messages below appears to be related to SSIS scale-out.)
27243 – Cannot find the cluster worker agent ‘%ls’ because it does not exist or you do not have sufficient permissions.
27246 – Cannot find the cluster execution task instance ‘%ls’ because it does not exist or you do not have sufficient permissions.
27251 – Cannot add a worker agent to execute the cluster job instance ‘%ls’ because the job is specified for all worker agents.
27252 – The Integration Services cluster worker agent ‘%ls’ is disabled.
27255 – The job id of the execution is null. Create a job for the execution first and try again.
27256 – The event message does not exist.
27257 – There is no active worker agent.
27259 – Failed to enable worker agent because current SQL Server edition only support limited number of worker agents.
27260 – Only members of sysadmin server roles can perform this action.
27261 – There is no Scale Out Master installed.
31021 – Could not get winfab log directory when performing DTA tuning related task.
31022 – Could not get winfab data package directory when performing DTA tuning related task.
31204 – Operation %ls is not supported on the %ls platform. (Note from Brent: don’t go assuming this means you can’t do it on Linux, ha ha ho ho. Could be the other way around.)
32056 – The SQL Server failed to create full-text FTData directory. This might be because FulltextDefaultPath is invalid or SQL Server service account does not have permission. Full-text may fail until this issue is resolved. Restart SQL Server after the issue is fixed.
3228 – Writing snapshot metadata to backup set for file “%ls” failed due to invalid format.
3259 – Failed to copy blob with name ‘%ls’, to destination ‘%ls’. Error returned ‘%ls’.
33061 – The specified RETENTION_DAYS value is greater than the maximum value allowed. The retenton days value must be less than %d days.
33062 – Password validation failed. The password does not meet SQL Server password policy requirements because it is too short. The password must be at least %d characters.
33063 – Password validation failed. The password does not meet SQL Server password policy requirements because it is too long. The password must be at most %d characters.
33064 – Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least %d characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.
33066 – An error occurred while configuring the password policy: NTSTATUS 0x%x, state %d. To skip configuring the password policy, use trace flag -T4634 during startup. This will cause SQL Server to run using a minimal password policy.
33179 – Searching for ‘%.*ls’ only searches “‘%.*ls'” for SQL Server authentication logins.
33180 – Searching for “‘%.*ls'” only searches for Azure Active Directory users. To search for a SQL Server authentication login, add the server name at the end. For example [%.*ls@%.*ls].
33181 – The new owner cannot be Azure Active Directory group.
33182 – The command must be executed on the target database ‘%.*ls’.
33183 – The Azure Key Vault client encountered an error with message ‘%s’.
33184 – An error occurred while obtaining information for the Azure Key Vault client with message ‘%s’.
33518 – Valid values of the database compatibility level are %d, %d, %d, or %d.
33519 – An external data source conflicting with ‘%.*ls’ already exists in EDS cache in memory. Use alter step to change the location or credential. Drop and re-create to change the EDS name.
33520 – Failed to allocate memory for caching external data source ‘%.*ls’ which is used by a database file.
33521 – Cannot drop the external data source ‘%.*ls’ because it is being used.
33522 – Cannot remove the external data source ‘%.*ls’ because it is being used.
33523 – Cryptographic failure due to insufficient memory.
33524 – The fn_get_audit_file function is skipping records from ‘%.*ls’. You must be connected to database ‘%.*ls’ to access its audit logs.
33525 – The fn_get_audit_file function is skipping records from ‘%.*ls’. You must be connected to server ‘%.*ls’ to access its audit logs.
33526 – The fn_get_audit_file function is skipping records from ‘%.*ls’, as it does not conform to the auditing blob naming convention.
33527 – Error occured while initializing the security functions lookup table. This might be because the installation of SQL Server is corrupted and a required file is missing.
3461 – Failed to wait for XTP %ls to complete during recovery.
35248 – The %ls operation is not allowed by the current availability-group configuration. The required_synchronized_secondaries_to_commit %d is greater than the %d possible secondary synchronous-commit availability replicas in availability group ‘%.*ls’. Change one of the existing asynchronous-commit replicas to the synchronous-commit availability mode, and retry the operation.
35383 – The use of user-defined functions is not allowed in default constraints when adding columns to a columnstore index.
35384 – The statement failed because column ‘%.*ls’ on table ‘%.*ls’ is a lob column. Non clustered index with lob column as included column cannot co-exist with clustered columnstore index.
35386 – Could not get the memory grant of %I64d KB for columnstore compression because it exceeds the maximum configuration limit of %I64d KB in the current workload group and resource pool. Please rerun query at a higher resource class, and also consider increasing DWU size. See ‘https://aka.ms/sqldw_columnstore_memory‘ for assistance.
35387 – TEMPDB ran out of space during spilling. Verify that data is evenly distributed and/or rewrite the query to consume fewer rows. If the issue still persists, consider upgrading to a higher service level objective. (Note from Brent – remember, this is on-premises. Given the number of Azure-specific messages in here, I’m going to stop noting these – they’re probably just getting down to a single set of messages across both the boxed product and Azure SQL DB.)
35388 – The statement failed because column ‘%.*ls’ on table ‘%.*ls’ is a persisted computed column. Adding persisted computed columns to an existing clustered columnstore index is not supported.
35389 – The statement failed because column ‘%.*ls’ on table ‘%.*ls’ is a computed column of a LOB type. Columnstore indexes cannot include computed columns of LOB types.
35390 – Computed columns in columnstore indexes are temporarily disabled. Please rewrite your query to exclude computed columns.
35392 – The statement failed because column ‘%.*ls’ on table ‘%.*ls’ is a computed column. Nonclustered index with computed column as key or included column cannot be created on a table that has a clustered columnstore index.
35504 – server encryption protector
35505 – RESUMABLE
35506 – MAX_DURATION
35507 – Storage
35508 – RESUME
35509 – PAUSE
35510 – ABORT
3643 – The operation elapsed time exceeded the maximum time specified for this operation. The execution has been stopped.
37101 – Server ‘%.*ls’ does not contain elastic job account: ‘%.*ls’.
37102 – Elastic job account ‘%.*ls’ does not contain the resource of type ‘%.*ls’ named ‘%.*ls’.
37103 – Internal job account error occurred : ‘%.*ls’.
37104 – A job account already exists for subscription ‘%.*ls’ for the selected region.
37105 – The job account ‘%.*ls’ on server ‘%.*ls’ already exists.
37106 – The database ‘%.*ls’ on server ‘%.*ls’ is in use by job account ‘%.*ls’. The database cannot be deleted or renamed while associated with a job account.
37107 – The database ‘%.*ls’ on server ‘%.*ls’ has service level objective ‘%.*ls’ which is not supported for use as a job account database.
3757 – WAIT_AT_LOW_PRIORITY clause is not permitted without ONLINE = ON option, for drop clustered %S_MSG ‘%.*ls’ on Table ‘%.*ls’.
3758 – Multiple %S_MSG cannot be dropped when WAIT_AT_LOW_PRIORITY clause is specified.
3759 – %.*ls constraint ‘%.*ls’ cannot be dropped when WAIT_AT_LOW_PRIORITY clause is used.
3760 – Cannot drop index ‘%.*ls’ on view ‘%.*ls’ that has SNAPSHOT_MATERIALIZATION.
39031 – Cannot parse the output schema of the builtin function ‘PREDICT’.
39032 – The function PREDICT expects parameters in the form of ‘name = value’.
39033 – The parameter name ‘%.*ls’ has already been declared. Parameter names must be unique in a PREDICT function call.
39034 – The parameter ‘PARAMETERS’ in PREDICT function contains a definition that doesn’t match the supplied arguments.
39035 – The function PREDICT has too many arguments supplied.
39036 – The function PREDICT expects parameter ‘%.*ls’ which was not supplied.
39037 – The function PREDICT contains a parameter ‘%.*ls’ that has an invalid type.
39038 – The function PREDICT expects parameter ‘PARAMETERS’ of type ntext/nchar/nvarchar.
39039 – Error converting the parameter value for ‘%.*ls’ to ‘%.*ls’.
39040 – The function ‘PREDICT’ does not support SQL identifier or variable for ‘PARAMETERS’.
39041 – The parameter ‘PARAMETERS’ has an invalid definition.
39042 – %s EXTERNAL LIBRARY failed because the library source parameter %d is not a valid expression.
39043 – %s EXTERNAL LIBRARY failed because filename ‘%.*ls’ is too long.
39044 – %s EXTERNAL LIBRARY failed because it could not open the physical file ‘%.*ls’: %ls.
39045 – %s EXTERNAL LIBRARY failed because it could not read from the physical file ‘%.*ls’: %ls.
39046 – CREATE EXTERNAL LIBRARY failed because the user “%.*ls” specified in the authorization clause does not exist.
39047 – External library ‘%.*ls’ already exists for owner ‘%.*ls’ in database ‘%.*ls’.
39048 – Failed to %s external library ‘%ls’: %ls.
39049 – Message(s) from ‘PREDICT’ engine: %.*ls%.*ls
39050 – Error occurred during execution of the builtin function ‘PREDICT’ with HRESULT 0x%x. Out of memory.
39051 – Error occurred during execution of the builtin function ‘PREDICT’ with HRESULT 0x%x. Model is corrupt or invalid.
39052 – Error occurred during execution of the builtin function ‘PREDICT’ with HRESULT 0x%x. Model type is unsupported.
39092 – Initialization of native scoring libraries failed with HRESULT 0x%x.
39093 – ‘PREDICT’ function does not take parameters of varchar(max), nvarchar(max) or varbinary(max) type except for ‘MODEL’ parameter.
39094 – ‘PREDICT’ function only supports models smaller than 100 MB.
39096 – Execution failed because its WITH clause specified different output columns from what ‘PREDICT’ function tries to return. The schema returned by ‘PREDICT’ function is ‘%ls’.
39097 – Input data column #%d is of type ‘%ls’ which is not supported by ‘PREDICT’ function. Unsupported types are binary, varbinary, timestamp, datetime2, datetimeoffset, time, text, ntext, image, hierarchyid, xml, sql_variant and user-defined type.
39098 – Error occurred during execution of the builtin function ‘PREDICT’ with HRESULT 0x%x.
39099 – Feature or option ‘PREDICT’ is not yet implemented. Please consult Books Online for more information on this feature or option.
40555 – ‘%ls’ is not supported for this database. See Books Online for more details on feature support in different service tiers of Windows Azure SQL Database.
40912 – The value for custom backup retention in days must be between %d and %d
40913 – Windows Azure SQL Database supports a maximum of %d Virtual Network firewall rules. (Note from Brent – see what I mean about Azure-specific messages? I really doubt SQL Server is going to get this exact message on-premises. Microsoft’s just shipping one sys.messages table to rule them all.)
40914 – Cannot open server ‘%.*ls’ requested by the login. Client from a Virtual Network with Service Tunneling is not allowed to access the server.
40915 – Secondary server specified in a Failover Group cannot reside in the same region (Note from Brent – this & the below messages are Azure-specific. It’s possible they could bring this functionality down on-premises, though, if we were given a set of tools to manage the geographic locations of servers. Not likely.)
40916 – The Failover Group ‘%.*ls’ already exists on server ‘%.*ls’
40917 – The Failover Group ‘%.*ls’ does not exist on server ‘%.*ls’
40918 – The Failover Group ‘%.*ls’ is busy with another operation and cannot perform ‘%.*ls’ operation. Please try again later
40919 – The server ‘%.*ls’ is currently the primary server in the Failover Group and cannot initate failover
40920 – The database ‘%.*ls’ is already included in another Failover Group
40921 – The operation to add database ‘%.*ls’ to Failover Group is in progress, please wait for this operation to finish
40922 – The operation to remove database ‘%.*ls’ from Failover Group is in progress, please wait for this operation to finish
40923 – The database ‘%.*ls’ is a secondary in an existing geo-replication relationship and cannot be added to the Failover Group
40924 – The operation cannot be performed due to multiple errors: ‘%.*ls’
40925 – Can not connect to the database in its current state.
40926 – The operation cannot be performed because the geo-replication link is part of a Failover Group. You must remove the database from the group in order to individually terminate or failover.
40927 – The endpoint ‘%.*ls’ is already in use. Use a different Failover Group name.
40928 – Create or update Failover Group operation successfully completed; however, some of the databases could not be added to or removed from Failover Group: ‘%.*ls’
40929 – The source database ‘%ls.%ls’ cannot have higher edition than the target database ‘%ls.%ls’. Upgrade the edition on the target before upgrading source.
40930 – The target database ‘%ls.%ls’ cannot have lower edition than the source database ‘%ls.%ls’. Downgrade the edition on the source before downgrading target.
40931 – Failover Group name ‘%.*ls’ cannot be empty or null. It can only be made up of lowercase letters ‘a’-‘z’, the numbers 0-9 and the hyphen. The hyphen may not lead or trail in the name.
40932 – The elastic pool cannot change its service tier since one or more of its databases use memory-optimized objects.
40933 – The edition ‘%.*ls’ does not support the database tempdb max size ‘%.*ls’.
41662 – Database ‘%ls’ (ID %d) of Windows Fabric partition ‘%ls’ (partition ID ‘%ls’) hit exception while running async tasks in Generic Subscriber.
41663 – Failed to parse datawarehouse columnar cache settings during replica manager startup.
41806 – Parameter “%ls” specified for procedure or function “%ls” is not valid.
41838 – Failed to retrieve size for this file due to an internal error. Please try again later.
41840 – Could not perform the operation because the elastic pool has reached its quota for in-memory tables. See ‘http://go.microsoft.com/fwlink/?LinkID=623028’ for more information.
41841 – Found inconsistent boot-page for database ‘%.*ls’.
41842 – Too many rows inserted or updated in this transaction. You can insert or update at most 4,294,967,294 rows in memory-optimized tables in a single transaction.
41843 – Unable to construct segment for segment table.
41844 – Clustered columnstore indexes are not supported on memory optimized tables with computed columns.
41845 – Checksum verification failed for memory optimized checkpoint file %.*ls.
41846 – Memory optimized checkpoint table consistency error detected. Checkpoint %I64d does not have unique recoverLsn. PrevLSN = (%I64d:%hu), CurrLSN = (%I64d:%hu).
41847 – Memory optimized checkpoint table consistency error detected. Checkpoint %I64d does not point to a transaction segment definition record.
41848 – Memory optimized checkpoint table consistency error detected. Checkpoint %I64d does not have ascending recoverLsn. PrevLSN = (%I64d:%hu), CurrLSN = (%I64d:%hu).
41849 – Memory optimized segment table consistency error detected. Segments are not contiguous in logical space. Older Segment CkptId = %I64d, LsnInfo = (%I64d:%hu), TxBegin = %I64d, TxEnd = %I64d. Newer Segment CkptId = %I64d, LsnInfo = (%I64d:%hu), TxBegin = %I64d, TxEnd = %I64d.
41850 – Memory optimized segment table consistency error detected. Segments are not well formed for Segment CkptId = %I64d, LsnInfo = (%I64d:%hu), TxBegin = %I64d, TxEnd = %I64d.
41851 – Memory optimized segment table consistency error detected. Segment definition ordering does not match the (strict) logical ordering. Older Segment CkptId = %I64d, LsnInfo = (%I64d:%hu), TxBegin = %I64d, TxEnd = %I64d. Newer Segment CkptId = %I64d, LsnInfo = (%I64d:%hu), TxBegin = %I64d, TxEnd = %I64d.
41852 – Memory optimized segment table consistency error detected. Segment has a NullHkLsn. CkptId = %I64d, Segment LsnInfo = (%I64d:%hu)
41853 – Memory optimized segment table consistency error detected. Current segment goes backward further than the definition record of the N-2 segment. Older Segment CkptId = %I64d, LsnInfo = (%I64d:%hu), TxBegin = %I64d, TxEnd = %I64d. Newer Segment CkptId = %I64d, LsnInfo = (%I64d:%hu), TxBegin = %I64d, TxEnd = %I64d.
41854 – Memory optimized file consistency error detected. An in use file with FileId %.*ls is referenced by the Checkpoint File Table but is not accounted for in the Storage Interface.
41855 – Memory optimized file consistency error detected. Could not find a file with FileId %.*ls in the File Watermark Table visible by checkpoint ID %u.
41856 – Memory optimized file consistency error detected. A file with FileId %.*ls of size %I64d bytes is smaller than expected %I64d bytes.
41857 – Memory optimized file consistency error detected. A presumably in use file with FileId %.*ls was not found in the Checkpoint File Table.
41858 – Memory optimized file consistency error detected. A presumably in use file with FileId %.*ls was not found in the File Watermark Table.
41859 – Memory optimized file consistency error detected. A presumably in use file with FileId %.*ls of size %I64d bytes is smaller than expected (%I64d bytes)
41860 – Memory optimized file consistency error detected. A presumably unused file with FileId %.*ls was found in the Checkpoint File Table.
41861 – Memory optimized large rows table consistency error detected. File Id is %.*ls. Corresponding LSN range is not ascending. Begin Lsn is (%I64d:%hu), End Lsn is (%I64d:%hu).
41862 – Memory optimized large rows table consistency error detected. Corresponding File %.*ls not found in File Table. Begin Lsn is (%I64d:%hu), End Lsn is (%I64d:%hu).
41863 – Checkpoint %I64d points to a root file %.*ls which was in use by a more recent checkpoint.
41864 – Checkpoint %I64d has a file %.*ls which has a watermark (%I64d) larger than the more recent checkpoints watermark (%I64d).
41865 – File %.*ls does not have a pair file %.*ls
41866 – Unprocessed File %.*ls does not have a pair file which is also unprocessed. Processed pair file is %.*ls.
41867 – Consistency errors detected in the MEMORY_OPTIMIZED_DATA filegroup. See preceding error messages for details. Consult https://go.microsoft.com/fwlink/?linkid=845604 for details on how to recover from the errors.
41868 – Memory optimized filegroup checks could not be completed because of system errors. See errorlog for more details. (Note from Brent – hallelujah, are we finally getting CHECKDB for in-memory OLTP?)
41901 – One or more of the options (%ls) are not supported for this statement in SQL Managed Instance. Review the documentation for supported options.
41902 – Unsupported device type. SQL Managed Instance supports database restore from URI backup device only. (Note from Brent: well, now, that’s interesting to see Managed Instance messages in here given that the product hasn’t officially hit production yet, only preview.)
41903 – CREATE DATABASE failed. Cannot create filename %ls. Local file paths are not supported.
4199 – Argument value %d is invalid for argument %d of %ls function.
42033 – Updating Layered AG Configuration failed.
4221 – Login to read-secondary failed due to long wait on ‘HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING’. The replica is not available for login because row versions are missing for transactions that were in-flight when the replica was recycled. The issue can be resolved by rolling back or committing the active transactions on the primary replica. Occurrences of this condition can be minimized by avoiding long write transactions on the primary.
43001 – ‘%.*ls’ is not a valid login name.
43002 – Storage in MB %d exceeds the maximum limit %d.
43003 – More than one firewall rules have the same name ‘%.*ls’.
43004 – ‘%.*ls’ is not a valid firewall rule name because it contains invalid characters.
43005 – ‘%.*ls’ is not a valid database name because it contains invalid characters.
43006 – Database name ‘%.*ls’ is too long.
43007 – ‘%.*ls’ is not a valid database charset.
43008 – ‘%.*ls’ is not a valid database collation.
43009 – Storage in MB %d is below the minimum limit.
43010 – ‘%.*ls’ cannot be updated.
43011 – ‘%.*ls’ is not a valid version.
43012 – The configuration name cannot be empty.
43013 – The value for configuration ‘%.*ls’ cannot be empty.
43014 – The same configuration ‘%.*ls’ cannot be updated more than once.
43015 – The configuration ‘%.*ls’ does not exist for %.*ls server version %.*ls.
43016 – The value ‘%.*ls’ for configuration ‘%.*ls’ is not valid. The allowed values are ‘%.*ls’.
43017 – The configuration names you defined are not consistent.
43018 – The value ‘%.*ls’ for configuration ‘%.*ls’ is not consistent with default value ‘%.*ls’.
43019 – The source ‘%.*ls’ for configuration ‘%.*ls’ is not valid.
45122 – ‘%ls’
45220 – An error occurred while configuring for the SQL Agent: error %d, severity %d, state %d.
45221 – An error occurred while configuring for the SQL Agent: error %d, severity %d, state %d.
4524 – Cannot alter view ‘%.*ls’ because it has snapshot materialization.
4525 – Cannot use hint ‘%ls’ on view ‘%.*ls’ that has snapshot materialization before the view is refreshed.
45309 – The operation could not be completed because database ‘%ls’ on server ‘%ls’ is recovering from a geo-replication role change and is not currently eligible to become a primary or standalone database. Wait until the relationship leaves the ‘SUSPENDED’ replication state and try again.
45310 – Unable to return metrics. Request would return too much data.
45311 – The server key ‘%.*ls’ already exists. Please choose a different server key name.
45312 – The server key URI ‘%.*ls’ already exists as another server key.
45313 – The server key ‘%.*ls’ cannot be deleted because it is currently in use by ‘%.*ls’.
45314 – Server key ‘%.*ls’ does not exist. Make sure that the server key name is entered correctly.
45315 – The operation could not be completed because a service objective assignment is in progress for database ‘%.*ls’ on server ‘%.*ls.’ Wait for the service objective assignment to complete and try again.
45316 – MODIFY FILE failed. Size is greater than MAXSIZE. Please query sys.database_files and use DBCC SHRINKFILE to reduce the file size first.
45317 – Server ‘%.*ls’ does not exist in resource group ‘%.*ls’ in subscription ‘%.*ls’.
45318 – Service Fabric Application Version is not available.
45319 – The service objective assignment for database ‘%.*ls’ on server ‘%.*ls’ could not be completed as the database is too busy. Reduce the workload before initiating another service objective update.
45320 – The operation could not be completed on server ‘%.*ls’ because the Azure Key Vault key ‘%.*ls’ is disabled.
45321 – The operation could not be completed on server ‘%.*ls’ because attempts to connect to Azure Key Vault ‘%.*ls’ have failed
45322 – The operation could not be completed because the Azure Key Vault key ‘%.*ls’ expiration date is invalid.
45323 – Unable to start a copy because the source database ‘%ls’ is being updated.
45324 – The operation could not be completed because the Azure Key Vault Uri is null or empty.
45325 – The operation could not be completed because the Azure Key Vault Key name is null or empty.
45326 – The operation could not be completed because the Azure Key Vault Key name ‘%ls’ does not exist.
45327 – The operation could not be completed because the Azure Key Vault Key name ‘%ls’ is currently set as server encryption protector.
45328 – The server identity is not correctly configured on server ‘%ls’. Please contact support.
45329 – An invalid response from Azure Key Vault. Please use a valid Azure Key Vault URI.
45330 – The server ‘%ls’ requires the following Azure Key Vault permisions: ‘%ls’. Please grant the missing permissions to the service principal with ID ‘%ls’.
45331 – The operation could not be completed because the read scale value specified is not supported for a ‘%ls’ database.
45332 – The operation could not be completed because the read scale value specified is invalid.
45333 – The service request timed out. %ls.
45334 – Server edition ‘%ls’ is invalid.
45335 – Server type ‘%ls’ is invalid.
45336 – The operation could not be completed because ‘%ls’ is an invalid Server Key name. Please provide a key name in the format of ‘vault_key_version’. For example, if the keyId is https://YourVaultName.vault.azure.net/keys/YourKeyName/01234567890123456789012345678901, then the Server Key Name should be formatted as: YourVaultName_YourKeyName_01234567890123456789012345678901.
45337 – The planned failover operation has rolled back because database ‘%ls’ could not be synchronized with its remote partner. This may be due to a service outage, or to a high volume of write traffic. Consider using forced failover.
45338 – The planned failover operation has rolled back because the remote server ‘%ls’ could not be reached. This may be due to a service outage. Consider using forced failover.
45339 – The max size update on the geo-secondary database ‘%ls’ on server ‘%ls’ failed with reason ‘%ls’.
45340 – The operation could not be completed because an Azure Active Directory error was encountered. The error message from Active Directory Authentication library (ADAL) is ‘%ls’.
45341 – The operation could not be completed because an error was encountered when attempting to retrieve Key Vault information for ‘%ls’ from server ‘%ls’. The encountered error message is ‘%ls’.
45342 – The operation could not be completed because an Azure Active Directory error was encountered. Please ensure the server ‘%ls’ and key vault ‘%ls’ belong to the same tenant. The error message from Active Directory Authentication library is ‘%ls’.
45343 – The provided Key Vault uri ‘%ls’ is not valid. Please ensure the uri contains the vault, key, and key version information. An example valid uri looks like ‘https://YourVaultName.vault.azure.net/keys/YourKeyName/01234567890123456789012345678901’. Please ensure the vault belongs to an endpoint from the list of supported endpoints available at ‘%ls’.
4632 – The permission ‘%.*ls’ is not supported in this version of SQL Server. Alternatively, use the %.*ls level ‘%.*ls’ permission.
46527 – Altering the ‘%S_MSG’ property is not permitted for an external data source of type %ls.
46628 – SHARED_MEMORY
46654 – database scoped credential
46723 – Large object column in Global Query is not supported for types other than Nvarchar(MAX), Varchar(MAX), Varbinary(MAX) and Image.
46829 – The proc %.*ls is only supported for external data sources of type SHARD_MAP_MANAGER or RDBMS.
46830 – Internal column references are not supported for external tables.
46917 – An internal error occured while attempting to retrieve the encrypted database-scoped credential secret.
46918 – An internal error occured while attempting to retrieve the encrypted database-scoped credential secret.
46919 – An internal error occured while attempting to retrieve the encrypted database-scoped credential secret.
47049 – Reason: VNET Firewall Rule has rejected the login.
47050 – Reason: Unexpected error while copying the VNET IPv6 address.
47051 – Reason: Unexpected error while parsing the VNET IPv6.
47052 – Reason: Unexpected error while extracting VNET metadata info from the IPv6 (VNET parsing).
47053 – Reason: Unexpected error on VNET firewall rule table lookup.
47054 – Reason: Unexpected error on VNET firewall rule table COUNT lookup.
47055 – Reason: VNET Firewall rejected the login due to a the source of a login being outside a VNET
47056 – Reason: Firewall rejected the login attempt because VNET firewall rules are not database-level, only server-level.
47057 – Reason: Firewall rejected the login because an IPv6 attempt was received when not expected
47058 – Reason: Unexpected error on VNET firewall rule table lookup while looking up the IPv4 Allow All rule.
47100 – The cluster type of availability group ‘%.*ls’ doesn’t match its primary configuration. Verify that the specified availability group cluster type is correct, then retry the operation.
47101 – The cluster type of availability group ‘%.*ls’ only supports MANUAL failover mode. Verify that the specified availability group cluster type is correct, then retry the operation.
47102 – The cluster type of availability group ‘%.*ls’ only supports EXTERNAL failover mode. Verify that the specified availability group cluster type is correct, then retry the operation.
47103 – The cluster type of availability group ‘%.*ls’ only supports AUTOMATIC and MANUAL failover modes. Verify that the specified availability group cluster type is correct, then retry the operation.
47104 – This operation cannot be performed on the availability group ‘%.*ls’ because it has EXTERNAL cluster type. Use the cluster management tools to perform the operation.
47105 – The Always On Availability Groups feature must be enabled for this server instance before you can perform availability group operations. Please enable the feature, then retry the operation.
47106 – Cannot join availability group ‘%.*ls’. Download configuration timeout. Please check primary configuration, network connectivity and firewall setup, then retry the operation.
47107 – The %ls operation is not allowed by the current availability group configuration. The availability group ‘%.*ls’ only supports one relica which has witness availability mode. Verify that the specified availability group availability mode is correct, then retry the operation.
47108 – The %ls operation is not allowed by the current availability group configuration. The availability group ‘%.*ls’ only supports two synchronous mode replicas and required_syncrhonized_secondaries_to_commit is zero when witness mode replica is specified. Verify that the specified availability group availability mode is correct, then retry the operation.
47109 – Availability group ‘%.*ls’ cannot failover to this replica. Witness replica cannot become primary. An attempt to fail over an availability group failed. The replica is a witness and cannot become a primary.
47110 – The ‘%.*ls’ option is not valid for the ‘%.*ls’ replica as it is a witness. Remove this option, then retry the operation
47111 – Local availability replica for availability group ‘%.*ls’ cannot be granted permission to create databases. The replica is a witness and cannot host databases inside the availability group.
4878 – Invalid quote character specified for bulk load. Quote character can be one single byte or Unicode character.
4879 – Bulk load failed due to invalid column value in CSV data file %ls in row %d, column %d.
4898 – Cannot bulk load CSV file. Invalid field parameters are specified for source column number %d in the format file “%s”. All data fields must be either character or Unicode character with terminator when CSV format is specified.
4899 – Cannot bulk load CSV file. Invalid terminator is specified for source column number %d in the format file “%s”. All source column terminators must be the same except the last one when CSV format is specified. Only row terminator should be different.
49401 – Database backup not supported on this database as it has foreign files attached.
49501 – DBCC SHRINKFILE for %.*ls is aborted. Sbs flat files are not supported
49502 – %.*ls: Page %d:%d could not be moved because it is an sbs flat file page or the destination is an sbs flat file page.
49600 – SQL tiered storage table schema is corrupt.
49601 – SBS Flat File Access not fully initialized, when function ‘%ls’ was called.
49602 – Failure waiting for %ls latch in ‘%ls’.
49814 – DynamicActivation is not supported for GeoDR DB: Logical Server ‘%.*ls’, Database ‘%.*ls’, ServiceLevelObjective ‘%.*ls’
49815 – Database cannot be deactivated: Server ‘%.*ls’, Database ‘%.*ls’, ServiceLevelObjective ‘%.*ls’
49816 – Server ‘%.*ls’, Database ‘%.*ls’ cannot be online as BlockingMode is set
49817 – Failed to query CMS for thottling on database ‘%.*ls’, ‘%.*ls’ due to the exception: ‘%.*ls’
49818 – Cannot deactivate a database when it is already getting deactivated, Server ‘%.*ls’, Database ‘%.*ls’
49819 – Deflation Monitor Timer task encountered an error (SQL Error Code: %d). Refer to the xel for more details.
49926 – Server setup is starting
49927 – An error occurred while setting the server administrator (SA) password: error %d, severity %d, state %d.
49928 – An error occurred during server setup. See previous errors for more information.
49929 – Server setup completed successfully.
49930 – Parallel redo is %ls for database ‘%.*ls’ with worker pool size [%d].
49931 – An error occurred while configuring engine telemetry: error %d, severity %d, state %d.
49932 – An error occurred while initializing security. %ls.
49933 – ERROR: The MSSQL_SA_PASSWORD environment variable must be set when using the –reset-sa-password option.
49934 – Error %u occurred while reading the RbIo configuration parameters. Verify that the sqlservr.ini or registry entries exist.
49936 – ERROR: The provided PID [%s] is invalid. The PID must be in the form #####-#####-#####-#####-##### where ‘#’ is a number or letter.
49937 – ERROR: A failure occurred in the licensing subsystem. Error [%d].
49938 – The licensing PID was successfully processed. The new edition is [%s].
49939 – Unable to initialize user-specified certificate configuration. The server is being shut down. Verify that the certificate is correctly configured. Error[%d]. State[%d].
49940 – Unable to open one or more of the user-specified certificate file(s). Verify that the certificate file(s) exist with read permissions for the user and group running SQL Server.
49941 – Unable to load one or more of the user-specified certificate file(s). Verify that the certificate file(s) are of a supported format.
49942 – Internal error occurred initializing user-specified certificate configuration. Error code [%08X].
49943 – The certificate [Certificate File:’%hs’, Private Key File:’%hs’] was successfully loaded for encryption. This will override any self-generated certificate.
49944 – The allowed TLS protocol version list [‘%hs’] is invalid. Verify that the supplied TLS version numbers are supported by SQL Server and separated by spaces in the configuration.
49945 – The allowed TLS cipher list [‘%hs’] is invalid. See docs.microsoft.com for more information on creating a cipher list.
49946 – Internal error occurred initializing the TLS configuration. Error code [%d].
49947 – Unable to initialize the TLS configuration. The server is being shut down. Verify that the allowed TLS protocol and cipher lists are configured correctly. Error state [%d].
49948 – Successfully initialized the TLS configuration. Allowed TLS protocol versions are [‘%hs’]. Allowed TLS ciphers are [‘%hs’].
49949 – ERROR: Unable to set system administrator password: %s.
49950 – The SQL Server End-User License Agreement (EULA) must be accepted before SQL
49951 – Server can start. The license terms for this product can be downloaded from
49952 – http://go.microsoft.com/fwlink/?LinkId=746388.
49953 – You can accept the EULA by specifying the –accept-eula command line option,
49954 – setting the ACCEPT_EULA environment variable, or using the mssql-conf tool.
49955 – Environment Variable Startup Parameters:%.*ls
49956 – The default language (LCID %d) has been set for engine and full-text services.
49957 – The default language (LCID %d) failed to be set for engine and full-text services.
49958 – The server collation cannot be changed with user databases attached. Please detach user databases before changing server collation. (Note from Brent – whoa, wonder if that’s Linux-only.)
49600 – SQL tiered storage table schema is corrupt.
49959 – ERROR: The environment variable MSSQL_COLLATION contains an invalid collation ‘%.*ls’.
5338 – Format option cannot be specified together with SINGLE_BLOB, SINGLE_CLOB or SINGLE_NCLOB option.
5339 – CSV format option is supported for char and widechar datafiletype options.
5545 – FILESTREAM: connected to kernel driver %ls. This is an informational message. No user action is required.
5546 – FILESTREAM: failed to connect to kernel driver %ls.
5576 – FILESTREAM feature is enabled. This is an informational message. No user action is required.
5577 – FILESTREAM access level has been changed to %d. Restart the instance of SQL Server for the settings to fully take effect.
5585 – FILESTREAM file I/O access could not be enabled. The operating system Administrator must enable FILESTREAM file I/O access on the instance using Configuration Manager.
5867 – Changing AFFINITY is not supported when the sql server is running in agnostic affinity mode. (Note from Brent – COME ON, dammit, it’s SQL Server, not sql server.)
694 – Internal error. Unable to create a block blob in azure storage.
695 – Internal error. Unable to delete a block blob in azure storage.
696 – Internal error. Unable to gain access to the tombstone table.
697 – Internal error. Unable to get a blob storage container.
8571 – SQL Server is unable to get outcome from Microsoft Distributed Transaction Coordinator (MS DTC) for the transaction with UOW ‘%ls’ because another resource manager with same RMID already exists.
8716 – Invalid data was detected. An external producer has inserted a value into shared memory table that is not supported by SQL Server.
8734 – Separator parameter for STRING_AGG cannot be large object type such as VARCHAR(MAX) or NVARCHAR(MAX).
8735 – The operation failed because an online index build on the table terminated and caused a temporary inconsistency. Re-run the query.
875 – Resilient buffer pool extension “%.*ls” has been started successfully with size is %I64d MB.
876 – Failed to startup resilient buffer pool extension of size %I64d MB on path “%.*ls”.
877 – Resilient buffer pool extension “%.*ls” has been enabled successfully with size %I64d MB.
878 – Failed to create resilient buffer pool extension of size %I64d MB on path “%.*ls”.
879 – Failed to start resilient buffer pool extension because database %d is not memory optimized.
880 – Failed to find the metadata of resilient buffer pool extension in database %d.
881 – Resilient buffer pool extension is already disabled. No action is necessary.
882 – The schema of a table created by InternalBaseTable is corrupt.
883 – Could not create the Write Page Recorder table: wpr_bucket_table for database %ls.
884 – Could not persist a bucket to the Write Page Recorder table: wpr_bucket_table for database %ls.
885 – An internal error occurred. Error code %X. Context: “%ls::%d”.
886 – Resilient buffer pool extension is already enabled. No action is necessary.
891 – Buffer pool extension is not supported on %ls platform.
9033 – The transaction in database ‘%ls’ has exceeded the max size of %I64u bytes. See ‘http://aka.ms/sqldw_loglimit’ for assistance.
9034 – PERSISTENT_LOG_BUFFER option is already on in database ‘%ls’.
9035 – PERSISTENT_LOG_BUFFER can only be set on the DAX volume.
9036 – The source has PERSISTENT_LOG_BUFFER enabled, but the current device is not DAX volume. PERSISTENT_LOG_BUFFER has been set to OFF.
9037 – The file ‘%.*ls’ on remote block device cannot be initialized. Native error is 0x%08x.
9038 – The VLF %lu could not be registered with the Log Leasing Service with error 0x%08X.
9039 – Could not start lease for VLF %lu for consumer ID %ls with the Log Leasing Service with error 0x%08X.
9040 – Could not end lease for consumer ID %ls with the Log Leasing Service with error 0x%08X.
9828 – The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.
9829 – STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

New Wait Stats and DMV Columns in SQL Server 2017

Last Updated October 2, 2017

SQL Server 2017, Wait Stats

SQL Server 2017 is officially out, and here’s what I’ve found so far.

New Wait Stats in 2017

BACKUP_INMEM_DIFFLIST_READ_ACCESS
BACKUP_INMEM_DIFFLIST_WRITE_ACCESS
BLOB_CONTAINER_TABLE
COLUMNSTORE_MIGRATION_BACKGROUND_TASK
EXTERNAL_SCRIPT_NETWORK_IO
FOREIGN_REDO
GHOSTCLEANUP_UPDATE_STATS
GLOBAL_QUERY_CANCEL
GLOBAL_QUERY_CONSUMER
GLOBAL_QUERY_PRODUCER
HADR_THROTTLE_LOG_RATE_LOG_SIZE
HADR_THROTTLE_LOG_RATE_SEEDING
HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE
MEMORY_GRANT_UPDATE
MIGRATIONBUFFER
PREEMPTIVE_OS_BCRYPTIMPORTKEY
PREEMPTIVE_OS_NCRYPTIMPORTKEY
PREEMPTIVE_XE_PROXY_ADDSESSION
PREEMPTIVE_XE_PROXY_PROCESSBUFFER
PREEMPTIVE_XE_PROXY_REMOVESESSION
PREEMPTIVE_XE_PROXY_SESSIONCOMMIT
PWAIT_AUDIT_CLOSE_EXPIRED_LOGS_MGR_RWLOCK
PWAIT_AUDIT_SESSIONS_LOCK
PWAIT_EXTERNAL_SCRIPT_LIBMGMT_DIR_PERMS
PWAIT_PREEMPTIVE_APP_USAGE_TIMER
PWAIT_PREEMPTIVE_XE_STUB_LISTENER
PWAIT_SBS_FILE_OPERATION
QDS_HOST_INIT
RBIO_WAIT_VLF
REMOTE_BLOCK_IO
RESTORE_MSDA_THREAD_BARRIER
SBS_DISPATCH
SBS_RECEIVE_TRANSPORT
SBS_TRANSPORT
SECURITY_CNG_PROVIDER_MUTEX
SLEEP_SETUP
SNI_WRITE_ASYNC
TEMPORAL_BACKGROUND_PROCEED_CLEANUP
UCS_CNG_PROVIDER_MUTEX
WAIT_XLOGREAD_SIGNAL
WAIT_XTP_COMPILE_WAIT
WAIT_XTP_SERIAL_RECOVERY
XE_STUBMGR_CLOSE
XIO_EDS_MGR_RWLOCK
XIO_EDS_RWLOCK
XIO_IOSTATS_BLOBLIST_RWLOCK
XIO_IOSTATS_FCBLIST_RWLOCK

New Management Objects & Columns in 2017

I’m doing a join between a 2017 RTM server’s sys.all_columns over to a 2016 SP1 server’s, and looking for the newly added rows. I’m hyperlinking to relevant documentation that you might find interesting, too.

Internal tables & columns:

_trusted_assemblies.create_date
_trusted_assemblies.created_by
_trusted_assemblies.description
_trusted_assemblies.hash
plan_persist_query.query_flags
plan_persist_query_hints.batch_sql_handle
plan_persist_query_hints.comment
plan_persist_query_hints.context_settings_id
plan_persist_query_hints.last_query_hint_failure_reason
plan_persist_query_hints.object_id
plan_persist_query_hints.query_hash
plan_persist_query_hints.query_hint_failure_count
plan_persist_query_hints.query_hint_id
plan_persist_query_hints.query_hints_flags
plan_persist_query_hints.query_hints
plan_persist_query_hints.query_id
plan_persist_query_hints.query_param_type
plan_persist_query_hints.statement_sql_handle
plan_persist_query_template_parameterization.comment
plan_persist_query_template_parameterization.last_parameterization_failure_reason
plan_persist_query_template_parameterization.parameterization_failure_count
plan_persist_query_template_parameterization.query_param_type
plan_persist_query_template_parameterization.query_template_flags
plan_persist_query_template_parameterization.query_template_hash
plan_persist_query_template_parameterization.query_template_id
plan_persist_query_template_parameterization.query_template
plan_persist_query_text.query_template_hash
plan_persist_wait_stats.count_executions
plan_persist_wait_stats.execution_type
plan_persist_wait_stats.last_query_wait_time_ms
plan_persist_wait_stats.max_query_wait_time_ms
plan_persist_wait_stats.min_query_wait_time_ms
plan_persist_wait_stats.plan_id
plan_persist_wait_stats.runtime_stats_interval_id
plan_persist_wait_stats.sumsquare_query_wait_time_ms
plan_persist_wait_stats.total_query_wait_time_ms
plan_persist_wait_stats.wait_category
plan_persist_wait_stats.wait_stats_id

Inline table-valued functions:

dm_db_log_info.database_id
dm_db_log_info.file_id
dm_db_log_info.vlf_active
dm_db_log_info.vlf_begin_offset
dm_db_log_info.vlf_create_lsn
dm_db_log_info.vlf_first_lsn
dm_db_log_info.vlf_parity
dm_db_log_info.vlf_sequence_number
dm_db_log_info.vlf_size_mb
dm_db_log_info.vlf_status
dm_db_log_stats.active_log_size_mb
dm_db_log_stats.active_vlf_count
dm_db_log_stats.current_vlf_sequence_number
dm_db_log_stats.current_vlf_size_mb
dm_db_log_stats.database_id
dm_db_log_stats.log_backup_lsn
dm_db_log_stats.log_backup_time
dm_db_log_stats.log_checkpoint_lsn
dm_db_log_stats.log_end_lsn
dm_db_log_stats.log_min_lsn
dm_db_log_stats.log_recovery_lsn
dm_db_log_stats.log_recovery_size_mb
dm_db_log_stats.log_since_last_checkpoint_mb
dm_db_log_stats.log_since_last_log_backup_mb
dm_db_log_stats.log_truncation_holdup_reason
dm_db_log_stats.recovery_model
dm_db_log_stats.recovery_vlf_count
dm_db_log_stats.total_log_size_mb
dm_db_log_stats.total_vlf_count
dm_os_enumerate_filesystem.creation_time
dm_os_enumerate_filesystem.file_or_directory_name
dm_os_enumerate_filesystem.full_filesystem_path
dm_os_enumerate_filesystem.has_integrity_stream
dm_os_enumerate_filesystem.is_directory
dm_os_enumerate_filesystem.is_hidden
dm_os_enumerate_filesystem.is_read_only
dm_os_enumerate_filesystem.is_sparse
dm_os_enumerate_filesystem.is_system
dm_os_enumerate_filesystem.is_temporary
dm_os_enumerate_filesystem.last_access_time
dm_os_enumerate_filesystem.last_write_time
dm_os_enumerate_filesystem.level
dm_os_enumerate_filesystem.parent_directory
dm_os_enumerate_filesystem.size_in_bytes
dm_os_file_exists.file_exists
dm_os_file_exists.file_is_a_directory
dm_os_file_exists.parent_directory_exists
dm_sql_referenced_entities.is_incomplete
fn_full_dblog.AllocUnitId
fn_full_dblog.AllocUnitName
fn_full_dblog.Article ID
fn_full_dblog.Begin Time
fn_full_dblog.Beginlog Status
fn_full_dblog.Bulk allocated extent count
fn_full_dblog.Bulk allocated extent ids
fn_full_dblog.Bulk allocation first IAM Page ID
fn_full_dblog.Bulk AllocUnitId
fn_full_dblog.Bulk RowsetId
fn_full_dblog.Byte Offset
fn_full_dblog.Bytes Freed
fn_full_dblog.Checkpoint Begin
fn_full_dblog.Checkpoint End
fn_full_dblog.CHKPT Begin DB Version
fn_full_dblog.CHKPT End DB Version
fn_full_dblog.CI Index Id
fn_full_dblog.CI Table Id
fn_full_dblog.Column Offset
fn_full_dblog.Command Type
fn_full_dblog.Command
fn_full_dblog.Compression Info
fn_full_dblog.Compression Log Type
fn_full_dblog.Context
fn_full_dblog.CopyVerionInfo Source Page Id
fn_full_dblog.CopyVerionInfo Source Page LSN
fn_full_dblog.CopyVerionInfo Source Slot Count
fn_full_dblog.CopyVerionInfo Source Slot Id
fn_full_dblog.Current LSN
fn_full_dblog.Database Name
fn_full_dblog.Description
fn_full_dblog.Dirty Pages
fn_full_dblog.End AGE
fn_full_dblog.End Time
fn_full_dblog.File ID
fn_full_dblog.File Status
fn_full_dblog.FileGroup ID
fn_full_dblog.Flag Bits
fn_full_dblog.Flags
fn_full_dblog.Format LSN
fn_full_dblog.InvalidateCache Id
fn_full_dblog.InvalidateCache keys
fn_full_dblog.Last Distributed Backup End LSN
fn_full_dblog.Last Distributed End LSN
fn_full_dblog.Lock Information
fn_full_dblog.Log Record Fixed Length
fn_full_dblog.Log Record Length
fn_full_dblog.Log Record
fn_full_dblog.Log Reserve
fn_full_dblog.LogBlockGeneration
fn_full_dblog.Logical Name
fn_full_dblog.LSN before writes
fn_full_dblog.Mark Name
fn_full_dblog.Master DBID
fn_full_dblog.Master XDESID
fn_full_dblog.Max XDESID
fn_full_dblog.Meta Status
fn_full_dblog.Minimum LSN
fn_full_dblog.Modify Size
fn_full_dblog.New Size
fn_full_dblog.New Split Page
fn_full_dblog.New Value
fn_full_dblog.NewAllocUnitId
fn_full_dblog.Next Replicated End LSN
fn_full_dblog.Num Elements
fn_full_dblog.Num Transactions
fn_full_dblog.Number of Locks
fn_full_dblog.Offset in Row
fn_full_dblog.Offset
fn_full_dblog.Old Size
fn_full_dblog.Old Value
fn_full_dblog.Oldest Active LSN
fn_full_dblog.Oldest Active Transaction ID
fn_full_dblog.Oldest Replicated Begin LSN
fn_full_dblog.Operation
fn_full_dblog.Page ID
fn_full_dblog.PageFormat FormatOption
fn_full_dblog.PageFormat PageFlags
fn_full_dblog.PageFormat PageLevel
fn_full_dblog.PageFormat PageStat
fn_full_dblog.PageFormat PageType
fn_full_dblog.Pages Written
fn_full_dblog.Parent Transaction ID
fn_full_dblog.Partial Status
fn_full_dblog.PartitionId
fn_full_dblog.Physical Name
fn_full_dblog.Prepare Time
fn_full_dblog.Preplog Begin LSN
fn_full_dblog.Previous LSN
fn_full_dblog.Previous Page LSN
fn_full_dblog.Previous Savepoint
fn_full_dblog.Publication ID
fn_full_dblog.Repl CSN
fn_full_dblog.Repl Epoch
fn_full_dblog.Repl Flags
fn_full_dblog.Repl Min Hold LSN
fn_full_dblog.Repl Msg
fn_full_dblog.Repl Partition ID
fn_full_dblog.Repl Source Commit Time
fn_full_dblog.Replicated Records
fn_full_dblog.Rowbits Bit Count
fn_full_dblog.Rowbits Bit Value
fn_full_dblog.Rowbits First Bit
fn_full_dblog.RowFlags
fn_full_dblog.RowLog Contents 0
fn_full_dblog.RowLog Contents 1
fn_full_dblog.RowLog Contents 2
fn_full_dblog.RowLog Contents 3
fn_full_dblog.RowLog Contents 4
fn_full_dblog.RowLog Contents 5
fn_full_dblog.Rows Deleted
fn_full_dblog.RowsetId
fn_full_dblog.Savepoint Name
fn_full_dblog.Server Name
fn_full_dblog.Server UID
fn_full_dblog.Slot ID
fn_full_dblog.SPID
fn_full_dblog.Tag Bits
fn_full_dblog.Text Size
fn_full_dblog.TextPtr
fn_full_dblog.Transaction Begin
fn_full_dblog.Transaction ID
fn_full_dblog.Transaction Name
fn_full_dblog.Transaction SID
fn_full_dblog.Virtual Clock
fn_full_dblog.VLFs added
fn_full_dblog.Xact ID
fn_full_dblog.Xact Node ID
fn_full_dblog.Xact Node Local ID
fn_full_dblog.Xact Type
fn_get_audit_file.affected_rows
fn_get_audit_file.application_name
fn_get_audit_file.client_ip
fn_get_audit_file.duration_milliseconds
fn_get_audit_file.response_rows
fn_xe_file_target_read_file.timestamp_utc

System Tables:

syscscolsegments.container_id
syscsdictionaries.container_id

User Tables:

MSreplication_options.install_failures
MSreplication_options.major_version
MSreplication_options.minor_version
MSreplication_options.optname
MSreplication_options.revision
MSreplication_options.value
spt_monitor.connections
spt_monitor.cpu_busy
spt_monitor.idle
spt_monitor.io_busy
spt_monitor.lastrun
spt_monitor.pack_errors
spt_monitor.pack_received
spt_monitor.pack_sent
spt_monitor.total_errors
spt_monitor.total_read
spt_monitor.total_write

Views:

all_columns.graph_type_desc
all_columns.graph_type
availability_groups.cluster_type_desc
availability_groups.cluster_type
availability_groups.required_synchronized_secondaries_to_commit
availability_groups.sequence_number
columns.graph_type_desc
columns.graph_type
computed_columns.graph_type_desc
computed_columns.graph_type
database_automatic_tuning_mode.actual_state_desc – not a GUI for this just yet, but you can change a database’s tuning mode with ALTER DATABASE SET commands. Right now it’s about forcing the last good plan in the event that you hit a parameter sniffing issue.
database_automatic_tuning_mode.actual_state
database_automatic_tuning_mode.desired_state_desc
database_automatic_tuning_mode.desired_state
database_automatic_tuning_options.actual_state_desc
database_automatic_tuning_options.actual_state
database_automatic_tuning_options.desired_state_desc
database_automatic_tuning_options.desired_state
database_automatic_tuning_options.name
database_automatic_tuning_options.reason_desc
database_automatic_tuning_options.reason
database_credentials.principal_id
database_files.is_persistent_log_buffer
database_query_store_options.wait_stats_capture_mode_desc
database_query_store_options.wait_stats_capture_mode
database_scoped_configurations.is_value_default
database_scoped_credentials.principal_id
databases.is_temporal_history_retention_enabled
dm_db_column_store_row_group_physical_stats.closed_time
dm_db_column_store_row_group_physical_stats.created_time
dm_db_column_store_row_group_physical_stats.deleted_rows
dm_db_column_store_row_group_physical_stats.delta_store_hobt_id
dm_db_column_store_row_group_physical_stats.generation
dm_db_column_store_row_group_physical_stats.has_vertipaq_optimization
dm_db_column_store_row_group_physical_stats.index_id
dm_db_column_store_row_group_physical_stats.object_id
dm_db_column_store_row_group_physical_stats.partition_number
dm_db_column_store_row_group_physical_stats.row_group_id
dm_db_column_store_row_group_physical_stats.size_in_bytes
dm_db_column_store_row_group_physical_stats.state_desc
dm_db_column_store_row_group_physical_stats.state
dm_db_column_store_row_group_physical_stats.total_rows
dm_db_column_store_row_group_physical_stats.transition_to_compressed_state_desc
dm_db_column_store_row_group_physical_stats.transition_to_compressed_state
dm_db_column_store_row_group_physical_stats.trim_reason_desc
dm_db_column_store_row_group_physical_stats.trim_reason
dm_db_file_space_usage.modified_extent_page_count
dm_db_mirroring_connections.address
dm_db_tuning_recommendations.details
dm_db_tuning_recommendations.execute_action_duration
dm_db_tuning_recommendations.execute_action_initiated_by
dm_db_tuning_recommendations.execute_action_initiated_time
dm_db_tuning_recommendations.execute_action_start_time
dm_db_tuning_recommendations.is_executable_action
dm_db_tuning_recommendations.is_revertable_action
dm_db_tuning_recommendations.last_refresh
dm_db_tuning_recommendations.name
dm_db_tuning_recommendations.reason
dm_db_tuning_recommendations.revert_action_duration
dm_db_tuning_recommendations.revert_action_initiated_by
dm_db_tuning_recommendations.revert_action_initiated_time
dm_db_tuning_recommendations.revert_action_start_time
dm_db_tuning_recommendations.score
dm_db_tuning_recommendations.state
dm_db_tuning_recommendations.type
dm_db_tuning_recommendations.valid_since
dm_db_xtp_checkpoint_internals.checkpoint_id
dm_db_xtp_checkpoint_internals.checkpoint_timestamp
dm_db_xtp_checkpoint_internals.is_synchronized
dm_db_xtp_checkpoint_internals.last_segment_lsn
dm_db_xtp_checkpoint_internals.recovery_lsn
dm_exec_query_stats.last_columnstore_segment_reads
dm_exec_query_stats.last_columnstore_segment_skips
dm_exec_query_stats.max_columnstore_segment_reads
dm_exec_query_stats.max_columnstore_segment_skips
dm_exec_query_stats.min_columnstore_segment_reads
dm_exec_query_stats.min_columnstore_segment_skips
dm_exec_query_stats.total_columnstore_segment_reads
dm_exec_query_stats.total_columnstore_segment_skips
dm_exec_requests.is_resumable
dm_os_enumerate_fixed_drives.drive_type_desc
dm_os_enumerate_fixed_drives.drive_type
dm_os_enumerate_fixed_drives.fixed_drive_path
dm_os_enumerate_fixed_drives.free_space_in_bytes
dm_os_host_info.host_distribution
dm_os_host_info.host_platform
dm_os_host_info.host_release
dm_os_host_info.host_service_pack_level
dm_os_host_info.host_sku
dm_os_host_info.os_language_version
dm_os_memory_objects.partition_type_desc
dm_os_nodes.cpu_count
dm_os_sys_info.cores_per_socket
dm_os_sys_info.numa_node_count
dm_os_sys_info.process_physical_affinity
dm_os_sys_info.socket_count
dm_os_worker_local_storage.gq_address
dm_tran_global_transactions.max_csn
dm_tran_global_transactions_enlistments.snapshot_timestamp
dm_tran_global_transactions_log.commit_timestamp
dm_tran_version_store_space_usage.database_id
dm_tran_version_store_space_usage.reserved_page_count
dm_tran_version_store_space_usage.reserved_space_kb
dm_xe_session_targets.bytes_written
dm_xe_sessions.buffer_full_count
dm_xe_sessions.buffer_processed_count
dm_xe_sessions.total_bytes_generated
external_libraries.external_library_id
external_libraries.language
external_libraries.name
external_libraries.principal_id
external_libraries.scope_desc
external_libraries.scope
external_library_files.content
external_library_files.external_library_id
external_library_files.platform_desc
external_library_files.platform
hash_indexes.auto_created
hash_indexes.is_ignored_in_optimization
identity_columns.graph_type_desc
identity_columns.graph_type
index_resumable_operations.index_id
index_resumable_operations.last_max_dop_used
index_resumable_operations.last_pause_time
index_resumable_operations.name
index_resumable_operations.object_id
index_resumable_operations.page_count
index_resumable_operations.partition_number
index_resumable_operations.percent_complete
index_resumable_operations.sql_text
index_resumable_operations.start_time
index_resumable_operations.state_desc
index_resumable_operations.state
index_resumable_operations.total_execution_time
indexes.auto_created
indexes.is_ignored_in_optimization
indexes.suppress_dup_key_messages
key_constraints.is_enforced
masked_columns.graph_type_desc
masked_columns.graph_type
master_files.is_persistent_log_buffer
query_store_plan.plan_forcing_type_desc
query_store_plan.plan_forcing_type
query_store_runtime_stats.avg_log_bytes_used
query_store_runtime_stats.avg_num_physical_io_reads
query_store_runtime_stats.avg_tempdb_space_used
query_store_runtime_stats.last_log_bytes_used
query_store_runtime_stats.last_num_physical_io_reads
query_store_runtime_stats.last_tempdb_space_used
query_store_runtime_stats.max_log_bytes_used
query_store_runtime_stats.max_num_physical_io_reads
query_store_runtime_stats.max_tempdb_space_used
query_store_runtime_stats.min_log_bytes_used
query_store_runtime_stats.min_num_physical_io_reads
query_store_runtime_stats.min_tempdb_space_used
query_store_runtime_stats.stdev_log_bytes_used
query_store_runtime_stats.stdev_num_physical_io_reads
query_store_runtime_stats.stdev_tempdb_space_used
query_store_wait_stats.avg_query_wait_time_ms
query_store_wait_stats.execution_type_desc
query_store_wait_stats.execution_type
query_store_wait_stats.last_query_wait_time_ms
query_store_wait_stats.max_query_wait_time_ms
query_store_wait_stats.min_query_wait_time_ms
query_store_wait_stats.plan_id
query_store_wait_stats.runtime_stats_interval_id
query_store_wait_stats.stdev_query_wait_time_ms
query_store_wait_stats.total_query_wait_time_ms
query_store_wait_stats.wait_category_desc
query_store_wait_stats.wait_category
query_store_wait_stats.wait_stats_id
sequences.last_used_value
spatial_indexes.auto_created
spatial_indexes.is_ignored_in_optimization
spt_values.high
spt_values.low
spt_values.name
spt_values.number
spt_values.status
spt_values.type
syscscontainers.blob_container_id
syscscontainers.blob_container_type
syscscontainers.blob_container_url
system_columns.graph_type_desc
system_columns.graph_type
tables.history_retention_period_unit_desc
tables.history_retention_period_unit
tables.history_retention_period
tables.is_edge
tables.is_node
trusted_assemblies.create_date
trusted_assemblies.created_by
trusted_assemblies.description
trusted_assemblies.hash
xml_indexes.auto_created
xml_indexes.is_ignored_in_optimization

SQL Server 2017 Installation Screenshot Tour (Windows)

Last Updated October 2, 2017

SQL Server 2017

It’s like an unboxing, but with setup.exe. For the vast majority of setup, it’s the same as 2016:

Setup.exe opens, then click the Installation tab on the left

Pick your edition – I’m going with Developer because I’m a…wait….

Yep, Developer Edition still phones home and you can’t turn it off

Feature selection – I’ll take most of the engine goodies

Configuring service accounts, Instant File Initialization isn’t checked by default

Mommas don’t let their babies put their files on the C drive

TempDB data & log files can finally default to over the size of a USB thumb drive! Nice little warning popup about how you might have a slow setup experience though

Arrr, matey. After you click Accept, it sits here for a while before Next is enabled, then you have to click that too

Same thing with the Python install – accept, wait, next

This “CPU action” (or lack thereof) has always been the slowest part of install for me

More hot CPU action (stuck here for minutes)

The 2017 installer was voted Most Likely to Succeed

And you’re done! Time to reboot, then start querying DMVs looking for surprise gifts.

Don’t forget to fire up Configuration Manager and enable TCP/IP

[Video] Office Hours 2017/09/27 (With Transcriptions)

Last Updated October 1, 2017

SQL Server, Videos

This week, Richie and Erik discuss altering database columns, separating log files, encryption, NOLOCK issues, developing new skills and experience, troubleshooting deadlocks, Oracle, calling C# method from a stored procedure and the type of headset Erik uses.