Press "Enter" to skip to content

Category: Administration

Client Diagnostics in Cosmos DB

Published 2024-10-10 by Kevin Feasel

Arthur Daniels digs into diagnostic data:


This topic keeps coming up with my customers so the purpose of this blog post is to keep all the useful information in one post. Think of this post as a consolidation of different pieces of information. If you’re stuck on what “pipelined”, “created”, or “transit time” means, you’re in the right spot.

We are not talking about Diagnostic Settings/Log Analytics in this post, I’d describe those as server-side rather than client-side diagnostics. They are useful in different ways, our client diagnostics will help us understand the path that a request takes from the application to Cosmos DB and back, along with any latency on that path.

Note: before we get started, check to see if you’re using Direct or Gateway mode. Ideally sending your requests Direct mode in the .NET or Java SDKs will usually result in faster requests.

Read on to see how to retrieve and interpret this diagnostic data.

Comments closed

Fixing Missing SQL Agent Jobs Post-Migration

Published 2024-10-08 by Kevin Feasel

Lee Markum is looking for that lost shaker of SQL Agent jobs:

I’ve been doing migrations fairly continuously for the past 18 months. PowerShell has been my primary mechanism for many parts of the process, including copying jobs from the source SQL Server to the target. That has worked almost without incident each time. However, recently, an app team noticed that there were SQL Server Agent jobs missing on their new 2022 SQL Servers.  Because the first couple of missing jobs also existed on their Development environment, they were able to recreate those jobs in production. They naturally expressed concern that other jobs may be missing.

Read on for Lee’s process, including the solution.

Comments closed

Reduced Auto-Pause Delay for Azure SQL DB Serverless

Published 2024-10-08 by Kevin Feasel

Morgan Oslake goes to sleep sooner:

Azure SQL Database serverless automatically scales compute based on workload demand and bills for compute used per second.  In the General Purpose tier, serverless also provides an option to automatically pause the database during idle usage periods when only storage related costs are billed.  When workload activity returns, the database is automatically resumed.

Customers choosing to enable auto-pausing can specify the auto-pause delay as part of the serverless configuration.  The auto-pause delay is the length of time the database must be idle before auto-pausing.  The lower the auto-pause delay and the more frequently auto-pausing occurs, the greater the potential compute cost savings. 

Read on for the update in minimum auto-pause time.

Comments closed

Managing SQL Agent Job History

Published 2024-10-03 by Kevin Feasel

Joe Gavin prunes some history:

The SQL Server Agent is a very powerful job scheduling and alerting tool that’s tightly integrated with SQL Server. It’s quite possible you’re only using it for basic maintenance tasks like database backups, index maintenance, DBCC checks, etc., and the default retention is fine. However, you may also be using it for much more, i.e., executing multiple step jobs that execute multiple SSIS packages, and you need to retain a longer than the default job history.

Read on to learn more, including how one really useful-sounding checkbox doesn’t quite work the way you’d expect.

Comments closed

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