A Fine Slice Of SQL Server
Kevin Hill reads the default trace to figure out who dropped a database:
We have a staging server that has a database for each client that we can test on before working in their production databases. Recently one has gone missing a few times…but nobody will own up to it.
If you want to set up an extended event to capture this information to disk separately, Jason Brimhall has you covered.
Comments closedSajib Mahmood gives various methods for debugging Spark applications running on an HDInsight cluster:
Spark Application Master
To access Spark UI for the running application and get more detailed information on its execution use the Application Master link and navigate through different tabs containing more information on jobs, stages, executors and so on.
These methods also apply for on-prem Spark clusters, although the resource locations might be a little different.
Comments closedErik Darling looks at a new DMV in vNext CTP 1.1:
It’s not exactly perfect
For instance, if you just let it loose without filters, you get a severe error. The same thing happens if you try to filter on one of the columns in the function, rather than a column in sys.stats, like this.
Very cool. It’s one step closer to us removing our dependencies on DBCC SHOW_STATISTICS.
Comments closedKendra Little gives thoughts on getting to zero-downtime releases:
I’ve worked in a bunch of environments where we rolled schema changes to production throughout the week, and worked hard to limit downtime. Our biggest challenges were:
The code released wouldn’t have the intended effect, and multiple hotfixes would sometimes need to be written quickly if the issue was urgent.
Problems would happen with replication in production – either replication would be delayed because a lot of modifications were occurring, or a change in an “upstream” system would cause a huge amount of data processing to happen in a “downstream” database
I’m in a ZDT environment. There’s a pretty good amount of overhead work that goes into getting there; there are times in which a 20 minute window would have saved hours of development time, so it’s important to keep that trade-off in mind.
Comments closedRK Kuppala explains how to integrate a Hadoop cluster with Active Directory:
This post explains kerberizing an existing Hadoop cluster using Ambari. Kerberos helps with the
Authenticationpart of enterprise security (whileauthorization,auditinganddata protectionbeing the remaining parts).HDP uses Kerberos, which is an industry standard for authenticate users and resources and providing strong identity for users. Apache Ambari can kerberize an existing cluster by using an existing MIT key distribution center (KDC) or Microsoft’s Active Directory.
This was a lot easier than I expected.
Comments closedDavid Alcock has a script to create SQL Agent alerts for common errors:
These alerts cover a range of errors from potential IO subsystem problems to failed logins, all of which are things a DBA needs to know about, and quickly too.As well as error notifications you can set up alerts to cover performance conditions. The final statement in the script below sets up an alert that triggers when Page Life Expectancy drops below 1000. In all honesty I don’t set up these performance alerts that often but I wanted to show you the kind of thing that is possible and would be handy if you don’t have any third party monitoring.
He follows this up with a post on appropriate response:
But what do I mean by sensible? Typically I see a number of problems with alerting setups; either alerts are inadequate and don’t cover the necessary errors (or there are none at all) but I also see the notifications to alerts not being set up correctly meaning problems go backwards and forwards delaying any fixes.The other problem I see is an over provision of alerts. This usually is because one or more other monitoring systems have been deployed and error notifications have been duplicated as a result. Imagine having an operational tool like System Centre, some SQL monitoring software and native alerting all pinging the same message to the one recipient mailbox. Now on top of that let’s say the alerts have not been configured correctly so information emails are being issued every second. It’s a scary thought but it is easy to see how a critical error might be missed in this scenario.
If you don’t have automatic alerts for high-severity errors, this is an easy way of gaining insight into the problems your server is experiencing.
Comments closedKevin Hill gets into Powershell:
What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.
Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉
One bit I’d recommend is using Out-GridView for occasional display and potentially getting rid of the Excel requirement.
1 CommentAndrew Peterson shows how to start, stop, and restart the SQL Server service on Linux:
Start Service
sudo systemctl start mssql-server
He also shows how to do a status check. This is for distributions which use systemd, which includes the Red Hat distribution set (Fedora, CentOS, Red Hat Enterprise). If you’re on Ubuntu, there’s no support quite yet, but you can use start and stop.
Comments closedKenneth Fisher shows that the Data Migration Assistant developers thought ahead:
I’ve been really excited about the new Data Migration Assistant (DMA) since I first heard about it. One of the things I like best about it is that unlike the old Upgrade Advisor it doesn’t have to be run on the server being upgraded. You can run it against any number of instances from a single workstation. The other day I was working from home and tried running the DMA against a couple of moderate size databases (about 1.25tb total) and I consistently got timeout errors.
Click through for the solution. I’d prefer “have the queries be quick enough not to require this change” be the solution, but I don’t know exactly which queries they’re using, and some DMVs/DMFs can be quite slow.
Comments closedPeter Schott extends a script to clean up the SSIS catalog database:
I really appreciate what MS has done w/ the SSIS Catalog. We have built-in logging at a level that wasn’t possible in prior releases, but that comes at a cost. The default retention is pretty high (365 days) and MS chose to handle cleanup using cascading deletes. This sort of coding makes life easier for the developers, but almost always performs poorly. That’s especially the case when you have 1 parent row with 100’s of thousands or child rows related to it. The problem is compounded because the first time people realize they need to lower the retention limit is about the same time that the database is starting to fill up or has filled up. At that point, it’s too late to lower the retention by a large number because the corresponding delete will still cause issues.
Click through for a script which helps extricate you from sticky situations. The ideal scenario here would be to set your retention period correctly and not have to delete rows directly, but sometimes you’re stuck in a less-than-ideal situation.
Comments closed