Log files running out of space after rebuild

Question

Post reply

Log files running out of space after rebuild

WhiteLotus

SSChampion

Points: 12122
More actions
July 13, 2014 at 11:16 pm

#296440

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 !!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 Default port Points: 1458 More actions · Answer 2

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 SSChampion Points: 12122 More actions · Answer 3

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

ShaunF SSChasing Mays Points: 612 More actions · Answer 4

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.

WhiteLotus SSChampion Points: 12122 More actions · Answer 5

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 🙂

WhiteLotus SSChampion Points: 12122 More actions · Answer 6

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!!

ShaunF SSChasing Mays Points: 612 More actions · Answer 7

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?

WhiteLotus SSChampion Points: 12122 More actions · Answer 8

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 🙂 ( 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 SSC Guru Points: 1004412 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WhiteLotus SSChampion Points: 12122 More actions · Answer 10

Hi All,

I performed rebuild indexes on certain databases ..maybe around 22 indexes need to be rebuilt and it failed and showed an error :

/*********************************************..." failed with the following error: "Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

at the moment there are still 10 indexes that need to be rebuilt...

I am thinking to drop some unused indexes

Any idea about this issue ?

Cheers