Some Notes on the Machines

Microsoft is no longer doing Service Packs for SQL Server, they’re doing Cumulative Updates. This page reports to be listing the most current for SQL 2017:

https://www.microsoft.com/en-us/download/details.aspx?id=56128

Excellent procedure here:

https://technet.microsoft.com/en-us/library/jj853293.aspx

There is a lot of talk about this on the wild Web. This situation occurs because some instances of Microsoft SQL default to “FULL” logging mode, and this can be very helpful because if there is a crash, the database can be restored to the very transactional instant of the crash. In “FULL” logging mode, the LDF file keeps on piling up until an SQL-aware system backup is performed, at which time the LDF is pared down automatically.

But there are many SQL databases out there where we don’t care about transaction-level restore, where the backup is system image-wide and we are very happy with that level of restoration. In this case, “FULL” logging mode is very wasteful, because since the backup is not SQL-aware, the LDF file keeps on piling and piling, taking many gigabytes of disk space.

For these we want “SIMPLE” logging mode. To set this up, log into SQL Studio using an appropriately high-privileged user, and run this query substituting database_name appropriately:

USE master;
ALTER DATABASE database_name SET RECOVERY SIMPLE ;
CHECKPOINT;

Two things are accomplished by the above. First, the LDF will quit piling. Second, a CHECKPOINT was done, which tells SQL Studio immediately that most of the space in the huge LDF is wasted. This means that you can tell SQL Studio to shrink the LDF, eliminating the wasted space, like this:

• In Databases, right-click on the database whose LDF needs shrinking
• Click Tasks —> Shrink —> Files.
• In File type, choose Log.
• Make sure “Release unused space” is selected. The other numbers will make it obvious how much space you are freeing up. If they’re not good, you have something else wrong or undone!
• Click OK. The shrinkage will begin. Depending on how much is to be done, it may take a while, but the progression will show up in free disk space recorded in CMD.