Transaction log grows. Strangely problem

Question

Transaction log grows. Strangely problem

dmbsseller

Old Hand

Points: 304
More actions
October 12, 2017 at 5:35 am

#324077

Hi Guys!
I have a really strangely problem.
I have Hyper-V virtual server with installed Microsoft SQL Server 2012 (SP3-CU10) (KB4025925),
Database -
'MyDatabase'.mdf - 242 GB;
'MyDatabase'.ldf - 460 GB;
Note:
My transaction log file almost twice as many datafile, its not a good i think so.
Recovery model -
FULL;
Backup plan -
log backup - every hour during a day. (8 00 - 19 00);
differential backup - every day during a week. (21:00);
full backup - every week during a month (sunday 18:00);
Also, in Sunday, when i do full backup, im doing some following procedurs:
1) Check Database Integrity
2) Ola hallangren script - index rebuild/reorganization
3) Full backup
I dont understand why my transaction log grow... (my model is FULL, i do log backup)
I have a monitoring system, and i saw one interesting moments. I have had 420 GB free space on my disc , in Monday in 20:00 place decreased until 110 GB!!!
Its surprised for me, because this is a non-working time and nobody works with the database.
To solve this problem i think doing following procedurs:
DBCC SHRINKFILE (MyDatabase_log, 2048);

But I dont understand why log backup do not reduce the size of the transaction log?

Thanks for help..

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

What is running at 20:00 on a Monday? It looks like that might be the culprit and don't shrink your database until you understand what is causing it to grow as it will just grow again.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

dmbsseller - Thursday, October 12, 2017 5:35 AM
But I dont understand why log backup do not reduce the size of the transaction log?

Because it doesn't. The log backup reduces the amount of data in the transaction log, but not the size of the log on disk. Think about it - if it reduced the size also, your log would be constantly growing and shrinking, using up system resources and causing fragmentation on your disk.

John

dmbsseller Old Hand Points: 304 More actions · Answer 3

NorthernSoul - Thursday, October 12, 2017 5:40 AM
What is running at 20:00 on a Monday? It looks like that might be the culprit and don't shrink your database until you understand what is causing it to grow as it will just grow again.
Thanks

Hey!
This is one time event! (no every Monday)
All procedurs for maintenance are made in Sunday (on this day no one work)
Until 18 september i have 420 GB , its start in 18 september in 20:00 PM and continued before 3 AM and my free disc space decreased until 110 GB and
'MyDatabase'.mdf - 242 GB;
'MyDatabase'.ldf - 460 GB;

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 4

dmbsseller - Thursday, October 12, 2017 5:58 AM
NorthernSoul - Thursday, October 12, 2017 5:40 AM
What is running at 20:00 on a Monday? It looks like that might be the culprit and don't shrink your database until you understand what is causing it to grow as it will just grow again.
Thanks
Hey!
This is one time event! (no every Monday)
All procedurs for maintenance are made in Sunday (on this day no one work)
Until 18 september i have 420 GB , its start in 18 september in 20:00 PM and continued before 3 AM and my free disc space decreased until 110 GB and
'MyDatabase'.mdf - 242 GB;
'MyDatabase'.ldf - 460 GB;

So, what was running that caused the log to grow? Are you sure that it was a one-off event?

Thanks

dmbsseller Old Hand Points: 304 More actions · Answer 5

Actually i doubt.
before 18 september - 420GB
with 18 september until 19 september - 110 GB
with 19 september until 10 october free space did not change.
And 10 october space decreased until 92 GB. And now space is 92 GB.

No errors in windows events , no errors and strangely events in sql error log.
All sql jobs, all maintenance plans work correctly!

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 6

Am I reading your backup / maintenance schedules correctly? You're only doing TLog backups during the work day, and not during database maintenance routines, such as index rebuilds?

Indexes maintenance can be transaction-log heavy. Indeed, I have in the past set transaction log backups to run every minute during index maintenance windows in order to reduce the disk space taken up by the TLog file.

Thomas Rushton
blog: https://thelonedba.wordpress.com

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 7

dmbsseller - Thursday, October 12, 2017 6:18 AM
Actually i doubt.
before 18 september - 420GB
with 18 september until 19 september - 110 GB
with 19 september until 10 october free space did not change.
And 10 october space decreased until 92 GB. And now space is 92 GB.
No errors in windows events , no errors and strangely events in sql error log.
All sql jobs, all maintenance plans work correctly!

There won't be errors because nothing is going wrong, it's behaving exactly as it should. Have a look back at the log growth events and find out what was running on the database at that time and causing the log to grow.

Is there a problem with having a large log file? Are you running out of space on the disk?

Thanks

dmbsseller Old Hand Points: 304 More actions · Answer 8

ThomasRushton - Thursday, October 12, 2017 6:22 AM
Am I reading your backup / maintenance schedules correctly? You're only doing TLog backups during the work day, and not during database maintenance routines, such as index rebuilds?
Indexes maintenance can be transaction-log heavy. Indeed, I have in the past set transaction log backups to run every minute during index maintenance windows in order to reduce the disk space taken up by the TLog file.

Yes you are right!

dmbsseller Old Hand Points: 304 More actions · Answer 9

NorthernSoul - Thursday, October 12, 2017 6:24 AM
dmbsseller - Thursday, October 12, 2017 6:18 AM
Actually i doubt.
before 18 september - 420GB
with 18 september until 19 september - 110 GB
with 19 september until 10 october free space did not change.
And 10 october space decreased until 92 GB. And now space is 92 GB.
No errors in windows events , no errors and strangely events in sql error log.
All sql jobs, all maintenance plans work correctly!
There won't be errors because nothing is going wrong, it's behaving exactly as it should. Have a look back at the log growth events and find out what was running on the database at that time and causing the log to grow.
Is there a problem with having a large log file? Are you running out of space on the disk?
Thanks

Yes, unfortunatelly problem in a free space on a disk!

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 10

Ok and have you been able to find out what is running at the times when you see growth? Is it index maintenance?

Thanks

dmbsseller Old Hand Points: 304 More actions · Answer 11

NorthernSoul - Thursday, October 12, 2017 6:59 AM
Ok and have you been able to find out what is running at the times when you see growth? Is it index maintenance?
Thanks

No, its not an index maintenance, because this procedurs running only Sunday.
How can I see what transactions were recorded in the transaction log on Monday at 20:00 PM?

SQLPirate SSCrazy Points: 2880 More actions · Answer 12

dmbsseller - Thursday, October 12, 2017 7:28 AM
No, its not an index maintenance, because this procedurs running only Sunday.
How can I see what transactions were recorded in the transaction log on Monday at 20:00 PM?

Assuming you still have the log backups covering the period, you can use fn_dump_dblog to browse through the log to see what happened.

dmbsseller Old Hand Points: 304 More actions · Answer 13

SQLPirate - Thursday, October 12, 2017 7:46 AM
dmbsseller - Thursday, October 12, 2017 7:28 AM
No, its not an index maintenance, because this procedurs running only Sunday.
How can I see what transactions were recorded in the transaction log on Monday at 20:00 PM?
Assuming you still have the log backups covering the period, you can use fn_dump_dblog to browse through the log to see what happened.

No, i didnt have log backup for this period..
But how can I solve my problem?
My transaction log still - 460 GB...
I understand that I have only one way out - change model to SIMPLE and run DBCC SHRINKFILE (MyDatabase_log, 2048), after change model to FULL.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 14

dmbsseller - Friday, October 13, 2017 3:16 AM
SQLPirate - Thursday, October 12, 2017 7:46 AM
dmbsseller - Thursday, October 12, 2017 7:28 AM
No, its not an index maintenance, because this procedurs running only Sunday.
How can I see what transactions were recorded in the transaction log on Monday at 20:00 PM?
Assuming you still have the log backups covering the period, you can use fn_dump_dblog to browse through the log to see what happened.
No, i didnt have log backup for this period..
But how can I solve my problem?
My transaction log still - 460 GB...
I understand that I have only one way out - change model to SIMPLE and run DBCC SHRINKFILE (MyDatabase_log, 2048), after change model to FULL.

Don't shrink your log until you've found out why it grew in the first place as it's likely to grow again. Do you have any maintenance tasks or SQL Agent jobs running at the time when you've seen growth?

Thanks