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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

65tdRetiredCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.