SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Excessive log growth in SIMPLE recovery model database - SQL 2012


Excessive log growth in SIMPLE recovery model database - SQL 2012

Author
Message
Sam -480699
Sam -480699
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 651
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
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51406 Visits: 14413
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
Bhuvnesh
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16552 Visits: 4077
looping myself

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51406 Visits: 14413
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
Bhuvnesh
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16552 Visits: 4077
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
Sam -480699
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 651
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
Lynn Pettis
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116888 Visits: 39107
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

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search