Differences between Oracle and SQL Server transaction log files

When moving between Oracle and SQL Server, one of the first gotchas is how the transaction log is handled differently between the two engines. In Oracle, the transaction log is a pure transaction log in that it contains only the data and not the rollback information. SQL Server combines the transaction log and the rollback data into the same file.
This leads to some interesting differences i behavior. Let’s take a peek at Oracle first. Oracle divides the different workloads from each other; the transaction log only contains change data but not data required for rollback. The rollback data is stored in a separate rollback tablespace. This also means that the transaction log size i fixed. When a transaction log file is filled up, a so-called log switch occurs where the log writer process switches to the next log file in the group and starts to write there.  This is a good time to point out that the transaction log on Oracle requires at least two (preferably at least three) files in a group, and that the group can consist of several mirrors of the transaction logs (preferably on different drives).

When the log writer switches out of the now full transaction log file, the archiving process steps in and starts reading and copying the transaction log to a new file called the archive log (specified by log_archive_dest_x-parameters). This process then repeats itself as soon as the log writer is finished with one transaction log.

As previously mentioned the transaction log size is fixed, but the number of archive logs produced vary depending on the workload. According to the classic best practices for Oracle, a log switch should occur about every 20-30 minutes. That means that the logs should be sized accordingly, but how many DBAs have a consistent workload that always fills up a transaction log in the exact same way?

Let’s think about the consequences of file size for  short while. Too small files means that the log writer process might be blocked by the archive log process, since the archiver is slower (it has to read AND write). This means poor performance and very hammered transaction log and archive log disks.
The other alternative is even worse – should the transaction log disk disappear in a black hole (it *might* happen!), you will loose all the data that’s still in the transaction log (and have yet to be archived).

The solution is to create large enough (or high enough number of) transaction log files that the archiver have time to scurry out of the way before the log writer switches back to the first file in the log group during the most intensive workload. This might mean that the files are either too large or too numerous to have a log switch of every 20-30 minutes during most of the day, but this is easily taken care of the parameter archive_lag_target. This parameter, set in minutes, specify the maximum amount of time that can pass before an explicit log switch (and hence archiving) is invoked.
The sad thing is that I’ve rarely ever seen anyone set this parameter.

SQL Server, then. The transaction log have a way of growing, sometimes out of hand. The good thing is that we have a continous log thread and the bad thing is that it is differing wildly in size. Where Oracle switches the log file automatically (and hopefully in a consistent, reasonable way), SQL Server does not and hence continues to build rather large transaction log files. There is always a hot discussion what is a good time between log backups, but as with the Oracle discourse above, it all boils down to how much data you are prepared to loose. Some of my customers run a log backup every five minutes, some do it once per day (don’t get me started). The really bad thing about SQL Server when it comes to log handling is the fact that you can’t mirror the transaction log file from within the database.

 

 

Basic settings for SQL Server 2012/2014, part 4

Part 4 – scripts for the previous parts

Below are scripts for most of the SQL Server specific settings I discussed in the previous three parts. Please note that not quite everything is included (trace flags, instant file instantiation, etc.).

 

-- Set memory amount
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'max server memory', 40000; GO RECONFIGURE; GO



-- Turn on optimize for adhoc workloads
EXECUTE sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXECUTE sp_configure 'optimize for ad hoc workloads', 1; GO RECONFIGURE
-- Turn on DAC

EXECUTE sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

-- Adjusting TEMPDB
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 100MB , FILEGROWTH = 50MB);
GO

-- Script to create TEMPDB files like the one existing
USE [master]

go

DECLARE @cpu_count     INT,
@file_count    INT,
@logical_name  SYSNAME,
@file_name     NVARCHAR(520),
@physical_name NVARCHAR(520),
@alter_command NVARCHAR(max)

SELECT @physical_name = physical_name
FROM   tempdb.sys.database_files
WHERE  NAME = 'tempdev'

SELECT @file_count = Count(*)
FROM   tempdb.sys.database_files
WHERE  type_desc = 'ROWS'

SELECT @cpu_count = 8

WHILE @file_count < @cpu_count
BEGIN
SELECT @logical_name = 'tempdev'
+ Cast(@file_count +1 AS NVARCHAR)

SELECT @file_name = Replace(@physical_name, 'tempdb.mdf',
@logical_name + '.ndf')

SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N'''
+ @logical_name + ''', FILENAME =N'''
+ @file_name
+ ''', SIZE = 100MB, FILEGROWTH = 50MB )'

PRINT @alter_command

EXEC Sp_executesql
@alter_command

SELECT @file_count = @file_count + 1
END;



--Create operator DBATeam
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBATeam',
@enabled=1,
@pager_days=0,
@email_address=N'foo@bar.com; gazonk@bar.com';
GO
--Add alerts

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 823', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 824', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 825', @operator_name=N'DBATeam', @notification_method = 7;
GO
--Set failsafe operator
USE [msdb]
GO
EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=N'DBATeam',
@notificationmethod=1;
GO
-- Set database mail profile
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N'MailProfile';
GO
-- Set SQLAgent to use database mail
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
@use_databasemail=1;
GO
-- Configure parallelism and backup compression settings.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'cost threshold for parallelism', N'50';
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'show advanced options', N'0';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'backup compression default', N'1';
GO
-- Adjust MODEL
USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 100MB , FILEGROWTH = 50MB);
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', SIZE = 100MB , FILEGROWTH = 50MB);
GO
-- Example of creating DBA_DB database and user for replication

USE [master]
GO

CREATE DATABASE [DBADB]
ON PRIMARY
( NAME = N'DBADB', FILENAME = N'D:\MSSQL\Data\DBADB.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
LOG ON
( NAME = N'DBADB_log', FILENAME = N'E:\MSSQL\Log\DBADB_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 51200KB )
GO

USE [master]
GO
CREATE LOGIN [dba_repl] WITH PASSWORD=N'xxxxxxxx', DEFAULT_DATABASE=[DBADB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DBADB]
GO
CREATE USER [dba_repl] FOR LOGIN [dba_repl]
GO
USE [DBADB]
GO
ALTER ROLE [db_owner] ADD MEMBER [dba_repl]
GO