December 4, 2017 at 8:56 pm
HI All
I have a repeat incident in my database server
Transaction log is getting full pretty quickly and fill up the disk space .
I have checked the log backup(every hour) and full backup(daily) running well
also log file has autogrowth 20MB restricted to 2TB
so far no Open transaction that i can see
no database mirroring in the server
What would be the cause and the best solution for this ?
really appreciate the feedback
Thanks a lot
December 4, 2017 at 11:53 pm
WhiteLotus - Monday, December 4, 2017 8:56 PMHI AllI have a repeat incident in my database server
Transaction log is getting full pretty quickly and fill up the disk space .
I have checked the log backup(every hour) and full backup(daily) running well
also log file has autogrowth 20MB restricted to 2TB
so far no Open transaction that i can see
no database mirroring in the serverWhat would be the cause and the best solution for this ?
really appreciate the feedback
Thanks a lot
You got two options, either increase the frequency of the transaction log backup or increase the log space, recommend you do the former.
😎
December 5, 2017 at 12:31 pm
The cause would be that size is what is needed to support the activities for hourly log backups. Increase the number of backups and/or increase the size of the log.
Also that growth increment you are using is pretty small and may not be appropriate.
Sue
December 5, 2017 at 1:26 pm
If it is filling up your drive you may need to move the log to a bigger disk. Also, I agree with the others that you should look at increasing the frequency of your log backups . Also, have you checked the value of the log_reuse_wait_desc column in sys.databases for the database in question?
December 5, 2017 at 1:43 pm
Do you know when the log file is growing? If not, you might be able to look in the default trace to see log growth events:SELECT td.DatabaseName, td.Filename, te.name AS Event, (IntegerData*8)/1024 AS Change_MB, td.StartTime, (Duration/1000) AS Duration_sec,
td.LoginName, td.HostName, td.ApplicationName, td.spid, td.ClientProcessID, td.IsSystem, td.SqlHandle, td.TextData
FROM sys.traces t
CROSS APPLY ::fn_trace_gettable(t.path, default) td
INNER JOIN sys.trace_events te ON td.EventClass = te.trace_event_id
WHERE t.is_default = 1
AND td.EventClass = 93
ORDER BY td.StartTime;
which can be further filtered by td.DatabaseName in the WHERE clause. This also shows the login and sometimes helpful application information to track down why it is happening.
December 5, 2017 at 5:14 pm
WhiteLotus - Monday, December 4, 2017 8:56 PMHI AllI have a repeat incident in my database server
Transaction log is getting full pretty quickly and fill up the disk space .
I have checked the log backup(every hour) and full backup(daily) running well
also log file has autogrowth 20MB restricted to 2TB
so far no Open transaction that i can see
no database mirroring in the serverWhat would be the cause and the best solution for this ?
really appreciate the feedback
Thanks a lot
Before we get into all the other stuff, let's first correctly identify the problem.
1. Lets start with the most obvious question... How much disk space do you have allotted to the drive the transaction log file is on?
2. Next, how big is the largest index on the database?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2018 at 5:13 pm
Guys...
I think I need to change the autogrowth setting as currently is very small . only 5 MB and db size is 150 GB
cheers
January 16, 2018 at 5:28 pm
WhiteLotus - Tuesday, January 16, 2018 5:13 PMGuys...I think I need to change the autogrowth setting as currently is very small . only 5 MB and db size is 150 GB
cheers
You may have another problem, too many very small VLF's. I don't have a link handy but you should Google it.
January 16, 2018 at 5:33 pm
How do you know that there is no open transaction?
Auto growth rate has nothing to do with the log size. Are you using replication? A suspended replication would prevent log from being truncated.
January 16, 2018 at 6:02 pm
RandomStream - Tuesday, January 16, 2018 5:33 PMHow do you know that there is no open transaction?
Auto growth rate has nothing to do with the log size. Are you using replication? A suspended replication would prevent log from being truncated.
Autogrowth rate has a lot to do with both the size and the number of VLFs in the Log File. The usual case is way too many at the beginning and way too few at the end.
I'm also a bit confused as to why you say the auto growth rate has nothing to do with the log size. If it's set incorrectly, it could have a whole lot to do with the log size.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2018 at 6:06 pm
WhiteLotus - Tuesday, January 16, 2018 5:13 PMGuys...I think I need to change the autogrowth setting as currently is very small . only 5 MB and db size is 150 GB
cheers
What happened to the 20MB you claimed the setting was at last month?
I agree with Lynn... Google for "Kimberly Tripp VLF" for more information.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2018 at 10:23 am
Jeff Moden - Tuesday, January 16, 2018 6:02 PMRandomStream - Tuesday, January 16, 2018 5:33 PMHow do you know that there is no open transaction?
Auto growth rate has nothing to do with the log size. Are you using replication? A suspended replication would prevent log from being truncated.Autogrowth rate has a lot to do with both the size and the number of VLFs in the Log File. The usual case is way too many at the beginning and way too few at the end.
I'm also a bit confused as to why you say the auto growth rate has nothing to do with the log size. If it's set incorrectly, it could have a whole lot to do with the log size.
The essence of OP's question was that log backup wasn't doing its job - backing up committed transactions and reuse those space. My thought was how did the OP confirm that there were no open transactions? He/she could've been wrong in that determination. I understand where you come from on the effect of a large number of VLF's and on a second thought, my statement was an over simplification and misleading. However, in an urgent situation, I'm inclined to focus on what could've caused this immediate problem, not what settings has led to this occurrence over time. Hence the quick answer and less than thoughtful statement.
Anyway, back to helping WhiteLotus...
Please try the following and post results:
DBCC SQLPerf(Logspace)
USE [Your db name]
GO
DBCC LogInfo
GO
SELECT DB_NAME(db.database_id) AS [Database],
db.recovery_model_desc AS [RecoveryModel],
db.log_reuse_wait_desc AS [ReuseWaitDesc]
FROM sys.databases AS db WITH (NOLOCK)
Also, are you using replication at all?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply