Press "Enter" to skip to content

Category: Administration

Monitoring for Blocked Processes and Deadlocks with Extended Events

Published 2024-10-02 by Kevin Feasel

Lori Brown’s speaking my language:

Here is a way to set up an extended events session that can be used to collect blocked processes and deadlock records.  I use similar code to collect this type of data each day and have a job that pulls the data into tables which can be used to evaluate the resources that are being blocked and deadlocked on.

When checking for blocked process records, you must set the blocked process threshold in the configuration.  I have mine configured with the threshold set to 30 seconds.  This means that blocked process reports are generated every 30 seconds. 

Click through for that configuration setting, as well as the Extended Events session to do the work, and even code to pull the results into a table. It doesn’t get much simpler than that.

Comments closed

Managing Orphaned Users in SQL Server

Published 2024-09-30 by Kevin Feasel

Jordan Boich asks for more gruel:

Addressing orphaned users is an important piece of SQL Server security management. When left unchecked, you can accumulate an overwhelming number of users that exist in a database but do not have a correlating login in the master database, thus preventing access to the server or the database at all. There are dbatools PowerShell modules out there that can help you accomplish what sp_FindOrphanedUser does. However, there are some situations where PowerShell may not be available to you for a multitude of reasons, and having an extra tool in the toolbelt never hurt anyone, am I right?

Read on to learn about orphaned users and how the sp_FindOrphanedUser procedure works.

Comments closed

Rebuilding a Transaction Log

Published 2024-09-27 by Kevin Feasel

David Fowler fixes a large-scale oopsie:

“Could you help me, we deleted the database’s transaction log file and now that database is stuck in ‘Recovery Pending’?”

This was a panicked call that I received a few weeks ago.

“Sure, no problem” said I, “we’ll have to restore back to your last backup”

And then things went silent for a while before the inevitable, “it’s only a development database, we don’t take backups”.

I can feel the face-palm from here. Read on to learn what you can do if you’re in that situation, as well as David’s important note about taking backups so that you don’t end up in this situation to begin with.

Comments closed

Cloud Governance via Azure Policy

Published 2024-09-23 by Kevin Feasel

Alexey Nazarov draws a line:

Azure Policy is a service that allows you to create, assign, and manage policies that govern your Azure resources. Policies are rules that define the desired state and configuration of your resources, such as the location, size, tags, and properties. Policies can also audit the compliance status of your resources and report any violations.

With Azure Policy, you can ensure that your resources follow the best practices and standards that you define for your organization. You can also use Azure Policy to implement cost management, security, and regulatory compliance for your cloud environment.

Click through to learn more about Azure Policy. My limited experience with it is that the idea is sound, though there are some limitations in what you can do that can make things annoying.

Comments closed

Automated Patching in SQL Server on Azure

Published 2024-09-18 by Kevin Feasel

Abdullah Mamun closes a door and opens a window:

The automated patching feature for SQL Server on Azure VM will be retired on September 17th, 2027.

While Automated Patching is being retired, we’re excited to replace it with Azure Update Manager. Azure Update Manager is an enterprise class powerful tool that provides:

  1. Centralize Update Management: Azure Update Manager provides a unified dashboard where you can view and manage updates across your entire environment, including virtual machines, on-premises servers, and even hybrid scenarios.
  2. Customize Schedules: With Azure Update Manager, you can create custom update schedules based on your organization’s needs. Whether you prefer weekly, monthly, or specific date-based updates, Azure Update Manager has you covered.
  3. Patch Compliance Reports: Azure Update Manager generates detailed reports on patch compliance, helping you stay informed about the status of updates across your infrastructure.

Click through to learn more about the transition and next steps.

Comments closed

Checking Cumulative Update Status on SQL Server Instances

Published 2024-09-17 by Kevin Feasel

Steve Jones reminds you to check those cumulative updates:

How can I quickly get a CU patch for a system that’s out of date? I’ll discuss that situation.

You might think you get to patch every instance every few months, and you may be able to. But most of us have laggards in any decent-sized estate. Someone always wants to avoid patching, or skip patching on the day you’ve scheduled every other system.

Steve’s solution involves using Redgate Monitor, though you could also do this on your own or using something like dbatools to get information about your estate.

Comments closed

External References in Data-Tier Applications

Published 2024-09-16 by Kevin Feasel

Andy Brownsword needs to make a call out:

One method for transferring a database to a different environment is using a Data-Tier Application – in the form of a DACPAC (for schema) or BACPAC (for schema and data).

Trying to use this approach with multi-database solutions is a challenge though as Data-Tier Applications don’t play nicely with cross-database objects.

Let’s look at how we can ease that pain.

Read on for the solution.

Comments closed

A Reminder for Server Consistency

Published 2024-09-12 by Kevin Feasel

Chad Callihan resolves an issue:

I connected to the latest SQL Server, opened SSMS, and tried to restore from there. Sure enough, I was presented with the error:

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.

Read on for the solution, which was easy enough, but serves as a reminder that having (and occasionally running!) idempotent configuration scripts can be quite useful.

Comments closed