Purging data with batched deletes

April 3, 2019 Andy 2

Yesterday, I wrote about the importance of planning your data purges. Today, let’s look at the simplest requirement for purging data: Keep data for X days after it’s created.

The thrill of purging old data

April 2, 2019 Andy 0

DBAs & Data professionals tend to be digital packrats. My personal Google Drive is filled with random files that I refused to part with–even though it’s of questionable value. I lament that I do not have a copy of every paper I typed in high school or college–and I envy those who do. You never know when you’ll need notes from that meeting 6 years ago, or when you’ll need that funny GIF you created for an inside joke you no longer remember.

TSQL Tuesday #99: Tabs vs Spaces

February 13, 2018 Andy 3

The great tabs vs spaces debate is often framed as a matter of opinion or coding style. “As long as you’re consistent, it doesn’t matter.” Keep in mind that unlike your choice in footwear, coding style can’t be personal. I’m going to draw a line in the sand and say that tabs, like Crocs and mullets, are bad style.

Parsing file names from paths using T-SQL

January 17, 2018 Andy 15

SQL Server is great at a lot of things. I spend a lot of time blogging about all the cool things that SQL Server does. But (you knew there was a “but”, didn’t you) SQL Server isn’t great at everything. Lets write about something that … [Read More]

Finding active transactions

January 2, 2018 Andy 2

Have you ever forgot to commit a transaction? Maybe you’ve even left for lunch and caused a problem while you were gone. Uncommitted transactions can cause all sorts of problems–not just blocking, but it can affect your Availability Groups & Mirroring, your TempDB version store … [Read More]

Finding the Leader Blocker

October 26, 2017 Andy 12

During the course of normal processing in a database, blocking happens. Blocking is “by design” because your database is on ACID. In an OLTP database, most of your transactions are short in duration, so we expect blocking to also be short in duration. If blocking … [Read More]

Multi-Statement TVFs in Dynamics CRM

August 22, 2017 Andy 0

If you’re familiar with supporting the database behind Microsoft Dynamics CRM, you probably know that it’s not the fastest-performing database. Honestly, that shouldn’t be a surprise–it’s not designed to be a screaming-fast database. It’s designed to be a flexible database. Most Customer Relationship Management (CRM) systems … [Read More]

What’s wrong with DELETE EXISTS?

June 30, 2017 Andy 1

This morning, when I logged into Twitter, the tweet at the top of my timeline was from Shane O’Neill (blog|twitter). It was to his blog post, Shane, What’s wrong with DELETE EXISTS?.  It’s not that unusual that Shane would talk to himself in the third … [Read More]

Add-Equals operator

May 24, 2016 Andy 9

Shortcuts & tips from an Impatient DBA This series of short posts will demonstrate some lesser-known features, keyboard shortcuts, and other tips that make my day as a DBA more productive. None of these are groundbreaking super-secret features–they are the little things that I do … [Read More]

How to shrink a database in 4 easy steps

April 28, 2016 Andy 9

It’s a really common question: “How do I shrink my database?” Luckily, there’s also a really common answer: “Don’t.” …but what if you really have to? Perhaps you just implemented data compression, and have 60% free space in your database. Perhaps you just deleted a … [Read More]