SQL Server ReIndexing Jobs fail due to full transaction logs

We've encountered issues with a couple of our customers wherein, as that database grows, index maintenance becomes difficult to manage.

In some cases, because the indexes become quite large, the index rebuilds on badly fragmented indexes fill the transaction logs.

For one of our customers, we resolved it in part by altering the recovery model to be BULK LOGGED during the index maintenance.

Index Rebuild operations qualify as a minimally logged operation, and can therefore take advantage of reduced logging in BULK LOGGED recovery model.

I wanted to do some research or testing before I recommended this, because it’s a big deal to change recovery models, do index maintenance, and then switch back.

I did find a link to someone else who did some testing, though, and found that using bulk logged would, indeed, reduce log file growth during  re-indexing.

https://blogs.msdn.microsoft.com/sqlserverfaq/2011/01/07/using-bulk-logged-recovery-model-for-bulk-operations-will-reduce-the-size-of-transaction-log-backups-myths-and-truths/

(Thanks to Balmukund for running and publishing this test!!!)

For his test Balmukund used Adventure Works, of course 😉. Classic 😀

In FULL RECOVERY MODE, the DBCC Reindex used about 100 MB space. 

(by my math using rounding: 41-2 = 39% used. 258 *.39 = 100.62)

In BULK LOGGED, it used about 2.5 MB.  

(again, rounding: 4.77- 2.98 = 1.8% of 258 is 2.58.)

This is a *VERY* significant difference. 

While I understand that switching back and forth between FULL and BULK LOGGED recovery model is scary, this remains something  to keep in our hip pocket when index maintenance is filling the transaction log! 

TODO: Verify that partitioning the table/index (es) involved will not also help this situation. My guess is that it will not, because there is only one transaction log for a database in sql server, and logging is logging. Still, I'd like to test the theory.

Someday 😏

LABELS not working in .bat file. Error: The system cannot find the batch label specified

I've often run across the issue where
- I write a .bat file with a label.
- The label is quite valid
- The system throws the error: The system cannot find the batch label specified

I've found this to be an issue of line terminators.
I typically use Notepad++ for editing .bat files. It tends to use Unix line terminators. (Just LF, or \n)
I need to change these to be \r\n (carriage return & new line)

This also seems to impact statement blocks for IF statements.
Example:
IF "%1"=="SomeValue"(
  echo %1
  echo I just echoed %1
  set myvalue=%1
)

I have not carefully tested, but I get the same sort of weird errors for perfectly valid statements. I'm guessing they're related.

Best of luck!

DataGeekBlog: How to investigate DB2 Copies ... On Windows

DataGeek.Blog posted this on How to investigate DB2 Copies ... On Windows.
It's got info for both Windows and Linux.
Might come in handy :-)

On Burnout

About 2 weeks ago, I was sitting in a doctor’s office with a blood pressure of 160/100 (very high for someone my age), and having such sever anxiety attacks that I couldn’t even go anywhere on vacation.  That’s an extreme example of the burnout level she’s talking about, below.

How did I get there?

Well, I persisted for far too long in an utterly hopeless position.

I remarked to my wife that I worked all the time, but was only productive for about 4-5 hours a day because I was spending the rest of my day stewing in my own juices about things that didn’t normally bother me; and even when I wasn’t working, I was thinking about work - day, night, and weekend.

I was growling at my family and spent most of my time just wanting to cry.

It took the first several days off  just to recover to a point where I could more-or-less function again. I spent some time with friends, which was very rejuvenating. I didn't pressure myself to do anything. If  I couldn't do it, I just said so. 

After a week off, do I feel like a new man? 

Yes!!!

Do I also realize that I’ll be back in that state within a week or two of having 5 times the workload that I can successfully handle while stranded on a desert island with no one paying attention, even when I try to tell them?

Also yes. :-/

I’m not honestly sure what I’m going to do about any of that. I actually was too far down in a hole to even try to change jobs. Maybe I should take advantage of my recovery to do just that. I dunno. Changing jobs almost certainly means moving, and we’ve got a lot holding us here.

Kendra Little is awesome. She’s a SQL Server consultant and blogger that I follow (and currently a product evangelist for Red Gate), and I love that she writes about this sort of thing (burnout).

The entire thing is useful, but the last section is pure gold. Included below with link, emphasis mine.

What if I don’t have time?

The voice of burnout in your head is likely to make an objection: we don’t have time for this.

*That’s the whole point.*

Well, here’s the thing that I’ve learned from the past: it may hurt to make time, but it’s going to hurt even more if you don’t.

It’s not easy to ask for help with your workload. You may need to negotiate to make it happen. It’s not a good feeling to say that you can’t do things which you’ve agreed to. If you’re in a culture of over-achievers, it can be quite difficult to say that you don’t want to work as much as everyone else is working.

However, the thing about burnout is that you can’t sustain it. If you don’t take action and you just keep your nose to the grindstone, chances are good that you’ll become desperate for a job change, and that you’ll either quit your job or take something, anything, for a change.

Burnout leads to bad choices.

It’s a much better choice to start doing the tough work and speak up for your own needs, before you are so burned out that you can’t. Get yourself into a more productive place before making any big decisions about the future — and things will look better from your new vantage point.

https://littlekendra.com/2019/05/14/how-to-recognize-the-early-stages-of-burnout-and-my-treatment-plan/

Using PowerShell as a port scanner

I've been having a lot of servers lately that have been stood up for me by the hero's in our IT department (that is not sarcasm - these guys are awesome) without having the right holes poked in the firewall.

For instance, SQL Server listens, by default on port 1433. (NOTE: Best practice for server hardening is to change the default instance name and port)

To test this, I'd been using a clunky old port scanner that I wasn't happy with.

I therefore googled and found this excellent blog post by jblanchard.

The portion that I actually use is this:

PowerShell port scanner:

1..1024 | % {echo ((new-object Net.Sockets.TcpClient).Connect("10.0.0.100",$_)) "Port $_ is open!"} 2>$null

I usually change the first bit to 1433 (or whatever specific port I want to check )

1433 | % {echo ((new-object Net.Sockets.TcpClient).Connect("Myserver.MyDomain.com",$_)) "Port $_ is open!"} 2>$null

Dark Theme in SSMS

My Life Begins today!

Make DB2 work again after server name has changed

There is an excellent guide on the steps needed to change the name of a server that is hosting DB2 LUW database that can be found on the Filmore Group Blog..