MS SQL Transaction Log Management

This is the most important administration area to take care of, because if log entries can’t be written in the log file, the db wil hang whatever the Recovery Model is.

I quickly noticed that a big responsible for log growth is the default Index Reorganization scheduled by Maintenance Plan :

USE [VirtualManagerDB]

GO

ALTER INDEX [pk_ADHC_AgentServer] ON [dbo].[tbl_ADHC_AgentServer] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Another reason for Log growth is a long running transaction ; this a main difference with Oracle as SQL server has to handle Rollback Information in the Log file VS. Oracle that stores before image in the dedicated Rollback Segment space ( UNDO tablespace ).

1] Transaction Log Monitoring

Instance wise

dbcc sqlperf('logspace')

DB	Log Size MB	Log Space Used %	Status
master	1,242188	43,08176	0
tempdb	214,1172	38,20101	0
model	6,117188	93,80588	0
msdb	1,492188	44,50262	0
SUSDB	285,0547	4,897463	0
SMS	5474,117	0,8905278	0
FEPDB	288,4297	10,69368	0

• Looking for Active transaction

dbcc opentran –- within a database

dbcc opentran (8) -- for dbid = 8

Informations de transaction pour la base de donnees 'Application_de_service_de_recherche_CrawlStoreDB_9cb0fc22d2884dea9c3df8a1f7bd7044'.

Plus ancienne transaction active :

    SPID (ID du processus serveur) : 105

    UID (ID utilisateur) : -1

    Nom            : user_transaction

    Numero de sequence d'enregistrement      : (380910:30305:90)

    Heure de debut : sept 23 2011 10:09:57:713AM

    SID      : 0x010500000000000515000000a992ce43efd109a8e61b0cb58e440100

Execution de DBCC terminee. Si DBCC vous a adresse des messages d'erreur, contactez l'administrateur systeme.

My Logfile reaches 71 GB !!!

DB	Log Size MB	% Log used
Application_de_service_de_recherche_CrawlStoreDB_9cb0fc22d2884dea9c3df8a1f7bd7044	71452,62	98,24786

Fix this by killing active transation Session.

sp_who then kill <spid>

2] Transaction Log File Size Modification

2.1/ Shrinking

DB wise

On every DBs.

sp_MSforeachdb 'use [?] ;

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,128)

'

2.1.1 SIMPLE Mode

Dbcc Shrinkfile second argument for logfile is the remaining Free Space size in MB.

For log files, the Database Engine uses target_size to calculate the target size for the whole log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the whole log is then translated to target size for each log file.

USE SMS

GO

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,128)

DB	Log Size MB	Log Space Used %	Status
SMS	129,7422	5,933868	0

2.1.2 FULL Recovery Mode

2.1.2.1 Before any Drive space reaches 100 % used

It may be necessary to backup log first if log_space_used is high.

dbcc sqlperf('logspace')

DB_NAME

-- 10 GB 93 % , needs backup log

BACKUP LOG [DB_NAME] TO  DISK = N'<path>\DB_NAME_backup_2016_06_07.trn'

WITH NOFORMAT, NOINIT,  NAME = N'DB_NAME_backup_2016_06_07.trn',

SKIP, REWIND, NOUNLOAD,  STATS = 10

GO

use "DB_NAME"

go

-- Shrink the log

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,128)

2.1.2.2 When Drive space is 100 % used

Dbcc Shrinkfile can’t be used because it needs to log information. Therefore, Recovery mode has to be changed to SIMPLE mode.

USE NPSXML

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

-- = 2005 Mark the inactive part of the log for release : Backup Log YourDatabaseName With Truncate_Only

ALTER DATABASE NPSXML

SET RECOVERY SIMPLE;

GO

-- Shrink the log

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,128)

-- Reset the database recovery model.

ALTER DATABASE NPSXML

SET RECOVERY FULL;

GO

2.2/ Increase Log File

2.2.1 For a Group of DBs

-- increase every log size to 64 MB

DECLARE @name VARCHAR(64)                      -- database name

declare @lnam varchar(512)

declare @query varchar(max)

DECLARE db_cursor CURSOR FOR

SELECT a.name , b.name FROM master.dbo.sysdatabases a, sys.master_files b

WHERE a.name NOT IN ('master','msdb','tempdb','model') and a.name = DB_NAME(b.database_id) and type_desc = 'LOG';

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name , @lnam

WHILE @@FETCH_STATUS = 0

BEGIN

       SELECT @query ='ALTER DATABASE '+@name+' MODIFY FILE (NAME = '+@lnam+', SIZE = 16 MB) ;'

       print @query

       exec(@query)

       SELECT @query ='ALTER DATABASE '+@name+' MODIFY FILE (NAME = '+@lnam+', FILEGROWTH = 64 MB) ;'

       print @query

       exec(@query)

       FETCH NEXT FROM db_cursor INTO @name , @lnam

END

CLOSE db_cursor

DEALLOCATE db_cursor

2.2.2 Log Full because of ACTIVE_TRANSACTION

Log 100 %

The transaction log for database 'DB' is full because of ACTIVE_TRANSACTION.

Fix : increase logfile

ALTER DATABASE DB MODIFY FILE (NAME = DB_log, SIZE = 2048 MB);

3] Log File Shrinking not possible because of Replication

how to kill Oldest non-distributed LSN , also seen on

http://serverfault.com/questions/280547/sql-server-log-file-wont-shrink-due-cause-log-are-pending-replication-on-non

3.1 Open transactions

dbcc sqlperf('logspace') -- 4 GB 100 % Full

The Log file doesn’t shrink with method seen in 2.2 FULL Mode, even after an instance restart !!

DBCC loginfo -- status = 2

use FEPDW

dbcc opentran

Transaction information for database 'FEPDW'.

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)

        Oldest non-distributed LSN : (1505:14540:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Replication is not set up, though.

select DATABASEPROPERTY('','ISPublished') -- NULL

However, the reason for not removing Log entries stays REPLICATION.

SELECT name, log_reuse_wait_desc FROM sys.databases

-- -> REPLICATION

3.2 Fix, remove « bugged replication », then shrink

sp_removedbreplication FEPDW

use FEPDW

dbcc opentran

-- -> OK distrib tx gone away

It may not be enough, in FULL Mode, go to SIMPLE.

USE FEPDW

GO

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,2048)

-- KO

USE FEPDW

GO

ALTER DATABASE FEPDW

SET RECOVERY SIMPLE;

GO

-- Shrink the log

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,1024)

-- Reset the database recovery model.

ALTER DATABASE FEPDW

SET RECOVERY FULL;

GO

-- OK !! 1 GB 0,12 % occupied

See also Always-On post.

4] Why is it notpossible to shrink the Logfile ?

select log_reuse_wait_desc from sys.databases where name = 'Search_Service_Application_CrawlStoreDB_c29f61691e374bf6abfd801294b0de1d'

a few example :

LOG_BACKUP

AVAILABILITY_REPLICA ( ALWAYS-ON )