Zero to Hero!

Kevin Hill
September 06, 2024

Coffee is my Super Hero!

From Disaster to Smooth Sailing

Who doesn’t love a good train wreck story?

A while back we received a call from a firm referred to us by a DBA I met in a Brent Ozar training class.

Their issue: Full Transaction Log file on the SQL Server – 500+ GB and growing rapidly.

This is a very common issue with SQL Servers where there is no DBA looking after them. Probably the most common issue we get emergency calls on. My video on it has over 100K views.

Most of the time this is due to the log file not being backed up. That was not the case this time. We checked master.sys.databases for the log_reuse_wait_desc info, which gave us XTP_Checkpoint.

XTP is one of the names for In-memory OLTP (eXtreme Transaction Processing, if I remember correctly).

They told me they had looked into IMOLTP but had never used it, meaning they turned it on and then never used it. I was able to find from a quick Google that this ‘re-use description not allowing the log to clear out’ was a bug, which was fixed in a SQL CU the caller did not have.

We patched the server and waited roughly 4 hours for the database to go through Recovery and come back online. We shrank the log file as well.

There were more issues.

The main database (very heavy transactional ingestion of device data) was using filegroups to be able to archive old data/clients onto a slower drive…but the FG was corrupt. This meant that they could only take File Group and Transaction Log backups. No Fulls.

A LOT of things had to happen to get this database functioning again...Single-user, Emergency mode, Service restarts, etc. but it did eventually come online and allow us to get File Group backups and a VM snapshot.

At this point, we suggested 2 things:

1. They need a DBA service provider

2. They need to migrate into a new database on a new server, minus the corrupt FG.

They did both. We signed them up for the Pocket DBA® service for ongoing support, as well as a migration project at significantly discounted rate from our advertised Ad Hoc pricing at the time.

The migration was controlled chaos. With no Full backups to restore, as well as some other issues I’m not allowed to mention, we wound up using Red Gate Software’s SQL Compare to create the new database and SQL Data Compare to move the data itself. It took 100 or so hours over 9 months to get all of this done, but they are in great shape now. Or, they will be as soon as I find an acceptable HA solution for their budget. DR is covered.

On the upside, they are an absolute delight to work with, even when things go sideways!