A Fine Slice Of SQL Server
Dave Mason shows how to build your own SQL Server Alerts:
You can mimic some (maybe all?) Alerts via Queues, Services, and Event Notifications. For instance, this script will create Event Notifications forEVENTLOG events.
It’s an interesting start to a solution, but Dave is left with a few questions and a brick wall. I’d be interested to see if there are any answers to be found here.
Comments closedTom Roush discusses gotchas around Microsoft’s Data Protection Manager:
You’ve got DPM installed, and for the most part, configured. It’s working, but you have transaction log drives filling up on some of your servers, and it’s not really clear why.
Wanna know why?
Here’s the answer:
It’s because the UI is very unclear, because the documentation is unclear, (there was a hint of it on page 83) and because the things that would be obvious to a DBA simply aren’t mentioned.
Tom has a very detailed post on the topic, making it a must-read if you use this tool.
Comments closedKenneth Fisher discusses database snapshots:
Here is where it starts getting interesting. A snapshot initially takes up little to no space. As changes are made to the source database the snapshot grows in size. In fact the snapshot is the size of all of the pages changed in the source database since the creation of the snapshot. Basically as a page is changed in the source database a copy of the original page is made and stored in the snapshot, but only the first time. (Note: The files used to store these pages are called sparse files.) This means that if you change the same page over and over again it will only be written to the snapshot once. It then logically follows that the largest a snapshot can get is the size of the source database at the time the snapshot was taken. Since most of the time we change a very small portion of the database at any given point in time this means that snapshots tend to be much smaller than the source database. In fact you could load millions of rows into the source database (assuming they are mostly/all in new pages) and it will have little to no effect on the size of the snapshot.
My favorite use of database snapshots was so developers could test their changes in QA and then revert back to a pre-snapshot environment. That way, they could preserve data for future runs.
Comments closedKendra Little notes that indirect checkpointing is now the default in SQL Server 2016:
SQL Server 2016 introduces big new features, but it also includes small improvements as well. Many of these features are described in the “It Just Runs Faster” series of blog posts by Bob Ward and Bob Dorr.
One article in this series explained that new databases created in SQL Server 2016 will use “Indirect Checkpoint” by default. Indirect checkpoint was added in SQL Server 2012, but has not previously been enabled by default for new databases. The article emphasizes this point:
Check out the comments as well.
Comments closedDarren Perucci has a checklist for spinning up a production ElasticSearch environment:
When you are looking for a node or a cluster, it is a good idea to have a name which describes what you will need to find and separates one from another.
The default cluster name of “elasticsearch ” could allow any nodes to join the cluster, even if this was not intended. Thus, it is a good idea to give the cluster a distinct identifier instead.
There are a few things to think about in this post.
Comments closedAndy Leonard shows how to create an SSIS catalog:
Check the “Enable CLR Integration” checkbox to enable the other controls on the form.
I recommend you also check the “Enable automatic execution of Integration Services stored procedure at SQL Server startup” checkbox. This feature causes a stored procedure to execute whenever SQL Server starts. The stored procedure will identify any SSIS packages in a running (or other “active”) status and mark them as “Ended Unexpectedly.” You want this. Trust me. (As my friend Kevin Boles (LinkedIn | @thesqlguru) says, “Push the trust me button and let’s move on,” (paraphrased).
You cannot alter the name of the SSIS Catalog database. It is SSISDB. And, as in Highlander, there can be only one SSIS Catalog per instance of SQL Server.
This post is full of helpful notes if you’ve never used the SSISDB database before.
Comments closedAndy Galbraith shows how to use extended events to track file growth events:
…but what does this have to do with Extended Events?
As I started out, I was looking for something slick I do with a trace that I could replicate in Extended Events, and this was a great candidate.
—
The catch as I discovered, is that while file growths are captured in the default trace, they are *not* in the system health session…
Fortunately, you can create your own XEvent and Andy gives us the details.
Comments closedNiko Neugebauer looks at a new feature in SQL Server 2016:
In the first version/iteration of the Database Scoped Configurations, there are 5 functionalities are supported on per Database level:
– Procedure Cache cleaning
– MAXDOP control
– Query Optimizer Cardinalitiy Estimation Model activation control
– Parameter Sniffing control
– Query Optimizer Hotfixes enabling
Niko also shows an example of how the different MAXDOP settings interact.
Comments closedRobert Davis ran into HADR_WORK_QUEUE waits recently:
Our 3rd party monitoring solution collects blocking information, but not for system threads. There was no additional information available for this blocking incident, but I could see that the system thread was a background process with the command “UNKNOWN TOKEN” and was sitting in a wait type of “HADR_WORK_QUEUE”. It was clearly the worker thread for the AG of a specific database.
A little later, we had blocking again involving that same thread, but this time, the AG worker thread was blocking the log backup thread. Seemed logical that if the worker thread could block the log backup, then the log backup could have also blocked the worker thread, but still it did not make sense to me.
This is one of those cases in which the answer makes perfect sense after the fact, but can be maddening until then.
Comments closedPerry Skountrianos introduces a new Docker image:
We are excited to announce the public availability of the sql server 2014 express Docker image for Windows Server Core based Containers! The public repo is hosted on Docker Hub and contains the latest docker image as well as pointers to the Dockerfile and the start PS script(hosted on Github). We hope you will find this image useful and leverage it for your container based applications!
Containerization is a huge part of modern administrative world and it’s good to see Microsoft (belatedly) jumping onto the bandwagon.
Comments closed