Press "Enter" to skip to content

Category: Powershell

Automated Alerts for Query Store Usage in Azure SQL DB

Jose Manuel Jurado Diaz doesn’t want to run out of disk space:

In this article, we will introduce a PowerShell script that helps monitor the usage of Query Data Store (QDS) in SQL Server databases. The script automatically sends an email alert when the storage space used by the Query Data Store reaches or exceeds 80% of its maximum allocated space. This can be particularly useful for database administrators to proactively manage and optimize their database storage.

Before we dive into the details, it’s essential to note that the script provided is a recommendation and should be adapted according to your specific environment and requirements. The responsibility of ensuring its compatibility and the impact on your system lies solely with the user.

Click through for the script. A quick glance of the code makes me think it will also work with on-premises SQL Server.

Comments closed

Skipping through Rows in a Large File with Powershell

Jay Robinson has a one-liner for us:

It came from working with enormous text files, and the gotchas that come with them. In this case, I had a 50gb data file that had something wrong with it, buried about 25% in. The file was being processed just fine, until it hit this unexpected data. And because the ETL app was written for performance first, there wasn’t a lot of data validation being done. So it’d just go boom when it hit that batch.

So what was wrong with the file? Well, in order to determine that, I had to see what was in that batch. But you can’t just open a 50gb file in Notepad. Yes, there are other ways to get around this, but here’s the one I chose:

Click through for Jay’s solution to the problem.

Comments closed

Debugging SQLPackage Issues in Powershell

Jose Manuel Jurado Diaz simplifies SQLPackage output:

Handling massive SQLPackage diagnostic logs, like those spanning over 4 million rows, can be an overwhelming task when troubleshooting support cases. This article introduces a PowerShell script designed to efficiently parse through SQLPackage diagnostic logs, extract error messages, and save them to a separate file, thus simplifying the review process and enhancing the debugging experience.

Click through for a Powershell script that can help.

Comments closed

Building a Retry Mechanism for sqlcmd in Bash

Jose Manuel Jurado Diaz won’t let failure get him down:

Introduction:

Efficiently managing temporary failures and timeouts is crucial in production environments when connecting to databases. In this article, we’ll explore how to implement a retry mechanism with sqlcmd in a Bash script, dynamically increasing timeouts with each failed attempt.

Problem Statement:

Operations can fail due to network issues, overloaded servers, or other temporary problems when interacting with databases. Implementing a retry mechanism helps address these temporary issues without manual intervention.

Read on for the solution script. You could also adapt this to Powershell fairly easily, I think, though if you do go down that road, I’d recommend taking a look at Polly and PsPolly.

Comments closed

Verifying a Backup in SQL Server

Chad Callihan knows your last backup is only as good as your last restore:

Is the process of testing your backups something you know you should do but never get around to? Do you find yourself assuming all is well with backups while putting out other fires? Test-DbaLastBackup, part of the beloved dbatools, can solve your dilemma.

There are many options available when using Test-DbaLastBackup. Let’s explore a few of these options and see some examples of how to use them.

Click through to learn more about this. And you could easily put together Powershell scripts to stagger your restorations over a time frame (such as, 15% of your databases each day, so that you get to 100% by the end of the week).

Comments closed

Monitoring Azure SQL Transaction Log Usage with Powershell

Jose Manuel Jurado Diaz has a script for us:

Database administrators and IT professionals often need to be proactive in monitoring resources, especially when working in cloud environments like Azure SQL. One critical resource that requires monitoring is the transaction log of a SQL Server database. If it fills up, it can hinder database operations, leading to potential application downtime.

In this article, we’ll discuss a PowerShell script that monitors transaction log usage in Azure SQL databases and sends an email alert if a database exceeds a specific threshold.

There is a reference in the script to Check-LogUsage, which appears to be a different function, likely related to the linked T-SQL statement Jose posted.

Comments closed

Powershell Quizzes

Jeff Hicks wants you to think fast:

Time to get back to the to blog. I’ve been working through my backlog of projects. These are things that I started writing or updating but then got pushed to the back of the line. One of these projects is a PowerShell module I wrote as a teaching tool. The idea was to create short quizzes on PowerShell topics that someone could take in a PowerShell session. My idea was to create quizzes on PowerShell topics, but you can create a quiz on anything. If you want to try things out, install the PSQuizMaster module from the PowerShell Gallery. The module will work in Windows PowerShell and PowerShell 7, including cross-platform.

Read on to see what’s in a quiz and how to create your own quiz.

Comments closed