6

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

  • From your screenshot, log growth is limited to 2 terabytes. That's probably not the problem tho. What does the log_reuse_wait_desc column in sys.databases say? – Andomar Aug 7 '14 at 18:40
  • I believe that is default setting and i think my table with 1000000 rows will generate 2 TB log file.Edited question with image now. – TheGameiswar Aug 7 '14 at 18:41
  • Agree, what does the log_reuse_wait_desc column in sys.databases say? – Andomar Aug 7 '14 at 18:44
  • it shows log_backup – TheGameiswar Aug 7 '14 at 18:45
  • 1
    i am able to repro the same behaviour,intermittently.when i rebuild index very fast(run query again with in a span of 2 minutes).even though this is not consistent,i am able to repro the behaviour only in this scenario – TheGameiswar Aug 11 '14 at 6:48
4

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.

In this case, you have to do

  • 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.

Therefore, it’s recommended before goes live you should prepare a LOG_BACKUP Maintenance Plan to backup 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 help you

0

I came across this issue today with one of my production databases. I resolved it by adding a second log file to the database. I could not take a full backup or perform a shrink of the log file as described above because when I tried to it would generate the LOG_BACKUP error message. I also attempted to extend the log file but this too generated the same error. In the end adding a second smaller log file made the database usable again - after which I was able to backup the database and resize the original log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.