A Fine Slice Of SQL Server
Have you heard of SQL Server’s dedicated administrator connection? The dedicated administrator connection (DAC) can come in handy in an emergency scenario so you should have it enabled and know how to use it…just in case. I haven’t needed it too often in my career but it was helpful in instances when SQL Server wasn’t being very responsive. If there are problems connecting to a server, the DAC can be used to connect and troubleshoot issues.
Read on to learn more.
Comments closedSean Gallardy performs corporate dentistry:
This came with very little to no data available, and to be quite honest, saying “slow restore” doesn’t really mean much. The initial analysis needs to be an actual set of concrete data that describes the issue, what is normal, and what outliers, if any, exist. Since we have none, we can’t even start to analyze anything, so we need to clarify the problem statement and understand a little more about the issue.
This is an interesting dive into the problem and a good example of how to work with “We won’t let you see/do that” as a consultant. Incidentally, if you haven’t heard of WPR, that comes with the Windows Performance Toolkit.
Comments closedAmit Khandelwal extends a solution for SQL Server on Windows:
In this blog, we will look at how we configure near real-time monitoring of SQL Server on Linux and containers with the Telegraf-InfluxDB and Grafana stack. This is built on similar lines to Azure SQLDB and Managed Instance solutions already published by my colleague Denzil Ribeiro. You can refer to the above blogs to know more about Telegraf, InfluxDB and Grafana.
Click through for the quick version, and then the step-by-step process.
Comments closedAndrew Pruski doesn’t have time to restart containers:
When running multiple SQL Server containers on a Docker host we should always be setting CPU and Memory limits for each container (see the flags for memory and cpus here). This helps prevent the whole noisy neighbour situation, where one container takes all the host’s resources and staves the other containers.
But what if we forget to set those limits? Well, no worries…we can update them on the fly!
Click through to see how you can change the memory limits on a running container.
Comments closedThomas Williams wants to go back to square one:
I’m a fan of the built-in database roles like
db_datareaderto standardise & simplify permissions (sorry Dr. Greg Low!) and recently I needed to do just that in a database created using SQL Server 2000, and remove old defaults and a lot of custom roles, schemas and users.I wrote the set of queries below to generate scripts to remove non-built-in roles, schemas and users, when compared to the model database on a new SQL Server 2019 server.
After running the script generated by the queries, I added back users and gave appropriate roles (like
db_datareader).
Read on for the script.
Comments closedTom Collins answers a question:
I have some t-sql code added to a job step on a SQL Server Agent job. The problem is I need to run the code as RUNAS . I though of executing the job with a proxy account – so progressed with the Credential & proxy set up. But I still can’t view the Proxy\Credential in the RunAs list . Is there a way around this problem?
Read on to learn why and for the answer.
Comments closedAaron Bertrand powers up for about six episodes straight, but the results are amazing:
In Part 1 of this series, I showed how to use a Visitor pattern to walk through one or more T-SQL statements to identify a problematic pattern where NOLOCK hints are applied to the target of an update or delete. The method in my initial examples was very manual, though, and won’t scale if this problem might be widespread. We need to be able to automate collecting a potentially large number of statements across an entire environment, and then try to eliminate false positives without manual intervention.
Read on to see how you can take what Aaron wrote last time and make it scalable.
Comments closedAlex Stuart performs some calculations:
I recently encountered a requirement to estimate the size of (a lot of) nonclustered indexes on some very large tables due to not having a test box to create them on or the time to build one. I couldn’t find a script to do this, and as any programmer knows, laziness is the real mother of invention, so I wrote one.
This post summarises how we can calculate this and provides a Powershell function to do so.I used Microsoft’s documentation as a basis on how to do it, but as it’s a wall of text that’s a little tricky to follow, I’ll go over the basics here. I’m only covering the leaf levels and non-MAX columns that would create LOB pages – I’ll explain why later.
Click through for the article and to see how Alex’s calculations play out.
Comments closedReiss McSporran continues a series on workload management in Azure Synapse Analytics:
So in part 1 we found out what Workload Management was and why we wanted to use it. (If you haven’t read that part, please click here and read that post before you carry on, as we’re jumping in at the deep end!) In this part we’ll be learning how to configure it.
Continuing from the end of the previous post, you have two main options to configure this. First is to assign a user or role to one of the existing system defined workload groups, second is to create a custom workload group and assign a user or role to this instead.
Read on to learn how to create classifiers, what the rules look like for them, and their importance.
Comments closedJohn Morehouse causes harm to a perfectly good database:
You might be asking why on earth would you want to get a database into an undesirable state, more specifically into a Recovery Pending state. Well, in my case, I had a client database that had entered into this state due to a failure of storage. Thankfully, this database was not being used for production, so I had some time to determine the best way to fix the issue.
A phrase that was often used during my time in the fire service, was “Try Before You Pry”. Does the front door of the house need to be kicked in? It may be unlocked and by trying before prying (with my boot) I can prevent damage to the door. In these types of scenarios, this philosophy holds true. Try things out on non-critical databases will help prevent any further damage.
In this instance, I want to try it before forcing something that might be damaging. This meant I had to get a test database into a recovering state. Once it is in the state I need, then I can attempt different methods to recover the database properly. Once a successful solution is determined, I can then confidently pry against the damaged database in production knowing that I am using a validated solution.
Read on to learn one way to put a database into recovery pending state, but definitely pay attention to the disclaimer. And its twin.
Comments closed