Saturday, April 13, 2024

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;
        name = 'NewLogFilename',
        filename = 'D:\MSSQL\2017\Log\Nautilus_log_2.ldf',
        size = 1048MB,
        filegrowth = 5%

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:


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

Wednesday, January 24, 2024

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:

Hope to have helped!

Thursday, April 20, 2023

Error 33129: Cannot use ALTER LOGIN with the ENABLE or DISABLE argument for a Windows group.

Recently an incredbily wise DBA friend of mine -- really -- attempted to disable the BUILTIN\Administrators login in SQL Server with this statement:



But it failed with this message (user name dummified):


Executed as user: domain\svc_account. Cannot use ALTER LOGIN with the ENABLE or DISABLE argument for a Windows group. GRANT or REVOKE the CONNECT SQL permission instead. [SQLSTATE 42000] (Error 33129).  The step failed.

Per BOL, "The login of a Windows Group cannot be disabled. To temporarily remove access permission granted to a Windows Group, REVOKE the CONNECT permission of the login for the Windows Group. Windows users might still have access through their individual login or through another Windows Group." 

So my incredibly talented DBA friend changed their statement to this, and the members of this Windows Group can no longer connect to SQL Server:


NOTE:  Don't miss that underlined piece about any member of that Windows Group can still get in if they are members of any other Windows Group.

More information on Error 33129:

Monday, April 11, 2022

Using xp_readerrorlog to read the SQL Server Error Log

The SQL Server Error Log contains a lot of information -- some of which can be very useful.  :)  But, how do you find what you're looking for in the Error Log quickly?  How might you trace an event over time to see when it was recorded in the Error Log?  

In this post I will show you how to use xp_readerrorlog to quickly find what you may be looking for in the Error Log.  In this example we are looking for all incidents of 'Login Failed':

       IF OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL
       DROP TABLE #ErrorLog
       CREATE TABLE #ErrorLog (
         LogDate DATETIME ,
         ProcessInfo VARCHAR(1000) ,
         LogMessage TEXT
       INSERT #ErrorLog  -- see below for xp_readerrorlog parms
       EXEC sys.xp_readerrorlog 0, 1, "Login failed"

-- now take a look at any occurrences that were recorded

This is the output:

The above example was just login failures, but you can use this to track down just about anything that is recorded in the Error Log.  I used it recently to track down the cause of database timeout exceptions.  The end user could only say when the timeouts were occurring, so I looked into the error log at the given time and found this:

I/O is frozen on database XXXX.  No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Then I used the above method to find the message in the Error Log every day at the same time that the full server backups were occurring.  And there you have it.  That was the cause of the timeouts they were seeing.  Not uncommon at all to see this w/the 3rd party full server backups, which they were then able to workaround.  

Anyway... this is just something you can use to quickly read the SQL Server Error Log to identify whenever a particular event is occurring.  Hope you find it useful.

These are the xp_readerrorlog parameters:

Stored procedure created & last modified date

To go alongside the last executed date of the stored procedure, you can use this to tell you when the procedure(s) were created and last moddified.  


             [DatabaseName] = DB_NAME(),
             [ProcedureName] = SCHEMA_NAME([schema_id]) +'.'+ [name],
             [Created] = create_date,
             [LastModified] = modify_date
             [type] = 'P'

Should return a little something like this:

You can narrow it down to a single procedure by adding this into your WHERE clause:

    AND [name] = 'usp_BackupAudit'

Stored Procedure - Last Executed Date

Thursday, March 18, 2021

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

All I did was this:    GRANT SHOWPLAN TO [domain\userName];
And the server returned this message:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

So what does that mean?  Per MSFT, this message occurs when you try to grant or revoke permissions for the following database principals:

    yourself, sa, dbo, entity owner, information_schema, sys

Weird. The [domain\userName] is not any of these users. Even more weird, the [domain\userName] was not even a user in the database yet.  So I dig a little further and found that this user was mapped to dbo within the database I was trying to GRANT SHOWPLAN to:

How to correct?

USE master;
ALTER AUTHORIZATION ON DATABASE::[databaseName] TO [sa]; -- or the desired owner
USE databaseName;
CREATE USER [domain\userName] FOR LOGIN [domain\userName] WITH DEFAULT_SCHEMA=[dbo];

Then my GRANT SHOWPLAN completes without error:

GRANT SHOWPLAN TO [domain\userName];

Commands completed successfully.
Completion time: 2021-03-18T14:48:18.5162709-05:00

ALTER AUTHORIZATION is the new version of sp_changedbowner, which has been deprecated.  More details here:

Many people say that there are risks to granting SHOWPLAN to users, but I disagree.  For the authorized user, of course, I believe it is ideal for them to have this permission so that they can become aware of the resource overhead of their statements, and potentially help them to write better code. 😏😏

More details on SHOWPLAN:

Hope I've helped!

Friday, August 7, 2020

Can you query SQL Server's Error Log file location or size?

Why yes.  You can!  Working a new server today that I have limited access to... and I needed a way to get around that limitation.  I just needed to find the location of the SQL Server error log files -- and you know what else?  The size, too, because it was taking an ungodly amount of time to open the error log within SSMS.  The server hasn't been cycled in months, so I was sure the thing was pretty big.  

What does the DBA without sysadmin privileges do?  😏 


DECLARE @logfiles TABLE (

       [FilArchive#] TINYINT,

       [Date] DATETIME,

       [LogFileSizeB] BIGINT



INSERT @logfiles

EXEC xp_enumerrorlogs





       CONVERT(VARCHAR(50),CAST(SUM(CAST([LogFileSizeB] AS FLOAT)) / 1024 / 1024 AS DECIMAL(10,4))) + ' MB' SizeMB








-- to identify error log file location

SELECT SERVERPROPERTY('ErrorLogFileName') [Error_Log_Location];

The current one is not quite as large as I expected, but here you go -- the location and size of the current and all archived SQL Server Error logs.


DBCC SQLPERF(logspace) -- Only one database, or with conditions.

Many SQL Server DBAs often use 'DBCC SQLPERF(logspace)' to analyze transaction log utilization for their databases.  It is very helpful.  When run exactly as I've quoted (but without the quotes), you will return log usage details for all databases on the server:

But - what if you have a ton of databases on the server and you don't want to be scrolling through everything to find what you care about?  Or, maybe you only wish to see those databases where the log usage is greater than X%. 

Here's a quick little trick to use DBCC SQLPERF(logspace) with conditions, only returning details for the databases you care about.  

-- optional parameters


       @dbname VARCHAR(155) = NULL,

       @SpaceUsed FLOAT = NULL



       dbName VARCHAR(155),

       LogSizeMB FLOAT,

       [LogSpaceUsed%] FLOAT,

       [Status] INT



EXEC ('DBCC SQLPERF(''logspace'')')


-- Now pull it back for review

-- if your optional parms are null, you return log usage for all databases

SELECT dbName, LogSizeMB, [LogSpaceUsed%][Status]


WHERE (dbName = @dbName OR @dbName IS NULL)

AND ([LogSpaceUsed%] >= @SpaceUsed OR @SpaceUsed IS NULL);

Here I have populated my optional @SpaceUsed parameter, to only return details where the log is 30% used or greater.  Most likely you'll want to use a higher @SpaceUsed, but this is just a visual so you can see what I mean.

Or in this case, I've input the @dbName:                            

Hopefully you find it useful!

More details here:

Friday, July 31, 2020

Auto-generate ALTER DATABASE statements to move SQL Server database files

This is for the DBA who needs to move a LOT of SQL Server database files, but has very little patience to sit and type each one up one by one.  Like me.  Someone I know had to move log files and adjust filegrowths for nearly 50 databases yesterday, and she tells me she began pulling her hair out about 15 minutes into it. 😣

This query is just a fast way to generate the ALTER DATABASE statements to modify file location and filegrowth settings for the given @dbName.  And, it generates the rollback as well, just in case.  Remember, always cya.  Check the variables, edit to suit your needs.

USE master;  -- run it in the master db 

       @dbname SYSNAME = 'tempdb',  -- change to db you are moving
       @oldpath VARCHAR(255) =  'C:\MSSQL\2017\TempDB',  -- input existing path
       @newpath VARCHAR(255) = 'C:\MSSQL\2017\Data',  -- input new path
       @dFileGrowth CHAR(5) = '256MB',  -- change, if needed
       @lFileGrowth CHAR(5) = '128MB'  -- change, if needed

-- Generate ALTER statement to move files
SELECT + CHAR(13) + CHAR(10) +
'ALTER DATABASE ' + @dbname '
       Name = ' + name + ',
       FileName = ''' + REPLACE(physical_name,@oldpath,@newpath) +''',
       FileGrowth = ' + CASE WHEN mf.type = 1 THEN @lFileGrowth ELSE @dFileGrowth END + ');'
FROM sys.master_files mf
WHERE database_id = DB_ID(@dbname);

/* Generate ROLLBACK statement for safety measures.  */
SELECT + CHAR(13) + CHAR(10) +
'ALTER DATABASE ' + @dbname + '
       Name = ' + name + ',
       FileName = ''' + physical_name + ''');'
FROM sys.master_files
WHERE database_id = DB_ID(@dbname);


Standard SSMS query to grid results will give you something like this, where you can just cut/paste into another query window to execute.

Or, if you change the SSMS query results to output to text, you can better see the structure of the ALTER statement.  Like this:

Happy SQL.  Hope to have helped!!