Sql-server – Transaction log full due to log_backup

sql servertransaction-log

I am testing how certain operations are logged under different recovery models.Below are steps i have did till now

1.create database in full recovery model
2.take backup
3.create a table and insert 10 million records
4.Take log backup,check VLF count and see log space free percentage
5.Now do an index rebuild and see the records generated using fn_dblog function

6.Now i swicthed to bulk recovery model
7.Took a backup
8.Taken a log backup
9.did an index rebuild

Strangely index rebuild is failing with below error.

The statement has been terminated.
Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'bulklogging' is full due to 'LOG_BACKUP'.

thats not true ,actually

1.log space autogrowth is not restrcited

Autogrwoth setting

2.Space on where logfile stored

space on drive where log file is stored

Can some one help me understand why i am getting above error ,eventhough space is there and autogrowth is not restrcited

adding image of index size..

Issue got resolved,but not sure how this change made difference.Any pointers would be much appreciated..

Comment is saying two long –so posting here…

i scripted out index again ,after changing recovery model and then it worked.Moreover scripted index before and after exactly remain same,only session got changed.But i am not sure how this got worked .

Before :

USE [bulklogging]  
GO  
ALTER INDEX [PK__bcc__3213E83FAC9DB5ED] ON [dbo].[bcc] 
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

After:

USE [bulklogging]  
GO  
ALTER INDEX [PK__bcc__3213E83FAC9DB5ED] ON [dbo].[bcc] 
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Update to close this question:
I am using Fn_dblog to check log records,This seems to have a hidden Bug as described here,which may be affecting my log growth..

Edit 8/15/13: Beware – Jonathan just found out from a customer system that uses this extensively that every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. It’s a bug that the SQL team is going to fix now we’ve alerted them to it. Use with caution.

Edit 5/15/15: It’s fixed in SQL Server 2012 SP2+ and SQL Server 2014. The fix won’t be backported any earlier.

enter image description here

Best Answer

This error occurs because the transaction log becomes full due to LOG_BACKUP. Therefore, you can’t perform any action on this database, and In this case,

the SQL Server Database Engine will raise a 9002 error.

To solve this issue, you have to do the following:

  • Take a Full database backup.
  • Shrink the log file to reduce the physical file size.
  • Create a LOG_BACKUP.
  • Create a LOG_BACKUP Maintenance Plan to take backup logs frequently.

Note: The Shrink operation effects on SQL Server Performance during executing shrink command. it also causes index fragmentation and can slow the performance of queries that search a range of the index.

So, it’s recommended before goes live you should prepare LOG_BACKUP Maintenance Plan to back up the log file frequently to avoid the shrink operation on Production.

For more details check The transaction log for database ‘SharePoint_Config’ is full due to LOG_BACKUP

Hope this helps you

Related Question