Excessive log growth in SIMPLE recovery model database - SQL 2012

Question

Excessive log growth in SIMPLE recovery model database - SQL 2012

Sam -480699

SSC Eights!

Points: 975
More actions
October 9, 2012 at 5:40 am

#268395

Hi,
Apologies if this has been posted before, but I keeping coming across excessive log growth on my reporting databases that are in SIMPLE recovery (can get to 80GB which is unusual for these databases).
These databases are large monthly reporting databases that perform many bulk inserts (via our loader) into the database throughout the day. These databases are large in size as a result for example this month’s being around 200GB so far.
Now on our 2005 server transaction logs were never a problem. However, we have recently taken the plunge and migrated to SQL 2012 with this reporting server.
Ever since then we have noticed that index rebuild \ reorganise seems to take much more log space than compared to 2005 (even when disabling the loader and ensuring nothing is reading \ writing whilst this is taking place).
However, what has thrown me is that on occasion, we can get log growth for which seems no reason.
When looking into this when it is happening the log_reuse_wait_desc on sys.databases says either:
ACTIVE_BACKUP_OR_RESTORE or LOG BACKUP. However, no backups are taking place on the server at all. And I confirm this by running:
SELECT session_id,
blocking_session_id,
command,
database_name = DB_NAME(database_id),
percent_complete,
estimated_completion_time,
wait_type,
wait_time,
wait_resource
FROM sys.dm_exec_requests
WHERE command IN (N'BACKUP DATABASE', N'BACKUP LOG');
I also check that there are no large uncommitted transactions and find none. I’m also a bit confused why it would say LOG_BACKUP in the wait description when the database is in SIMPLE recovery.
Would anyone be able to point me in right direction on what root cause could possibly be here? Or what SQL could be doing in the backend (perhaps a checkpoint of some sort?)
Any help would be appreciated.
(BTW the quick fix I found when this happens is to change the recovery model of the database to FULL, then back to SIMPLE and then shrink log, but I’m really no keen on doing this everytime!!)
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

Have you isolated what times the log grows to determin what is making it grow? If not, you can get this information from the Default Trace. Chart the trend when autogrow operations affect your log. Establishing the timeline might help you figure out what is causing it to grow.

Same exact codebase on 2012 as was on 2005? Or have changes been made since the migration?

In your index maintenance job are you doing ONLINE rebuilds now where before you were doing OFFLINE? Are you doing most re-organizes or rebuilds?

It might be worth reposting this in the SQL 2012 General Forum with a pointer back to this thread just get more eyes on it. Some folks have stopped watching the 2005 Forums.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

looping myself

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Bhuvnesh (10/10/2012)
looping myself

FYI you can use the Topic Options button on the top roght of each forum page to Subscribe to a topic without posting.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

opc.three (10/11/2012)
Bhuvnesh (10/10/2012)
looping myself
FYI you can use the Topic Options button on the top roght of each forum page to Subscribe to a topic without posting.

thanks

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sam -480699 SSC Eights! Points: 975 More actions · Answer 5

Bit of an old thread, but issue we have had looks to be a known SQL 2012 bug. I have found the blog:

http://www.travisgan.com/2013/05/sql-server-2012-simple-recovery-model.html

Seems this issue will be resolved in CU7.

Hope this helps others who encounter same issue.

Thanks,

Sam

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Sam -480699 (5/20/2013)
Bit of an old thread, but issue we have had looks to be a known SQL 2012 bug. I have found the blog:
http://www.travisgan.com/2013/05/sql-server-2012-simple-recovery-model.html
Seems this issue will be resolved in CU7.
Hope this helps others who encounter same issue.
Thanks,
Sam

Making it easier for others:

http://www.travisgan.com/2013/05/sql-server-2012-simple-recovery-model.html