Opinion: Forced updates and tempdb

One of the changes that has happened with Windows in recent years is the concept of forced updates. Basically, you’re going to get updates from now on, like it or not. Generally that’s a good thing. You can delay them for a little while but not for that long. In the Advanced options update Updates, you can see this:

So you aren’t going to delay them for that long.

Now what does this have to do with tempdb I hear you ask?

Well, tempdb gets recreated each time SQL Server starts, and that’s normally in two situations:

• You shutdown and restart the computer that it’s installed on
• You restart the SQL Server service

Now Windows 10 out of the box changes that behavior. If you shut it down, and start it up again, you’ll find that objects that you had in tempdb are still there. That’s because a shutdown and power up are no longer the same as a restart. Choosing shutdown actually hibernates the computer and power up just brings it back from hibernation.

So SQL Server didn’t get restarted.

I generally find this when I go to create a demo table in tempdb, just after restarting my machine, only to find the table is already there.

You can change that by this setting in the power options for Windows 10:

Note that I’ve chosen to not have fast start-up enabled. When you turn it off, shutting down actually does a shutdown.

OK, so we can see how this affects SQL Server, but what does it have to do with forced updates?

Well it’s because after Microsoft applies a forced update, I keep finding settings like this “automagically” reset for me, back to the value that I didn’t want. I really wish they would not do this.

2018-03-20