No Scooby-Doo story is complete without footprints leading to a hidden passage. In SQL Server 2025, those footprints point us straight toward the next big feature: optimized locking. And like any good sleuth, we’re going to follow the trail step by step. But first, flip the levers that open the secret door: Turn on ADR before you hunt. Think of Accelerated Database Recovery (ADR) as the latch that unlocks the passage. You must enable ADR before you can enable optimized locking. (And if you ever need to turn ADR off, you’ll have to disable optimized locking first—no sneaking out the window!) RCSI = brighter flashlight. For the biggest win, enable…
-
-
Like any good mystery, some SQL Server problems look spooky on the surface but have a very simple culprit hiding in the shadows. Every good Scooby-Doo episode starts with something spooky: flickering lights, creepy footsteps, and someone yelling, “There’s a ghost in here!” In the world of SQL Server, one of the most common “ghosts” I run into? Forgotten database compatibility modes. The Setup: A Creepy Little Detail Here’s the deal: when you create a new database, it gets the compatibility mode that matches the engine level at that time. Sounds fine, right? But let’s say you spun up an Azure SQL Database years ago—chances are it’s still running at compat…
-
I saw this again recently and see it too often in environments so wanted to take a second to remind everyone to cycle their error logs on a regular basis. SQL Server keeps error logs and when you reboot or restart SQL Server services the logs are cycled and a new one is created. Depending on how many logs you have configured for SQL Server to have this may include removal of the oldest log as well. Since many of pride ourselves on keeping our SQL Servers up and running, reboots may be few and far between thus our logs get large in size. When they grow out of control…
-
Quite often I see database administrators set SQL Server max server memory thinking everything related to SQL Server uses this shared memory pool. This is a mistake. There are many things that rely on memory that are not part of SQL Server. Best practices state that you should leave memory allotted for the operating system. However, did you know that if you are running services like SSIS, SSAS or SSRS on the same server as the database engine that it does not use the same memory you have allocated for SQL Server? If the Max Memory setting is not configured correctly, these other serves could incur memory pressure. While the…
-
Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained database is a database that is completely un-reliant on the instance of SQL Server that hosts the database including the master database–theoretically this makes a database much easier to move between servers (you’ll note the absence of SQL Agent jobs from this post, that’s a difference problem.). One of the biggest benefit is that it allows database level authentication, so there no need to have user logins on server level. Contained database now enables us to make a database more portable. I can backup and restore to any instance of…
-
Ever seen the below error? Until this week I hadn’t. So, I figured I’d take a little time and introduce it to those that had not. Error Description: Length of LOB data (65754) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit We ran into an issue with a customer this week, this error was flooding the error log. After a little digging I found it had to do with transactional replication (also applies to Change Data Capture) they had setup which included…
-
When applying a new SQL Server cumulative update, hot fix, or upgrade SQL Server doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you had already stay at their pre-upgrade compatibility level, which means they run under the older set of optimizer rules. Additionally, many optimizer fixes are not turned on. The reason for this is that while they may improve overall query performance, they may have negative impact to some queries. Microsoft actively avoids making breaking changes to its software. To avoid any negative performance impacts, Microsoft has hidden optimizer fixes behind a trace flag, giving admins the option to enable…
