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


Index maintenance increase log size


Index maintenance increase log size

Author
Message
S_Kumar_S
S_Kumar_S
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1085
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
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34347 Visits: 16649
Is your database in Full recovery mode, and if so, how often do you back up the transaction log?

John
S_Kumar_S
S_Kumar_S
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1085
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
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34347 Visits: 16649
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
OnlyOneRJ
OnlyOneRJ
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 692
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 !!!!! :-D
S_Kumar_S
S_Kumar_S
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1085
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
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34347 Visits: 16649
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94049 Visits: 38955
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 .

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)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222330 Visits: 46287
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, MVP, M.Sc (Comp Sci)
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


S_Kumar_S
S_Kumar_S
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1981 Visits: 1085
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
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