Log Size is too high

Question

Log Size is too high

Arsh

SSCertifiable

Points: 6354
More actions
December 22, 2022 at 5:49 am

#4128732

Hi Friends,
I have been assigned to manage an SQL server pool with some standalone and with Log shipped to a DR. I noticed one of them has two databases that have over 100GB of logfiles. Few others are high too ,at 50GB around. Would really appreciate if you can comment on this.Thank you.
Best Regards..Arshad

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Arsh SSCertifiable Points: 6354 More actions · Answer 1

Log contents only have 214 rows . Majority of the operations are for LOP_MOFIFY_ROW etc . I have seen for LOP_SHRINK_NOOP.

Thanks...Arshad

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Firstly do you know what caused the log file to grow that large?

Without knowing the cause, there is a great possibility that the log will once again reach the size you are seeing now?

Was it log miss management where no one was backing up the log? A maintenance process like index rebuild / reorg? Some bulk load?

Once you know the cause and you have fixed it, can you then look at shrinking the log, or if it is maintenance related, leave it alone.

There is a reason the log grew that much and you need to find it before shrinking, but without any historical monitoring of the instance finding it may be impossible.

If you are new to managing this server setup, I would put some monitoring in around the default trace for file growth events so you can track what operations are growing the log.

Then you may proceed with a "one off" shrink of the log file using DBCC SHRINKFILE(2,<target size in mb>) within the context of the databases you which to shrink the log of.

Then monitor if the log grows again and why it is growing from the default trace.

Note I mention a one off shrink, as I say there is a reason the log got that big, shrinking the log all the time without fixing the problem is futile, if the log needs to be a particular size, let it stay at that particular size, don't keep shrinking it.

Also it would be beneficial having a read of the "transaction log" sections of the following books, I still refer to these from time to time

https://www.sqlservercentral.com/books/troubleshooting-sql-server-a-guide-for-the-accidental-dba

https://www.sqlservercentral.com/books/sql-server-transaction-log-management-by-tony-davis-and-gail-shaw

Arsh SSCertifiable Points: 6354 More actions · Answer 3

Hi @Ant-green,

I actually acquired this newly. I am sure there was no analysis ever done. But yes , there is log shipping enabled and the Log backup setup within the log shipping settings, is every 1 Hr.|Thank you.

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

OK, that doesn't answer any of the questions above.

If you don't know what caused the log to grow so much, then you need to enable monitoring into the growth events.

Once you have done that, you can do a "one off" shrink and let the log grow back to the size it needs to be.

If it grows to 100GB again, you know there is a process which needs it to be at 100GB, so then armed with the monitoring knowledge of what caused the growths you can see if there is anything you need to tune to not use as much log space.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

Here are a few pointers for this common problem. Chances are very high that your databases are in FULL recovery mode, but that you don't have log backups set up. Or, possibly, that your log backups are running too infrequently.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Arsh wrote:

Hi Friends,
I have been assigned to manage an SQL server pool with some standalone and with Log shipped to a DR. I noticed one of them has two databases that have over 100GB of logfiles. Few others are high too ,at 50GB around. Would really appreciate if you can comment on this.Thank you.
Best Regards..Arshad

How big are the database files for the two databases that have 100GB log files and the "few others" that have "at 50GB around"?
How often are you doing Transaction Log backups on all of those?
What does your Index Maintenance look like? Does it follow the pattern of using REORGANIZE between 5 and 30% and doing REBULDs over 30% Logical Fragmentation? If not, please explain what your Index Maintenance plan follows and, if you aren't doing an Index Maintenance (which can be a really good thing), please state that, as well.

--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)

Arsh SSCertifiable Points: 6354 More actions · Answer 7

Well, I guess the log files have not been actually growing fast given a nominal size-increase I noticed last few days.So , as you said, they might just be normal but one I saw has a log file of 100 GB but a datafile of 97 GB which shouldn't look strange too, as the log can grow but the actual data in the data files can still be less than log.

For the questions u asked , below are my answers :

1.The data file sizes are twice except for the one I mentioned above, with is 3 to 4% less than the log.

2.Log shipping program takes it every 1 Hr.

3.I saw an Index rebuild plan created that was re-building all indexes every week, until 11 months ago.its been kept disabled I can see.

Thank you

Arshad

3.

Arsh SSCertifiable Points: 6354 More actions · Answer 8

Well, I guess the log files have not been actually growing fast given a nominal size-increase I noticed last few days.So , as you said, they might just be normal but one I saw has a log file of 100 GB but a datafile of 97 GB which shouldn't look strange too, as the log can grow but the actual data in the data files can still be less than log.

For the questions u asked , below are my answers :

1.The data file sizes are twice except for the one I mentioned above, with is 3 to 4% less than the log.

2.Log shipping program takes it every 1 Hr.

3.I saw an Index rebuild plan created that was re-building all indexes every week, until 11 months ago.its been kept disabled I can see.

Thank you

Arshad

3.

Arsh SSCertifiable Points: 6354 More actions · Answer 9

Hi Grant,

No , there is Log a shipping setup that backs up log every 1 Hr.thanks

Arsh SSCertifiable Points: 6354 More actions · Answer 10

@ant-green, these are the answers:

Firstly do you know what caused the log file to grow that large? - I inherited just a week ago , and no documentation or any legacy info available.

Without knowing the cause, there is a great possibility that the log will once again reach the size you are seeing now? Its not growing alarmingly.

Was it log miss management where no one was backing up the log? A maintenance process like index rebuild / reorg? Some bulk load? - Log shipping is taking an hourly backup.

So I believe I need to monitor for sometime to see any change..till then it should be ok.

I saw the log for these databases,some hundreds of rows with modify,checkpoint,Sync etc records.anything to look for , there ? thanks.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 11

Hourly log backups may not be frequent enough. Think of it like this. If the server crashed, you may lose up to an hour of data if you couldn't get the tail log backup completed. Bump it to every 1/2 hour or even every 15 minutes. The log file size won't shrink, but they'll be using less log. You can try shrinking them after you increase the frequency of log backups.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning