backup the log - does not decrease the file size

Good Morning:

i am using SQL Server 2016 Enterprise / Standart
the LOG file is big, we are talking in Gigas, I think it's a lot, I make LOG backup every hour but the size of the log file is the same (it does not decrease)

DECLARE @FECHALOG VARCHAR(20)
DECLARE @ARCHIVO_BK_LOG VARCHAR(500)
DECLARE @NAME VARCHAR(500)
DECLARE @DESCRIPCION VARCHAR(500)
DECLARE @MEDIANAME VARCHAR(500)
DECLARE @NOMBREBD VARCHAR(100)
DECLARE @CARPETABK     VARCHAR(100)
DECLARE @RUTARAIZBK NVARCHAR(100) = 'V:\BACKUP\'

/*BD Loyalty*/
SELECT @NOMBREBD = UPPER(name) FROM sys.databases WHERE UPPER(name) LIKE N'%MIBD'
SET @CARPETABK = CONCAT(@RUTARAIZBK, @NOMBREBD, '\')
EXECUTE master.dbo.xp_create_subdir @CARPETABK
SELECT @FECHALOG = CONCAT(RIGHT('00' + CAST(DAY(GETDATE()) AS VARCHAR(2)) , 2), RIGHT('00' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) , 2), YEAR(GETDATE()), RIGHT('00' + CAST(DATEPART(HOUR,GETDATE()) AS VARCHAR(2)) , 2), RIGHT('00' + CAST(DATEPART (MINUTE,GETDATE()) AS VARCHAR(2)) , 2))
SET @ARCHIVO_BK_LOG  = CONCAT(@CARPETABK, @NOMBREBD, '_LOG_', @FECHALOG, '.trn')
SET @NAME = CONCAT('Backup LOG ', @NOMBREBD)
SET @DESCRIPCION = CONCAT('Backup LOG de la BD ', @NOMBREBD, ' dia y hora ', @FECHALOG)
SET @MEDIANAME = CONCAT('Backup ', @NOMBREBD, ' LOG')
BACKUP LOG @NOMBREBD TO DISK = @ARCHIVO_BK_LOG
     WITH FORMAT,
     NAME = @NAME,
     DESCRIPTION = @DESCRIPCION,
     MEDIANAME = @MEDIANAME
GO

Open in new window

enrique_aeoAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Backing up the log does not reduce size, it does truncate internally but that just means that there is internal space to use for log activity.

Physical size is the amount of disk allocated to a file. Sql Server then proceeds to fill it up and grow if possible. It wont necessarily auto shrink (which should be banned)  - unless running SIMPLE recovery and restarting SQL Server - as in tempdb. Once the transaction log has an allocated size, then that is what it has available to consume.

There are some conditions which might stop the Transaction Log behaving as expected....

Have a read of  https://www.experts-exchange.com/articles/657/Managing-the-Transaction-Log-for-the-Accidental-DBA.html
0
 
Adam BrownSr Solutions ArchitectCommented:
What is your recovery model set to? It will definitely need to be set to full if it isn't already.
0
 
65tdRetiredCommented:
Have you tried shrinking the log file from the management console?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
enrique_aeoAuthor Commented:
recovery model = FULL

can you give me t-sql shrinking ?
Why size does not decrease when I do the LOG backup?
0
 
65tdRetiredCommented:
0
 
65tdRetiredCommented:
Why size does not decrease when I do the LOG backup?

See this comment:
Log backups don't shrink the log file, hence you shouldn't expect to see the log file get smaller. Log backups just mark portions of the log reusable.

If you see that the log is not being reused after a log backup and the log file keeps on growing, then please take a read through this: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
0
 
Bharat BhushanSolution ManagerCommented:
Hi,

If you are trying to shrink the log file then:

  1. Backup your database
  2. Stop the database services
  3. Shrink the LDF file
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
I agree. Backing up the log file does not physically reduce the file size. The operation only makes it possible for SQL Server to reuse the existing file rather than having to go to the OS requesting a file size increase (which is a costly operation).

That being said, it is also possible that your configuration has replication, mirroring/AG or log shipping in place. If the target servers are down or not processing, it prevent the log from clearing out.
0
 
Mark WillsTopic AdvisorCommented:
Easiest is to use SSMS
Right click on DBname, => tasks => Shrink => Files
Choose transaction log (filename drop down)
Then shrink option should be REORG before releasing unused space.

In T-SQL (shrink,backup,shrink) and make sure you are doing full backups and log backups
DBCC SHRINKFILE(<TransactionLogName>, 1)                         
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Open in new window

And to find the log name to use
 SELECT name FROM sys.master_files WHERE type_desc = 'LOG'

Open in new window


But it will start growing again which is why you must manage the transaction log. Again, please read  https://www.experts-exchange.com/articles/657/Managing-the-Transaction-Log-for-the-Accidental-DBA.html

And please get back to us and let us know what is happening....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.