Patching SQL Server and SSMS? Order matters.

If you’ve been living under a rock, you may not have noticed how frequently SSMS is getting updated. The SQL Team is really rocking over at Microsoft putting out fixes and enhancements fast and furious. When CUs (cumulative updates) and SPs (service packs) come out, it makes sense to install the latest SSMS update at the same time (if you put SSMS on your servers). If applying both an SP or CU along with an SSMS update, order does matter.
read more

Why Log Shipping is Better than Database Mirroring for Migrations

This topic has come up several times recently, so I feel the need to blog on it. As the person who wrote the book on Database Mirroring, it will probably come as a surprise to many of you that I believe that log shipping is a much better tool for database migrations than database mirroring. I’m not just talking about the fact that database mirroring is deprecated (since SQL Server 2012) and log shipping is not. Both are still in
read more

T-SQL Tuesday #87 – New Way to See Wait Stats for a Single Query

Welcome to T-SQL Tuesday #87 being hosted this month by Matt Gordon (blog|@sqlatspeed). This month’s topic is “Fixing Old Problems with Shiny New Toys”. The old problem I’m going to talk about is seeing what wait types are experienced by a particular query. If you would like to participate in this month’s blog party, go to Matt’s invitational blog post: Announcing T-SQL Tuesday #87 – Fixing Old Problems with Shiny New Toys. Why is my query waiting? SQL Server 2005
read more

Warning: Not a valid checkpoint file name

One of the other DBAs I work with noticed a warning message that was flooding the log files on one of our servers. He asked me to look into it. I did a Bing search for the warning message and found no useful links. This is the warning message: [WARNING] HkHostBackupGetCheckpointFileInfoV2(). Database ID: [<database ID>]. Not a valid checkpoint file name. FileName: fffeaca6-ffff519f-fffc.00015359-0000ae60-0003.c1242a5d-8a93-46ec-9e21-cf41c32179fa.0-0.1000016. (d:\b\s1\sources\sql\ntdbms\hekaton\sqlhost\sqlmin\hkhostbackup.cpp : 2958) The database ID was the ID of the database in which we had recently
read more

Finding Automatic Soft-NUMA

If you already know about Automatic Soft-NUMA in SQL Server 2016, then you probably already read the blog post SQL 2016 – It Just Runs Faster: Automatic Soft NUMA by Robert Dorr, who makes up one half of “the Bobs” that run the BobSQL blog. The other Bob, Bob Ward (@bobwardms), followed that up with How It Works (It Just Runs Faster): Auto Soft NUMA…. Lots of great info there. So having read up on automatic soft-NUMA, I was eager
read more

T-SQL Tuesday #85 Part Deux: Backup Myth Restoring Differential Backups

Welcome to T-SQL Tuesday #85 being hosted this month by Kenneth Fisher (blog|@SQLStudent144). This month’s topic is “Backup and Recovery”. This is one of my favorite topics, so the hard part was narrowing down what I want to cover .. as you can probably tell by me doing a second post for this month. I call this one T-SQL Tuesday #85 Part Deux. If you would like to participate in this month’s blog party, go to Kenneth’s invitational blog post:
read more

T-SQL Tuesday #85: Backup Log With NoRecovery

Welcome to T-SQL Tuesday #85 being hosted this month by Kenneth Fisher (blog|@SQLStudent144). This month’s topic is “Backup and Recovery”. This is one of my favorite topics, so the hard part was narrowing down what I want to cover. If you would like to participate in this month’s blog party, go to Kenneth’s invitational blog post: T-SQL Tuesday #85: Backup and Recovery. NoRecovery Many of you already familiar with the NORECOVERY option for performing restores to allow you to continue
read more

T-SQL Tuesday #81 – Migrating Databases with Dell LiteSpeed

It’s time for T-SQL Tuesday again, and this month’s host is fellow Certified Master and Data Platform MVP Jason Brimhall (Blog|@sqlrnnr). Jason has challenged us to spend some time sharpening a skill and then blog about it. For my participation, I found myself needing to get reacquainted with a third-party backup software that I had not used in many years, LiteSpeed for SQL Server by Dell. I worked with LiteSpeed extensively many years ago when I was a DBA at
read more

Possible Infinite Recompile Was Detected for SQLHANDLE

On one of my SQL Server instances, I see a lot of these infinite recompile messages in the SQL log. Sounds bad, but they never lined up with any detected errors or failures, and I always seemed to have more important things to focus on so I let it slide. Well, today was a slow day — being the Friday before a holiday weekend — so I decided to investigate. The error messages, error #2814, all look like the below:
read more

Return Max or Min Value of a Group of Columns as a Single Column

Recently, I needed a query to identify tables that developers had create as point-in-time backups of tables that were never used again (turns out, there’s quite a lot of them in this database). They are characterized by having _bak or _ appended to the end of them. I wanted to provide a list of the tables to the development team and give them the opportunity to say that any of the tables should not be deleted. I wanted to provide,
read more