Amy (SQLKitten) shows how to troubleshoot a common error for replication-enabled databases:
Based on the results from sp_helpreplicationdb , I now have confirmation that my database is at least enabled for replication. The next thing I need to do is turn off replication for this database with sp_removedbreplication.
Sometimes the short answer is the best one.
Kendra Little shows us how to fix a common partitioning issue:
Everything was fine, but we forgot to put a boundary point in for Jan 1, 2016… and now it’s well into January. All our data for January 2016 is in the partition with the December data.
The more time passes, the more data is going to go into that partition, and it’ll get bigger and more lopsided. If we’re switching out old partitions by month, eventually that’s not going to work. And partition elimination won’t work for anything after Dec 1, 2015, either!
Be sure to keep those empty partitions around, just in case .
Steve Jones explains database snapshots:
I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.
However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick “create database xx” syntax I often use, with a snapshot I need to be more specific.
Word of warning: don’t have more than one active snapshot of a single database. If you do, you’ll likely have major performance problems. My favorite use case for snapshots was building some semi-automated integration tests a few years back. I created a tool for devs to create snapshots, and then they could run all the tests they wanted and revert the snapshot afterward. There are some good uses in production environments as well.
Michael J. Swart has an Extended Event and a query to help monitor deadlocks:
Guess what? Apparently I “reinvented the wheel”. The extended events session I created is equivalent to one that Jeremiah Peschka wrote two years ago in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. The embarrassing thing is that in Jeremiah’s article, he references a tool I wrote. And the first comment was written by yours truly.
There are a bunch of ways to capture deadlock information. This is a good one.
The situation: Your server is down. The drive/directory where tempdb is supposed to be doesn’t exist. Who knows why. Maybe those evil SAN guys forgot to re-attach your storage during a DR situation. You may or may not realize it but SQL Server will not start without tempdb. Which is fine. Just move it to a location that exists right? Well, yes. That is an important step. So here is how we
I like the way Russ Thomas (and Kenneth Fisher) put it: this is a low-occurrence, high-liability issue.
Chrissy LeMaire ran into a trust issue one one node of a cluster:
You know what’s scary as hell? When one node of an important cluster loses its trust relationship with the domain and you see the error “the trust relationship between this workstation and the primary domain failed”. That happened to me late last year with one of my SQL Server 2008 R2 nodes. The scary part was that I just didn’t know what to expect. The fix could be simple, or it could require a node rebuild.
Trust no one.
Tom Norman talks about setting up a data scrubbing server:
With that said, when I setup a Scrub server, it is in a very secure area where the data access is very, very limited. For example, in my current company, the server is in a separate domain from Production and QA/Dev. Only DBAs are allowed to access this server. If you have multiple DBAs at your location, you may want to even limit which DBAs have access to this server. Our goal is to automate the entire scrubbing process so no one has to access the data including copying backup files from Production and to a shared scrub location for QA/Dev to retrieve.
Scrub servers are a way of stripping personally identifiable or sensitive information from production data so developers can safely use the data in lower, less secure environments.
David Klee answers the question, when should you have multiple named instances on a single VM?
I am personally partial to having just one instance per VM, as long as the situation allows for it. The resource management area between SQL Server and Windows allows me to manage the overall resource consumption at the VM level, and en mass, managing at this layer rather than multiple layers is usually preferable. I claim that the extra overhead of managing more VMs is worth the resource management flexibility.
I agree with this. The biggest advantage I see is in licensing, but if your environment is of a non-trivial size, you’re probably going to license the host instead of individual VMs. Nevertheless, check out David’s pro-and-con list and see where your situation lies.
I ran across a question on network protocols recently, which is something I rarely deal with. Often the default setup for SQL Server is fine, but there are certainly times you should add or remove network connectivity according to your environment.
Microsoft’s guidance on protocols pushes you toward TCP/IP and that’s a good default.
Grant Fritchey shows how to clear the Query Store in SQL Server 2016:
While setting up example code for my presentation at SQL Cruise (which is going to be a fantastic event), I realized I wanted to purge all the data from my Query Store, just for testing. I did a series of searches to try to track down the information and it just wasn’t there. So, I did what anyone who can phrase a question in less than 140 characters should do, I posted a question to Twitter using the #sqlhelp hash tag.
You can also call EXEC sp_query_store_remove_query to remove a specific query from the Query Store.
Kevin Feasel
2016-01-28
Administration, Replication