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


Log files running out of space after rebuild


Log files running out of space after rebuild

Author
Message
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 971
Hi ....

I have an issue after running the rebuild/reorganize index maintenance job based on demand ... the log files grows very fast ..we just added new harddisk( triple size than original) a few days ago but then today ..it is running out of space again ....
we had tried shrink or backup transaction log but it doesnt help that much...

Pls kindly advice...Much appreciate it !!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232310 Visits: 46356
Don't shrink.

http://www.sqlservercentral.com/articles/Administration/64582/

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


roger.price-1150775
roger.price-1150775
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 246
If the database is in FULL recovery mode you must back up the log file. Backing up the log file will not make the file smaller but will release space within the file that will be reused.
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 971
Dear Roger and Gail Shaw

Thanks for your response..much appreciate it .. actually we perform the log backup every 3 hours but we do it using third party which is Symantec..
I am thinking to increase the FILLFACTOR becomes 90 ( previously is 80 ) so it probably will slower down the speed of data growth ?

or any other suggestion ?

Thanks heaps
Shaun Finnegan
Shaun Finnegan
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 534
Rebuilding the index will be fully logged in the transaction log. You could bring down your whole database server if you dont get this right.
Is the reason you are rebuilding related to performance issues or just doing a regular maintenance?
are you using sort_in_tempdb='ON', this may help in your case depending on where you tempdb is located and how much free disk space you have.

I'd also suggest you look at your t-log backup stragey and considering changing to backing up every 15 minutes.

SQL Server Best Practices Supporter
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 971
Thanks for your response ...
I am doing the maintenance plan due to the performance issues...
hmm at the moment we changes the t-log backup into every 2 hours and let see how it goes ...
Tonight i am going to run the rebuild of other database which has more indexes and much bigger index size...Finger cross Smile
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 971
sorry ... I hv just realized there is something that I haven't answered yet..
I perform the default rebuild indexes which has the feature ( Sort_temp = off ) and when i take a look on my temp db ( data file size is 792.56 MB and Volume usage = 0.25% ) and log file is 505MB ( volume usage = 0.16%)

meanwhile my database size is data file --> 5.36 GB ( volume usage = 1.78%)
log file 1 --> 5MB (volume usage = 0%)
log file 2 --> 15 GB ( volume usage = 9.37%)

I will rebuild around 23 indexes with each size around 0.3 GB

What do you think about it ?

thankss!!
Shaun Finnegan
Shaun Finnegan
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 534
I'd first try to update the stats on the database that has the performance issues to see if that helps. Looks like you have alot of free space on your drives, how often do you intend to do the rebuild?

Is your tempdb on a separate drive?
Out of curiosity, I see that you have two log files on your database, is there a specific reason for this?

SQL Server Best Practices Supporter
WhiteLotus
WhiteLotus
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2882 Visits: 971
Shaun Finnegan (7/15/2014)
I'd first try to update the stats on the database that has the performance issues to see if that helps. Looks like you have alot of free space on your drives, how often do you intend to do the rebuild?

Is your tempdb on a separate drive?
Out of curiosity, I see that you have two log files on your database, is there a specific reason for this?


I perform the rebuild /reorganize everyday Smile ( based on logic : rebuild if index fragmentation above 50% else reorganize)

no they are in the same drive ...FYI log 1 in the E:\ and log 2 in the G:\

hmm I guess they are afraid there will be a lot of transactions from that database so it is better to spilt it up ...

BTW can we rollback the rebuild / reorganize ?

Many thanks

Cheers
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221473 Visits: 42003
murnilim9 (7/15/2014)
sorry ... I hv just realized there is something that I haven't answered yet..
I perform the default rebuild indexes which has the feature ( Sort_temp = off ) and when i take a look on my temp db ( data file size is 792.56 MB and Volume usage = 0.25% ) and log file is 505MB ( volume usage = 0.16%)

meanwhile my database size is data file --> 5.36 GB ( volume usage = 1.78%)
log file 1 --> 5MB (volume usage = 0%)
log file 2 --> 15 GB ( volume usage = 9.37%)

I will rebuild around 23 indexes with each size around 0.3 GB

What do you think about it ?

thankss!!



Doing a log file backup once every 2 to 3 hours isn't enough especially when rebuilding/reorganizing indexes. I recommend you do T-LOG backups every 15 minutes or less. Don't worry about the number of log files. Restores through the GUI will easily take care of all that for you. If you don't trust the restore GUI, use it to build the restore script.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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