Press "Enter" to skip to content

Category: Administration

Slow Record Cleanup

Published 2020-02-18 by Kevin Feasel

Jared Poche investigates a slow record deletion process:

I encountered a curious issue recently, and immediately knew I needed to blog about it. Having already blogged about implicit conversions and how the TOP operator interacts with blocking operators, I found a problem that looked like the combination of the two.

I reviewed a garbage collection process that’s been in place for some time. The procedure populates a temp table with the key values for the table that is central to the GC. We use the temp table to delete from the related tables, then delete from the primary table. However, the query populating our temp table was taking far too long, 84 seconds when I tested it.

Read on to understand why.

Comments closed

Loading the SQL Server Error Log into a Table

Published 2020-02-14 by Kevin Feasel

Jeff Mlakar shows how you can load the SQL Server error log into a table:

Why Not Just Use the File System?

When possible I prefer to go into the file system and open the SQL Server error logs in a text editor e.g. Notepad++ or UltraEdit. However, there are sometimes you may have access to a SQL Server instance but not be able to RDP or otherwise scan the file system.

That’s where this comes in – straight T-SQL.

Click through for demos.

Comments closed

Tenant Usage Monitoring with Power BI

Published 2020-02-11 by Kevin Feasel

Jeff Pries shows us the culmination of several blog posts’ worth of work:

Most of those posts (listed at the bottom of this page) are fairly long and technical — as, initially getting started using a program to read Power BI data via the API can be a bit much.

This post is going to be a bit different. Short and sweet. The payoff for all that hard work authenticating to Power BI, requesting data, downloading that data, and storing it in an easy to use SQL table.

With all of the hard work out of the way, its time to build a Power BI report to explore that great Activity Log usage data.

This is the payoff and it’s quite useful.

Comments closed

Recovering Lost Linked Servers

Published 2020-02-10 by Kevin Feasel

Taryn Pratt had a post-upgrade problem:

Recently, I kicked off a project to start moving us to SQL Server 2019. During my initial review of our servers, I found quite a few (9 total) that were still running on Windows Server 2012 R2. This meant that I would need to upgrade the operating system and move us to SQL Server 2019. Having completed plenty of SQL Server upgrades, as well as operating system upgrades, I couldn’t possibly make a mistake, right? Wrong…I completely forgot to script out the linked servers on the server I upgraded this week. I screwed up and decided to write about how I went about fixing it.

Click through to understand the problem and solution.

Comments closed

Auditing DDL with Event Notifications and Service Broker

Published 2020-02-10 by Kevin Feasel

Max Vernon wants to audit Data Definition Language events:

SQL Server doesn’t audit DDL events out of the box, aside from several major events which are captured in the Default System Trace. By “audit”, I mean there is no log of the data-definition-language statements issued against the server. So, if someone creates a table in a database, you can see the table, and when it was created, but details about who created it, and what code they used to create it isn’t saved anywhere. The code in this blog post shows how to configure SQL Server Event Notifications in tandem with SQL Server Service Broker to capture all SQL Server DDL events asynchronously to a database specially configured for the purpose.

Click through for the code and explanation of what’s happening.

Comments closed

Implementing Soft Deletes in SQL Server

Published 2020-02-07 by Kevin Feasel

Brent Ozar shows how to use soft deletes in SQL Server:

Normally when you run a DELETE statement in a database, the data’s gone.

With the soft delete design pattern, you add a bit column like IsDeleted, IsActive, or IsArchived to the table, and instead of deleting rows, you flip the bit column. This can buy you a few advantages:

– Easier/faster undeletes
– History tracking (keeping deleted rows around for auditing purposes, although the method I’m going to show here doesn’t do anything fancy like track who did the delete)
– Easier reconciliation during disaster recovery failovers (refer to the Senior DBA class module on recovering from failovers for more info)
– Lower workload for Availability Groups secondaries (neat scenario from Aaron Bertrand)

Read on to see how and what the costs of this are.

Comments closed

Cleaning Up Schema Ownership

Published 2020-02-05 by Kevin Feasel

Pamela Mooney doesn’t like user-owned schemas:

My colleagues and I take schema ownership seriously.  The owner (with few exceptions) should always be “dbo”.  Certainly, it should not be a user.  Why?  Because if the user leaves and their account is disabled or deleted, we have a problem.  If you’re a DBA, you have enough problems without adding this one to your list. 

So, how do you find these offenders, much less fix them?

That’s what you’ll find out, but only if you click through.

Comments closed

When Identity Columns Skip Values

Published 2020-02-05 by Kevin Feasel

Kevin Hill explains why you might see SQL Server skip 1000 values in an identity column:

Video shows a walk-through of before and after each fix, plus a “Two guys walk into a bar” joke when I disappeared to troubleshoot a broken demo…

I’d probably avoid the fix and live with gaps. You also get gaps when you roll back an operation which inserted into an identity column, or if you have merge replication enabled on a table keyed by an identity column and SQL Server bumps the range on you. All of these are normal and good reasons not to expect contiguous numbering.

1 Comment

Azure SQL Hyperscale Auto-Scaling

Published 2020-02-05 by Kevin Feasel

Davide Mauri explains how automatically to scale Azure SQL Hyperscale:

Azure SQL Hyperscale is the latest architectural evolution of Azure SQL, that has been natively designed to take advantage of the cloud. One of the main key features of this new architecture is the complete separation of Compute Nodes and Storage Nodes. This allow for independent scale of each service, making Hyperscale more flexible and elastic.

In this article I will describe how it is possible to implement a solution to automatically scale your Azure SQL Hyperscale database up or down, to dynamically and automatically adapt to different workload levels without the requiring manual .

Davide has some test measures of how much downtime you see and give you a couple thoughts on how you can track when it’s time to scale up or down.

Comments closed

Burn the Database Down

Published 2020-02-04 by Kevin Feasel

Jana Sattainathan has a script to drop almost all user objects in a database:

I hope this script does not become infamous for the wrong reasons! Please use caution.

I had to help a team recreate everything in a database and test their scripts but leave the roles and role grants in place. Basically, this meant that I could have scripted out the permissions and recreated the database but I thought it would be easier and more re-runnable to just drop everything else except the permissions.

Caution here means making sure you have good backups beforehand, ensuring that you pick the right database, and double-checking everything.

Comments closed