SQL SERVER – Information Message: Cannot shrink file in database to xxxx pages as it only contains yyyy pages.

I often get emails from my blog readers asking various types of clarifications about my own blog. Here is one of the interesting questions from a reader about message Cannot shrink file in the database to xxxx pages as it only contains yyyy pages which he encountered after following my below blog.

Read More

SQL SERVER – Script – Transaction Log Backups Based on Growth / Size

While doing a comprehensive health check of the SQL Server, one of the things I check is the backup plan for the production databases. Many times, I have run into a situation where a customer says ” Transaction log file of the database has grown more than expected. I want to know the reason why and how to reclaim space and how to avoid such situations”. Let’s break it down into two parts and then

Read More

SQL SERVER – FIX: The Log for Database Cannot be Shrunk Until All Secondaries Have Moved Past the Point Where the Log was Added

While preparing for a demo for a client I was hit with an interesting error in AlwaysOn Availability Group backup and restore scenario. In my lab setup, I had four nodes SQLAUTH1 to SQLAUTH4 and I have always on high availability turned on SQL Server 2016. I played and did several operations with database and found that I have bloated the transaction log file to 100 GB. And now I need to shrink the transaction log on SQLAUTH1. As soon as I did that, I was welcomed with below message about database cannot be shrunk until all secondaries have moved past the point.

Read More

SQL SERVER – MSDB Database Uncontrolled Growth Due to Queue_messages. How to Clear All Messages From a Queue?

Received an email “Need your urgent help On Demand, our MSDB Database has grown too big and we need help to check our MSDB.” I have been an independent consultant for a while and one of the services I provide is “On Demand (50 minutes)” service. This service is very helpful for organizations who are in need immediate help with their performance tuning issue. Though, I have set working ours for my regular clients, every single day, I keep two hours available for this particular offering. This way, I can make sure that anyone who urgently needs my help, can avail the same. Click here to read more about it.

Read More