Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Excessive log growth in SIMPLE recovery model database - SQL 2012 Expand / Collapse
Author
Message
Posted Tuesday, October 9, 2012 5:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:55 AM
Points: 134, Visits: 588
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
Post #1370335
Posted Wednesday, October 10, 2012 5:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 7,094, Visits: 12,582
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
Post #1370856
Posted Wednesday, October 10, 2012 11:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
looping myself

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1371221
Posted Thursday, October 11, 2012 10:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 7,094, Visits: 12,582
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
Post #1371883
Posted Friday, October 12, 2012 12:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 2,837, Visits: 3,957
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
Post #1371908
Posted Monday, May 20, 2013 4:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:55 AM
Points: 134, Visits: 588
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
Post #1454481
Posted Monday, May 20, 2013 6:55 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 23,083, Visits: 31,624
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



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)
Post #1454514
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse