Log File continue growing
-
Hello everyone,
i read a lot about growing Log Files on this forum but can´t find a solution for my problem.
I moved a database from one server to another, cause there were several performance problems on that old server.
Since i moved the database the lfd file is continue growing. Database (mdf) file is 38gb big.
The log file is after aprox 45 days 620gb big...
The log file get a backup every 15 minutes. When i look with DBCC LOGINFO there are 417 rows and all of them have status 2. The database is not replicated or in an group.
Some information about the server:
Windows Server 2022 21H2
SQL Server 2019 15.0.4298.1
Recovery model "FULL"
Server Agent Job that reorganize index and update statistics every night and one job that rebuild index every week (suggestion of application manufacturer).
Let me know if you need more information.
Hope someone can help me.
There are no open transactions (DBCC OPENTRAN) and no other activities while backuping (sp_whoisactive).
-
Hi,
you can check why your logfile won't be "free" inside:
SELECT
name AS DatabaseName,
log_reuse_wait_desc AS LogBlocking,
recovery_model_desc AS RecoveryModel
FROM sys.databases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb', 'maintenancedb') AND log_reuse_wait_desc NOT LIKE 'NOTHING'Maybe you can see some reason, why our logfile will growth although you made backups.
Good luck,
Andreas
-
Thanks Andreas. No results for the query
greets
Chris
-
SELECT
name AS DatabaseName,
log_reuse_wait_desc AS LogBlocking,
recovery_model_desc AS RecoveryModel
FROM sys.databasesWhat is the results from the query above? And what is the name of the database in question with the large log?
Your doing log backups every 15 minutes, but what is the command your using to do your log backups? Do you add in the copy_only flag or is it an implied copy_only backup?
-
When the backup is running the query results the above
i use the transaction log shipping option to do backups. tried it with a job but that also doesnt work
-
And the LSBackup job is that reporting success or failure when running?
Could you supply the output of this code?
USE [ANTRAGO]
GO
SELECT
DatabaseName = DB_NAME(),
FileID = df.file_id,
LogicalName = df.name,
PhysicalName = df.physical_name,
FileSize_MB = CONVERT(DECIMAL(12, 2), ROUND(df.size / 128.000, 2)),
SpaceUsed_MB = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(df.NAME, 'SpaceUsed') / 128.000, 2)),
FreeSpace_MB = CONVERT(DECIMAL(12, 2), ROUND((df.size - fileproperty(df.NAME, 'SpaceUsed')) / 128.000, 2)),
PercentageGrowth = CASE is_percent_growth WHEN 1 THEN Yes ELSE No END,
SizeOfNextGrowth_MB = CASE is_percent_growth WHEN 1 THEN ((CONVERT(DECIMAL(12, 2), ROUND(df.size / 128.000, 2)) /100) * df.growth) ELSE ((df.growth * 8.0)/1024.0) END
FROM sys.database_files df; -
Backup is working fine
Result of the query enclosed
-
Anything holding a transaction open?
Do you have SP_WHOISACTIVE or SP_BLITZWHO scripts available?
-
Yea having sp_whoisactive
Right now there are some results but all are sleeping or suspended
In evening there are no results (open transactions)
-
Your log backups are small, but your log used it large, so something must be preventing log re-use.
exec sp_whoisactive @show_sleeping_spids = 2, @get_transaction_info = 1
Anything sleeping with an open transaction?
You're going to need to keep monitoring the log_reuse_wait_desc in sys.databases see what is not allowing re-use
-
i´m getting 221 rows
there are several transactions sleeping with open_tran_count 1 and implicit tran off
edit: log_reuse_wait_desc is still "NOTHING" for that database
-
So i took the database offline and dropped all connections and took the database online again.
When the BACKUP_LOG starts there were no open transactions and the database was on log_reuse_wait_desc NOTHING switched while the backup process to BACKUP_LOG for a second and than back to NOTHING
while this process no transaction sleeping or whatever were detected for the database-> exec sp_whoisactive @show_sleeping_spids = 2, @get_transaction_info = 1
and DBCC LOGINFO still shows me that all results in status 2...
-
If you look for the accepted answer at the following link...
... it says...
Status = 2 means that VLF can't be reused (overwritten) at this time and it doesn't
necessarily mean that VLF is still active and writing transactions to that VLF.
As Jonathan already mentioned, it means that the VLF is waiting for backup/REPL/Mirroring etc...Since you used "transaction log shipping option to do backups", I'm thinking you've pretty much screwed yourself.
Turn that off and start doing normal backups. If you have that stuff turned on because you actually want to do log shipping, then a backup isn't going to clear things until you've actually shipped the log. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the advice.
So i read this before and did a log backup.
Backup Type: Transaction Log
It doesnt work... Or do you mean anything else?
Edit: So i turned the log shipping off now.
- datkop wrote:
Thanks for the advice.
So i read this before and did a log backup.
Backup Type: Transaction Log
It doesnt work... Or do you mean anything else?
Edit: So i turned the log shipping off now.
I'm not sure what you mean so I'll just say what needs to be done.
Your current logfile is pretty useless simply because of it's size. You could change to the SIMPLE recovery model, do a "checkpoint", and that will pretty much truncate your log file. You'll still need to shrink it to something normal after that.
Then, you need to change back to the FULL recovery model, do a Full Backup, then do a Transaction Log Backup to get the log chain going again and to protect the database.
If you need a little help, you can right click on a database in the Object Explorer window in SSMS and a menu will pop up. Then click on [tasks] and another menu will pop up. Then click on [backup] and follow your nose.
Use that to do both a FULL backup first and then again to do a log file backup. That will get you out of the woods with your log file
I normally don't recommend "Maintenance Plans" but you either setup setup a maintenance plan for your backups or do a search for Ola Hallengren's Backup Solution.
Or, do like I did and "roll your own" but it doesn't sound like that's an option here.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply