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 123»»»

Index maintenance increase log size Expand / Collapse
Author
Message
Posted Monday, April 29, 2013 5:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 380, Visits: 899
In SQL Server 2008R2, we are running an Index maintenance job on Sunday.It rebuilds/reorganize the indexes ONLINE on a database in a loop. The max size in Indexes is 16GB , but my log size grows upto 160 GB.There are around 200 indexes. I understand that ONLINE rebuild causes log size to grow but I also think that once the rebuild of one index is complete, the log space should be available for reuse but that doesn't seem to be happening. Can it be a reason that since rebuild happens in a loop and loop runs fast , so essentially there are several rebuilds happening in parallel? Will putting a waitperiod of ,say 10 sec, after each rebuild help?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1447456
Posted Monday, April 29, 2013 5:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 5,437, Visits: 10,129
Is your database in Full recovery mode, and if so, how often do you back up the transaction log?

John
Post #1447460
Posted Monday, April 29, 2013 5:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 380, Visits: 899
yes, it's in full recovery and log is backed every 20 minutes. I checked this and log backups are happening fine every 20 min

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1447462
Posted Monday, April 29, 2013 6:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 5,437, Visits: 10,129
Once the rebuild of any index has used up space in your transaction log, that space won't be freed up again until the transaction log is backed up. You have several options:
(1) Allow more space for your transaction log
(2) Back up your log more frequently during index maintenance
(3) Use an index maintenance script that only rebuilds or reorganises indexes that are fragmented.

John
Post #1447466
Posted Monday, April 29, 2013 6:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
John Mitchell-245523 (4/29/2013)
Once the rebuild of any index has used up space in your transaction log, that space won't be freed up again until the transaction log is backed up. You have several options:
(1) Allow more space for your transaction log
(2) Back up your log more frequently during index maintenance
(3) Use an index maintenance script that only rebuilds or reorganises indexes that are fragmented.

John


Hi John,

Consider a scenario where in..
u have to Create a DB & then backup the Logs every day... then what would you suggest for the mdf & ldf files to be.. in terms of Size..
Like while creating the DB.. in properties u have an option to change & set the size..
1) Initial Size
2) AutoGrowth..

Please suggesta best method of setting this for smooth execution...








************************************
Every Dog has a Tail !!!!!
Post #1447469
Posted Monday, April 29, 2013 6:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 380, Visits: 899
Hi
I don't have the option 1.
Option 3 , I am already doing.
So I am left with option 2 only. Here also, the log size takes 2 hours to grow to 160 GB. So when log backups are happening every 20 min, then it shouldn't grow to that size, right?

John Mitchell-245523 (4/29/2013)
Once the rebuild of any index has used up space in your transaction log, that space won't be freed up again until the transaction log is backed up. You have several options:
(1) Allow more space for your transaction log
(2) Back up your log more frequently during index maintenance
(3) Use an index maintenance script that only rebuilds or reorganises indexes that are fragmented.

John


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1447470
Posted Monday, April 29, 2013 6:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 5,437, Visits: 10,129
Maybe you've got an uncommitted transaction or something like that. How much free space is there in the log file after the index maintenance has finished and the log has been backed up?

John
Post #1447474
Posted Monday, April 29, 2013 6:32 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 20,806, Visits: 32,738
If t-log growing to 160GB over 2 hours and you are running t-log backups every 20 minutes, sort of sounds like the index maintenance process may be running as a single transaction. This is only a guess since we can't see the routines you are running .



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 #1447478
Posted Monday, April 29, 2013 6:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 40,428, Visits: 36,878
S_Kumar_S (4/29/2013)
So I am left with option 2 only. Here also, the log size takes 2 hours to grow to 160 GB. So when log backups are happening every 20 min, then it shouldn't grow to that size, right?


If you're rebuilding a 100+GB index during those two hours then yes, it should grow to that size. For index maintenance you need space in the log > total size of the largest index you're rebuilding.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1447479
Posted Monday, April 29, 2013 6:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 380, Visits: 899
I don't think there are any long running transaction that time when maintenance is happening.Once the maintenance is over after 2 hrs, and if I run DBCC SQLPERF(Logspace), the used space is less than 1%. That means almost all space is free. Although I never checked the space after each transaction log backup.




John Mitchell-245523 (4/29/2013)
Maybe you've got an uncommitted transaction or something like that. How much free space is there in the log file after the index maintenance has finished and the log has been backed up?

John


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1447488
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse