Some Notes on the Machines

Often one will find a Microsoft SQL server with no working or available administrative login. To set one up:

1. Stop the database. You’ll need its name. Go to services.msc , find entry “SQL Server (XYZ)”; XYZ is the name.

2. Set up the database to run in single-user mode. In the Sql Server Configuration Manager, in SQL Server Services, in the properties of the desired SQL Server, in the Advanced tab, and for Startup Parameters, add “-m;” (no spaces, no quotes) to the beginning.

3. Start the database.

4. From administrative command prompt, run:

SQLCMD -E -S 127.0.0.1\XYZ

If you have only one SQL server on this machine, omit the -S and everything after.

This may take multiple attempts. The attempts may error saying “Reason: Server is in single user mode. Only one administrator can connect at this time.” Eventually it will connect and give you the prompt:

1>

5. Enter the following, press Enter at every line, and choose something long and horrible for PasswordText:

CREATE LOGIN [sa2] WITH PASSWORD = 'PasswordText'
GO
GRANT CONTROL SERVER TO [sa2]
GO
EXIT

6. Reverse the change in SQL Server Configuration Manager.

7. Restart the SQL server.

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.