More than one database transaction log file?

You can have more than one transaction log file for your database... but why would you?   

SQL Server will only write to one log file at a time, regardless of how many you give the database.  The transaction log file is written to sequentially, not serially, and there are ZERO performance benefits to having more than one log file for your database.  In fact, multiple log files can actually degrade your performance in some cases.  

But again, why would you have more than one log file?  Easy.  Your log file blows up due to a rogue transaction or some other unexpected reason, and your drive is filling up fast.  You cannot afford the downtime, so your only choice is to add a 2nd transaction log file temporarily.

Here's how:

USE master;
ALTER DATABASE Nautilus ADD LOG FILE (
        name = 'NewLogFilename',
        filename = 'D:\MSSQL\2017\Log\Nautilus_log_2.ldf',
        size = 1048MB,
        filegrowth = 5%
        )
GO

Now you can do whatever you need to do operationally until you reach a point where you can clean things up and remove that 2nd log file.  When it doesn't contain any transactions, the log file can be removed with this ALTER statement:

ALTER DATABASE Nautilus REMOVE FILE NewLogFilename;

If the log file is not completely empty, your statement may fail with this error:

The fast way to resolve that is to backup the log first:

BACKUP LOG Nautilus TO DISK = 'F:\Backup\Nautilus.bak'

Now run the same ALTER statement again and it should succeed:

We've got to remember that there is no reason to create more than one transaction log file for your database under normal circumstances.  The above method can be used in the abnormal or unexpected situations when you're running out of disk and need to do something fast to keep your database online.

See these for more details:   

Adding & removing data or transaction log files
Multiple transaction logs for SQL Server databases

Change SSAS server mode from Multidimensional to Tabular

I had to change a SQL Server Analysis Service (SSAS) instance from Multidimensional to Tabular today, and want to share the steps with you, my loyal readers.  😊

Why was this needed?  Because I didn't ask beforehand what deployment mode was desired, and I just used the default of 0, which is multidimensional.  My mistake.  Haste makes waste, I believe they say.  But fortunately, the fix is easy.  No reinstallation needed.  

These are my steps:

1.      Backup any databases and detach (Multidimensional databases are not usable in Tabular instance).

2.      Stop SSAS service

3.      Open notepad as administrator, then File + Open, browse to your \OLAP\Config directory:

D:\Program Files\Microsot SQL Server\MSAS16.MSSQLSERVER\OLAP\Config

4.      Open the msmdsrv.ini file, change DeploymentMode to 2, save and close file.

5.      Restart SSAS service

6.      All done

This is where your msmdsrv.ini file is, and the location of DeploymentMode setting within it:

There are 3 DeploymentModes, and the same steps can be used to change to Multidimensional or SharePoint:

0  Multidimensional

1  SharePoint

2  Tabular

    

Unsure what your current SSAS DeploymentMode is?  Launch SSMS, connect to Analysis Services, right click server, choose 'Properties' and here you go:

Any reference up there to the \OLAP\Config directory will change based on your SSAS build.  Mine is at \MSAS16.MSSQLSERVER\OLAP\Config for v2022, but yours will vary if your version is different.

Further reading on your SSAS server mode:

https://learn.microsoft.com/en-us/analysis-services/instances/determine-the-server-mode-of-an-analysis-services-instance?view=asallproducts-allversions&viewFallbackFrom=asallproducts-allversionshttps%3A%2F%2Fwww.google.com

Hope to have helped!