August 29, 2016 at 12:24 pm
running a reindexing job on three databases sitting on a two node cluster. Reindexing job runs weekly, just fine. But, once in a while the reindexing fails, saying the transaction log is full. But the transaction log is not full. its set to unlimited growth, and there are hundreds of gigs free on the data drive.
any thoughts?
August 29, 2016 at 12:31 pm
Not really. Pretty vague on details so all you may get are wild shots in the dark.
Have you checked the SQL and system error logs to see if there may be some other errors occurring around the same time?
August 29, 2016 at 2:23 pm
Do you backup the log file on any consistent basis?
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 29, 2016 at 3:38 pm
Autogrow size too large and timing out? Too small and not adding the required space fast enough?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2016 at 6:52 pm
That's pretty simple.
During reindexing the log grows until it runs out of free space on the disk.
Then it fails and stops.
The backup job comes couple of hours later, cleans up the log and truncates the file.
By the time you check it in the morning it all looks nice and innocent.
Solution:
Add BACKUP LOG step after rebuilding of each index.
If you do log shipping then start the corresponding job step and don't proceed with a next index until it's completed.
And do not truncate log files.
You only fooling yourself by doing that.
_____________
Code for TallyGenerator
August 30, 2016 at 4:53 am
Sergiy (8/29/2016)
That's pretty simple.During reindexing the log grows until it runs out of free space on the disk.
Then it fails and stops.
The backup job comes couple of hours later, cleans up the log and truncates the file.
By the time you check it in the morning it all looks nice and innocent.
Solution:
Add BACKUP LOG step after rebuilding of each index.
If you do log shipping then start the corresponding job step and don't proceed with a next index until it's completed.
And do not truncate log files.
You only fooling yourself by doing that.
Problem is that the allocated space on the log would still be there, even if the log was truncated. From the stand point of the file system, they would see a full drive.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 30, 2016 at 5:52 am
Grant Fritchey (8/30/2016)
Problem is that the allocated space on the log would still be there, even if the log was truncated. From the stand point of the file system, they would see a full drive.
Not necessarily.
If they have log shipping going, or just log backups taken every hour or more often, and shrinking the file is included into the process, then the disk space will be deallocated after 2, or maximum 3, runs of log backups.
After 1st run most of the pages will be made inactive, but because all the active pages would be in the "tail" of the file, shrinking would not make any difference.
The new records in the log would be written at the beginning of the file. So, ideally, by the time of the next log backup the pages from the tail of the file would be moved to backup and the file could be shrunk.
But because we are talking about the situation when the database ran out of space for log records, there might be a lot of rollbacks, which take often much longer then commits, so releasing all the pages at the end of the file may take much longer.
But eventually it will be done, and SHRINKFILE will actually reduce the size of the file, releasing disk space to th OS.
_____________
Code for TallyGenerator
August 30, 2016 at 6:04 am
Sergiy (8/30/2016)
Grant Fritchey (8/30/2016)
Problem is that the allocated space on the log would still be there, even if the log was truncated. From the stand point of the file system, they would see a full drive.Not necessarily.
If they have log shipping going, or just log backups taken every hour or more often, and shrinking the file is included into the process, then the disk space will be deallocated after 2, or maximum 3, runs of log backups.
After 1st run most of the pages will be made inactive, but because all the active pages would be in the "tail" of the file, shrinking would not make any difference.
The new records in the log would be written at the beginning of the file. So, ideally, by the time of the next log backup the pages from the tail of the file would be moved to backup and the file could be shrunk.
But because we are talking about the situation when the database ran out of space for log records, there might be a lot of rollbacks, which take often much longer then commits, so releasing all the pages at the end of the file may take much longer.
But eventually it will be done, and SHRINKFILE will actually reduce the size of the file, releasing disk space to th OS.
That's if they add shrink file. That wasn't in your original answer. You just talked about the truncate, which won't result in freeing space on the drive.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 30, 2016 at 6:57 am
Grant Fritchey (8/30/2016)
That's if they add shrink file. That wasn't in your original answer. You just talked about the truncate, which won't result in freeing space on the drive.
Probably not the most careful choice of words from my side, but honestly - what is another possible meaning of "truncate" in application to a log file?
Is there any way of truncating it not as part of shrinking?
_____________
Code for TallyGenerator
August 30, 2016 at 7:01 am
Sergiy (8/30/2016)
Grant Fritchey (8/30/2016)
That's if they add shrink file. That wasn't in your original answer. You just talked about the truncate, which won't result in freeing space on the drive.Probably not the most careful choice of words from my side, but honestly - what is another possible meaning of "truncate" in application to a log file?
Is there any way of truncating it not as part of shrinking?
Yeah, it truncates internally once all the transactions are committed and any log shipping or AG work is complete. I thought that was what you meant.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
August 30, 2016 at 7:13 am
Sergiy (8/30/2016)
Grant Fritchey (8/30/2016)
That's if they add shrink file. That wasn't in your original answer. You just talked about the truncate, which won't result in freeing space on the drive.Probably not the most careful choice of words from my side, but honestly - what is another possible meaning of "truncate" in application to a log file?
'Truncate' as applied to a log file, means to make VLFs reusable (as in BACKUP LOG ...TRUNCATE ONLY), not shrink the file. When we talk about 'truncating the log' it means to make space inside the file by marking 0 or more VLFs as reusable. Checkpoint does the log truncation in simple recovery, log backups in full/bulk-logged
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply