How to avoid transaction log fill up's.

Problem: How to avoid filling up the Transactional logs.

Solution: BACKUP (Transact-SQL)

In any application which has concurrent transactions happening on the live database there are number of log sequences which happen on the background that the database keeps writing to ldf and as the spaces increase the physical file fragmentation can occur.

So, it is a best practice to BACKUP the transaction logs for every now and then, recommended to be less than 5 mins.

Example:

In this article I am going to show you how we can synthesize the problem and give a real time solution to it.

I have an update statement fired on one of the table, as it is running on batch loop using GO for a million times, this can do our job to grow the log space.

update dbo.history

set Entry_date=h.entry_date

from dbo.history h

where callid=callid

and unitid=unitid

go 1000000

Here is the log space after this command.

Here is how we set this number down by running a full back backup and then take a log backup.

-- Backing up a database to disk with noformat  to add to existing file

BACKUP DATABASE DB to disk = 'C:\Temp\db.bak'

WITH NOFORMAT

--backing up the log files to a disk(set the recover mode to full and also take a full backup before you run the log backup)

ALTER DATABASE DB SET RECOVERY FULL

BACKUP LOG DB to disk = 'C:\Temp\db.Trn’

WITH NOFORMAT

OUTPUT:

Processed 92089 pages for database 'DB', file 'DB_log' on file 3.

BACKUP LOG successfully processed 92089 pages in 4.278 seconds (168.172 MB/sec).

After backing up the log files to a disk if you want to shrink the log file you can use dbcc shrink file which sets the database log to size we specify.

DECLARE @db NVARCHAR(50) = 'db',

                @size VARCHAR(10) = '100',

                @sql NVARCHAR(4000);

SET @sql = 'use [' + @db + ']; '

EXEC sp_executesql @sql

SET @sql = '

alter database [' + @db + ']

set recovery simple;'

EXEC sp_executesql @sql

SET @sql = 'dbcc shrinkfile (' + @db + '_log ,' + @size + ');'

EXEC sp_executesql @sql

OUTPUT:

DbId      FileId     CurrentSize        MinimumSize    UsedPages         EstimatedPages

14           2              14048                    128                         14048                    128

Here's how it looks after shrinking and log backup 

To know more about the shrinking a log file please go through the below article.

https://learnerssql.blogspot.com/2017/01/how-to-shrink-log-file-data-file-or.html